HEX
Server: LiteSpeed
System: Linux s1049.use1.mysecurecloudhost.com 4.18.0-477.27.2.lve.el8.x86_64 #1 SMP Wed Oct 11 12:32:56 UTC 2023 x86_64
User: xedaptot (3356)
PHP: 8.3.31
Disabled: NONE
Upload Files
File: /home/xedaptot/ai.naniguide.com/tests/Feature/MailListImportScenarioTest.php
<?php

/**
 * MailListImportScenarioTest — end-to-end scenarios with realistic multi-row
 * CSVs. Complements MailListImportSettingsTest (which is per-row matrix).
 *
 * Setup:
 *   - 1 customer + 1 user + 1 empty MailList with the 3 default fields
 *     (EMAIL/FIRST_NAME/LAST_NAME) plus a CITY field.
 *   - input1 = 5 records with mixed status + tags values (the "current state"
 *     of the list).
 *   - input2 = same 5 emails + 1 new email (frank), all field values mutated
 *     so any unintended overwrite is impossible to miss.
 *
 * Scenario coverage:
 *   1. Import input1 into empty list — every field on every row must land.
 *   2. Re-import input1 after wiping the list — must produce the same outcome
 *      (idempotency).
 *   3-7. Seed list with input1, then import input2 under each Settings combo:
 *      - mode=overwrite, both flags off  → custom fields updated; status/tags preserved
 *      - mode=overwrite, overwriteStatus → status overwritten; tags preserved
 *      - mode=overwrite, overwriteTags   → status preserved; tags overwritten
 *      - mode=overwrite, both flags on   → everything overwritten
 *      - mode=skip                       → existing rows untouched; only frank inserted
 *
 *   Each scenario reseeds the list back to input1 baseline so the assertions
 *   compare against a known starting state.
 */

use App\Library\Facades\AppCache;
use App\Model\Customer;
use App\Model\MailList;
use App\Model\Subscriber;
use App\Model\User;
use App\Services\Plans\Quotas\QuotasService;
use Illuminate\Foundation\Testing\DatabaseTransactions;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Gate;
use Tests\TestCase;

uses(TestCase::class);
uses(DatabaseTransactions::class);

beforeEach(function () {
    Gate::before(function ($user, $ability) {
        if (in_array($ability, ['addMoreSubscribers', 'import'], true)) {
            return true;
        }
        return null;
    });

    // Real QuotasService has 13 ctor deps — Mockery sidesteps them.
    $fakeQuotas = Mockery::mock(QuotasService::class)->shouldIgnoreMissing(true);
    $fakeQuotas->shouldReceive('canAccept')->andReturn(true);
    $fakeQuotas->shouldReceive('canAcceptOnList')->andReturn(true);
    app()->instance(QuotasService::class, $fakeQuotas);

    $this->customer = Customer::forceCreate([
        'uid'      => uniqid('imptest_c_'),
        'timezone' => 'UTC',
        'status'   => 'active',
    ]);
    $this->user = User::forceCreate([
        'customer_id' => $this->customer->id,
        'uid'         => uniqid('imptest_u_'),
        'email'       => uniqid('u') . '@import-scenario.local',
        'password'    => bcrypt('x'),
    ]);

    $this->list = MailList::forceCreate([
        'uid'                      => uniqid('imptest_l_'),
        'customer_id'              => $this->customer->id,
        'name'                     => 'Scenario Test List',
        'from_email'               => '[email protected]',
        'from_name'                => 'Scenario Test',
        'send_welcome_email'       => false,
        'subscribe_confirmation'   => false,
        'unsubscribe_notification' => false,
    ]);

    // EMAIL + FIRST_NAME + LAST_NAME are auto-created by the MailList boot event.
    $this->emailField     = $this->list->getEmailField();
    $this->firstNameField = $this->list->fields()->where('tag', 'FIRST_NAME')->first();
    $this->lastNameField  = $this->list->fields()->where('tag', 'LAST_NAME')->first();
    $this->cityField      = $this->list->createField('text', 'CITY', 'City', null, false, true, false);

    $this->firstNameCol = $this->firstNameField->custom_field_name;
    $this->lastNameCol  = $this->lastNameField->custom_field_name;
    $this->cityCol      = $this->cityField->custom_field_name;

    // Mapping used by every scenario.
    $this->mapping = [
        'EMAIL'      => $this->emailField->id,
        'FIRST_NAME' => $this->firstNameField->id,
        'LAST_NAME'  => $this->lastNameField->id,
        'CITY'       => $this->cityField->id,
    ];

    // ──────────────────────────────────────────────────────────────────────
    // input1 — initial state for the list.
    //   Mixed statuses (subscribed/unsubscribed/unconfirmed) and tag lists so
    //   the "preserve" assertions can prove each value survived intact.
    // ──────────────────────────────────────────────────────────────────────
    $this->input1Rows = [
        ['EMAIL' => '[email protected]',  'FIRST_NAME' => 'Alice',  'LAST_NAME' => 'Anderson', 'CITY' => 'Austin',  'status' => 'subscribed',   'tags' => 'vip,beta'],
        ['EMAIL' => '[email protected]',    'FIRST_NAME' => 'Bob',    'LAST_NAME' => 'Brown',    'CITY' => 'Boston',  'status' => 'unsubscribed', 'tags' => 'lapsed'],
        ['EMAIL' => '[email protected]',  'FIRST_NAME' => 'Carol',  'LAST_NAME' => 'Clark',    'CITY' => 'Chicago', 'status' => 'unconfirmed',  'tags' => ''],
        ['EMAIL' => '[email protected]',   'FIRST_NAME' => 'Dave',   'LAST_NAME' => 'Davis',    'CITY' => 'Denver',  'status' => 'subscribed',   'tags' => 'newsletter'],
        ['EMAIL' => '[email protected]',    'FIRST_NAME' => 'Eve',    'LAST_NAME' => 'Evans',    'CITY' => 'El Paso', 'status' => 'subscribed',   'tags' => 'vip'],
    ];

    // ──────────────────────────────────────────────────────────────────────
    // input2 — second-pass CSV. Same 5 emails (lowercase + .local-cased) so
    // they collide with input1; every other field deliberately mutated; one
    // brand-new email (frank) to verify INSERT path still fires.
    // ──────────────────────────────────────────────────────────────────────
    $this->input2Rows = [
        ['EMAIL' => '[email protected]',  'FIRST_NAME' => 'Alice2',  'LAST_NAME' => 'Anderson2', 'CITY' => 'Atlanta',     'status' => 'unsubscribed', 'tags' => 'gold'],
        ['EMAIL' => '[email protected]',    'FIRST_NAME' => 'Bob2',    'LAST_NAME' => 'Brown2',    'CITY' => 'Baltimore',   'status' => 'subscribed',   'tags' => 'reactivated'],
        ['EMAIL' => '[email protected]',  'FIRST_NAME' => 'Carol2',  'LAST_NAME' => 'Clark2',    'CITY' => 'Cleveland',   'status' => 'subscribed',   'tags' => 'confirmed,vip'],
        ['EMAIL' => '[email protected]',   'FIRST_NAME' => 'Dave2',   'LAST_NAME' => 'Davis2',    'CITY' => 'Dallas',      'status' => 'unconfirmed',  'tags' => 'pending'],
        ['EMAIL' => '[email protected]',    'FIRST_NAME' => 'Eve2',    'LAST_NAME' => 'Evans2',    'CITY' => 'Eugene',      'status' => 'unsubscribed', 'tags' => 'churn'],
        ['EMAIL' => '[email protected]',  'FIRST_NAME' => 'Frank',   'LAST_NAME' => 'Foster',    'CITY' => 'Fresno',      'status' => 'subscribed',   'tags' => 'fresh'],
    ];

    // CSV column order — kept here so individual tests can override (e.g. omit
    // status to verify the case-where-status-column-missing safe default).
    $this->csvColumns = ['EMAIL', 'FIRST_NAME', 'LAST_NAME', 'CITY', 'status', 'tags'];
});

afterEach(function () {
    // Defensive temp-table cleanup — DatabaseTransactions rolls back rows but
    // not DDL for the tmp table created by createTmpTableFromMapping().
    if (isset($this->list)) {
        $tmp = DB::getTablePrefix() . '__tmp_subscribers_' . $this->list->uid;
        DB::statement("DROP TABLE IF EXISTS {$tmp}");
    }
});

// ════════════════════════════════════════════════════════════════════════════
//  Helpers
// ════════════════════════════════════════════════════════════════════════════

function scenarioMakeCsv(array $columns, array $rows): string
{
    $dir = storage_path('app/tmp/import-scenario');
    File::ensureDirectoryExists($dir);
    $path = $dir . '/csv-' . uniqid() . '.csv';
    $fh = fopen($path, 'w');
    fputcsv($fh, $columns);
    foreach ($rows as $row) {
        $line = [];
        foreach ($columns as $col) {
            $line[] = $row[$col] ?? '';
        }
        fputcsv($fh, $line);
    }
    fclose($fh);
    return $path;
}

function scenarioReloadByEmail(MailList $list, string $email): ?object
{
    return DB::table('subscribers')
        ->where('mail_list_id', $list->id)
        ->where('email', $email)
        ->first();
}

function scenarioAllSubs(MailList $list)
{
    return DB::table('subscribers')
        ->where('mail_list_id', $list->id)
        ->orderBy('email')
        ->get();
}

function scenarioWipeList(MailList $list): void
{
    DB::table('subscribers')->where('mail_list_id', $list->id)->delete();
}

/**
 * Assert that a subscriber row matches the values that came from a CSV row.
 * `$expected` keys are the CSV headers; the helper translates EMAIL/FIRST_NAME/etc.
 * to the actual subscribers table columns.
 */
function scenarioAssertMatchesCsvRow(object $sub, array $expectedCsv, string $firstNameCol, string $lastNameCol, string $cityCol): void
{
    expect($sub->email)->toBe(strtolower($expectedCsv['EMAIL']));
    expect($sub->{$firstNameCol})->toBe($expectedCsv['FIRST_NAME']);
    expect($sub->{$lastNameCol})->toBe($expectedCsv['LAST_NAME']);
    expect($sub->{$cityCol})->toBe($expectedCsv['CITY']);
    expect($sub->status)->toBe($expectedCsv['status']);
    if ($expectedCsv['tags'] === '') {
        // empty CSV cell → tags column was inserted as empty string by import.
        // Treat both NULL and "" as "no tags".
        expect($sub->tags === null || $sub->tags === '' || $sub->tags === '[]')->toBeTrue();
    } else {
        expect(json_decode($sub->tags, true))->toBe(
            preg_split('/\s*,\s*/', $expectedCsv['tags'])
        );
    }
}

// ════════════════════════════════════════════════════════════════════════════
//  SCENARIO 1 — Import input1 into an empty list
// ════════════════════════════════════════════════════════════════════════════

test('scenario 1 — import input1 into an empty list lands every field on every row', function () {
    expect(scenarioAllSubs($this->list))->toHaveCount(0); // sanity

    $csv = scenarioMakeCsv($this->csvColumns, $this->input1Rows);
    $this->list->import($csv, $this->mapping, []);

    $all = scenarioAllSubs($this->list);
    expect($all)->toHaveCount(5);

    foreach ($this->input1Rows as $row) {
        $sub = scenarioReloadByEmail($this->list, $row['EMAIL']);
        expect($sub)->not->toBeNull("Missing subscriber for {$row['EMAIL']}");
        scenarioAssertMatchesCsvRow($sub, $row, $this->firstNameCol, $this->lastNameCol, $this->cityCol);

        // Every freshly-inserted row is tagged with the import batch id and
        // gets the imported subscription_type — pin those, they're observable.
        expect($sub->subscription_type)->toBe(Subscriber::SUBSCRIPTION_TYPE_IMPORTED);
        expect($sub->import_batch_id)->not->toBeNull();
        expect($sub->verification_status)->toBeNull(); // never set on INSERT
    }
});

// ════════════════════════════════════════════════════════════════════════════
//  SCENARIO 2 — Idempotent re-import after wipe
// ════════════════════════════════════════════════════════════════════════════

test('scenario 2 — wipe + re-import input1 produces identical row content', function () {
    // First import.
    $csv1 = scenarioMakeCsv($this->csvColumns, $this->input1Rows);
    $this->list->import($csv1, $this->mapping, []);
    $first = scenarioAllSubs($this->list);

    // Wipe and re-import.
    scenarioWipeList($this->list);
    expect(scenarioAllSubs($this->list))->toHaveCount(0);

    $csv2 = scenarioMakeCsv($this->csvColumns, $this->input1Rows);
    $this->list->import($csv2, $this->mapping, []);
    $second = scenarioAllSubs($this->list);

    expect($second)->toHaveCount(5);

    // Same content (compare field-by-field; uid + import_batch_id + timestamps
    // will differ between imports — exclude those).
    $compare = function ($row) {
        return [
            'email'   => $row->email,
            'status'  => $row->status,
            'tags'    => $row->tags,
            'fname'   => $row->{$this->firstNameCol},
            'lname'   => $row->{$this->lastNameCol},
            'city'    => $row->{$this->cityCol},
        ];
    };
    expect($second->map($compare)->toArray())->toBe($first->map($compare)->toArray());
});

// ════════════════════════════════════════════════════════════════════════════
//  SCENARIOS 3-7 — Import input1, then import input2 under each option combo
// ════════════════════════════════════════════════════════════════════════════

/**
 * Seed the list to the input1 baseline. Each scenario starts here.
 */
function scenarioSeedInput1($test): void
{
    $csv = scenarioMakeCsv($test->csvColumns, $test->input1Rows);
    $test->list->import($csv, $test->mapping, []);
    expect(scenarioAllSubs($test->list))->toHaveCount(5);
}

test('scenario 3 — overwrite + both flags OFF: custom fields refreshed, status + tags preserved, frank inserted', function () {
    scenarioSeedInput1($this);

    // Snapshot the existing subscribers' status + tags so we can prove they
    // didn't change.
    $before = scenarioAllSubs($this->list)
        ->keyBy('email')
        ->map(fn ($r) => ['status' => $r->status, 'tags' => $r->tags]);

    $csv = scenarioMakeCsv($this->csvColumns, $this->input2Rows);
    $this->list->import($csv, $this->mapping, []); // defaults: mode=overwrite, both flags off

    expect(scenarioAllSubs($this->list))->toHaveCount(6); // 5 + frank

    // For each existing subscriber, custom fields must match input2; status +
    // tags must match the snapshot taken before.
    foreach (['alice', 'bob', 'carol', 'dave', 'eve'] as $name) {
        $email = "{$name}@scenario.local";
        $sub   = scenarioReloadByEmail($this->list, $email);
        $row2  = collect($this->input2Rows)->firstWhere('EMAIL', $email);

        expect($sub->{$this->firstNameCol})->toBe($row2['FIRST_NAME']); // refreshed
        expect($sub->{$this->lastNameCol})->toBe($row2['LAST_NAME']);   // refreshed
        expect($sub->{$this->cityCol})->toBe($row2['CITY']);            // refreshed
        expect($sub->status)->toBe($before[$email]['status']);          // PRESERVED
        expect($sub->tags)->toBe($before[$email]['tags']);              // PRESERVED
    }

    // Frank — fresh INSERT, must reflect input2 values exactly.
    $frank = scenarioReloadByEmail($this->list, '[email protected]');
    expect($frank)->not->toBeNull();
    scenarioAssertMatchesCsvRow(
        $frank,
        collect($this->input2Rows)->firstWhere('EMAIL', '[email protected]'),
        $this->firstNameCol, $this->lastNameCol, $this->cityCol
    );
});

test('scenario 4 — overwrite + overwriteStatus=true: status overwritten, tags preserved', function () {
    scenarioSeedInput1($this);
    $tagsBefore = scenarioAllSubs($this->list)->keyBy('email')->map(fn ($r) => $r->tags);

    $csv = scenarioMakeCsv($this->csvColumns, $this->input2Rows);
    $this->list->import($csv, $this->mapping, ['mode' => 'overwrite', 'overwriteStatus' => true]);

    expect(scenarioAllSubs($this->list))->toHaveCount(6);

    foreach (['alice', 'bob', 'carol', 'dave', 'eve'] as $name) {
        $email = "{$name}@scenario.local";
        $sub   = scenarioReloadByEmail($this->list, $email);
        $row2  = collect($this->input2Rows)->firstWhere('EMAIL', $email);

        expect($sub->status)->toBe($row2['status'])->and(true)->toBeTrue();          // OVERWRITTEN
        expect($sub->tags)->toBe($tagsBefore[$email]);                                // preserved
        expect($sub->{$this->firstNameCol})->toBe($row2['FIRST_NAME']);
    }

    // Frank still inserted clean.
    $frank = scenarioReloadByEmail($this->list, '[email protected]');
    expect($frank->status)->toBe('subscribed');
});

test('scenario 5 — overwrite + overwriteTags=true: tags overwritten, status preserved', function () {
    scenarioSeedInput1($this);
    $statusBefore = scenarioAllSubs($this->list)->keyBy('email')->map(fn ($r) => $r->status);

    $csv = scenarioMakeCsv($this->csvColumns, $this->input2Rows);
    $this->list->import($csv, $this->mapping, ['mode' => 'overwrite', 'overwriteTags' => true]);

    expect(scenarioAllSubs($this->list))->toHaveCount(6);

    foreach (['alice', 'bob', 'carol', 'dave', 'eve'] as $name) {
        $email = "{$name}@scenario.local";
        $sub   = scenarioReloadByEmail($this->list, $email);
        $row2  = collect($this->input2Rows)->firstWhere('EMAIL', $email);

        expect($sub->status)->toBe($statusBefore[$email]);                            // preserved
        if ($row2['tags'] === '') {
            expect($sub->tags === null || $sub->tags === '')->toBeTrue();
        } else {
            expect(json_decode($sub->tags, true))->toBe(preg_split('/\s*,\s*/', $row2['tags'])); // OVERWRITTEN
        }
    }
});

test('scenario 6 — overwrite + both flags ON: every field replaced by input2', function () {
    scenarioSeedInput1($this);

    $csv = scenarioMakeCsv($this->csvColumns, $this->input2Rows);
    $this->list->import($csv, $this->mapping, [
        'mode'            => 'overwrite',
        'overwriteStatus' => true,
        'overwriteTags'   => true,
    ]);

    expect(scenarioAllSubs($this->list))->toHaveCount(6);

    // Now the list should look identical to importing input2 from scratch — for
    // every row, every CSV-derived field must match input2's value.
    foreach ($this->input2Rows as $row) {
        $sub = scenarioReloadByEmail($this->list, $row['EMAIL']);
        scenarioAssertMatchesCsvRow($sub, $row, $this->firstNameCol, $this->lastNameCol, $this->cityCol);
    }
});

test('scenario 7 — mode=skip: existing 5 rows untouched, only frank is inserted', function () {
    scenarioSeedInput1($this);
    $snapshot = scenarioAllSubs($this->list)->keyBy('email');

    $csv = scenarioMakeCsv($this->csvColumns, $this->input2Rows);
    $this->list->import($csv, $this->mapping, ['mode' => 'skip']);

    expect(scenarioAllSubs($this->list))->toHaveCount(6); // 5 untouched + frank

    foreach (['alice', 'bob', 'carol', 'dave', 'eve'] as $name) {
        $email = "{$name}@scenario.local";
        $sub   = scenarioReloadByEmail($this->list, $email);
        $row1  = collect($this->input1Rows)->firstWhere('EMAIL', $email);

        // All fields equal to input1 — nothing changed.
        expect($sub->{$this->firstNameCol})->toBe($row1['FIRST_NAME']);
        expect($sub->{$this->lastNameCol})->toBe($row1['LAST_NAME']);
        expect($sub->{$this->cityCol})->toBe($row1['CITY']);
        expect($sub->status)->toBe($row1['status']);
        // updated_at must also be unchanged.
        expect($sub->updated_at)->toBe($snapshot[$email]->updated_at);
    }

    // Frank — newly inserted from input2.
    $frank = scenarioReloadByEmail($this->list, '[email protected]');
    expect($frank)->not->toBeNull();
    expect($frank->{$this->firstNameCol})->toBe('Frank');
    expect($frank->{$this->cityCol})->toBe('Fresno');
    expect($frank->status)->toBe('subscribed');
});

// ════════════════════════════════════════════════════════════════════════════
//  SCENARIO 8 — Status + Tags WITHOUT corresponding CSV columns
// ════════════════════════════════════════════════════════════════════════════

test('scenario 8 — input2 lacking status + tags columns: existing rows fully preserve those fields under defaults', function () {
    scenarioSeedInput1($this);
    $before = scenarioAllSubs($this->list)->keyBy('email')
        ->map(fn ($r) => ['status' => $r->status, 'tags' => $r->tags]);

    // Same mutations as input2 but DROP the status + tags columns entirely.
    $rowsNoStatusTags = array_map(function ($r) {
        unset($r['status'], $r['tags']);
        return $r;
    }, $this->input2Rows);

    $csv = scenarioMakeCsv(['EMAIL', 'FIRST_NAME', 'LAST_NAME', 'CITY'], $rowsNoStatusTags);
    $this->list->import($csv, $this->mapping, []); // defaults

    expect(scenarioAllSubs($this->list))->toHaveCount(6);

    foreach (['alice', 'bob', 'carol', 'dave', 'eve'] as $name) {
        $email = "{$name}@scenario.local";
        $sub   = scenarioReloadByEmail($this->list, $email);
        $row2  = collect($this->input2Rows)->firstWhere('EMAIL', $email);

        expect($sub->{$this->firstNameCol})->toBe($row2['FIRST_NAME']);  // refreshed
        expect($sub->status)->toBe($before[$email]['status']);            // preserved (regression!)
        expect($sub->tags)->toBe($before[$email]['tags']);                // preserved
    }

    // Frank still gets the default subscribed status on INSERT.
    $frank = scenarioReloadByEmail($this->list, '[email protected]');
    expect($frank->status)->toBe('subscribed');
});

// ════════════════════════════════════════════════════════════════════════════
//  SCENARIO 9 — Case-insensitive status/tags detection on real CSV headers
// ════════════════════════════════════════════════════════════════════════════

test('scenario 9 — CSV uses capitalized "Status" / "Tags" headers: overwrite flags still take effect', function () {
    scenarioSeedInput1($this);

    // Same input2 but header casing flipped to "Status" + "Tags".
    $columnsMixedCase = ['EMAIL', 'FIRST_NAME', 'LAST_NAME', 'CITY', 'Status', 'Tags'];
    $rowsMixedKeys = array_map(function ($r) {
        $r['Status'] = $r['status'];
        $r['Tags']   = $r['tags'];
        unset($r['status'], $r['tags']);
        return $r;
    }, $this->input2Rows);

    $csv = scenarioMakeCsv($columnsMixedCase, $rowsMixedKeys);
    $this->list->import($csv, $this->mapping, [
        'mode'            => 'overwrite',
        'overwriteStatus' => true,
        'overwriteTags'   => true,
    ]);

    expect(scenarioAllSubs($this->list))->toHaveCount(6);

    foreach (['alice', 'bob', 'carol', 'dave', 'eve'] as $name) {
        $email = "{$name}@scenario.local";
        $sub   = scenarioReloadByEmail($this->list, $email);
        $row2  = collect($this->input2Rows)->firstWhere('EMAIL', $email);

        expect($sub->status)->toBe($row2['status']);
        if ($row2['tags'] === '') {
            expect($sub->tags === null || $sub->tags === '')->toBeTrue();
        } else {
            expect(json_decode($sub->tags, true))->toBe(preg_split('/\s*,\s*/', $row2['tags']));
        }
    }
});

// ════════════════════════════════════════════════════════════════════════════
//  SCENARIO 10 — Subset mapping (LAST_NAME unmapped) preserves unmapped column
// ════════════════════════════════════════════════════════════════════════════

test('scenario 10 — unmapped LAST_NAME column is preserved even on overwrite', function () {
    scenarioSeedInput1($this);

    // Map only EMAIL + FIRST_NAME + CITY — drop LAST_NAME.
    $partialMapping = [
        'EMAIL'      => $this->emailField->id,
        'FIRST_NAME' => $this->firstNameField->id,
        'CITY'       => $this->cityField->id,
    ];

    $csv = scenarioMakeCsv($this->csvColumns, $this->input2Rows);
    $this->list->import($csv, $partialMapping, ['mode' => 'overwrite']);

    expect(scenarioAllSubs($this->list))->toHaveCount(6);

    foreach (['alice', 'bob', 'carol', 'dave', 'eve'] as $name) {
        $email = "{$name}@scenario.local";
        $sub   = scenarioReloadByEmail($this->list, $email);
        $row1  = collect($this->input1Rows)->firstWhere('EMAIL', $email);
        $row2  = collect($this->input2Rows)->firstWhere('EMAIL', $email);

        expect($sub->{$this->firstNameCol})->toBe($row2['FIRST_NAME']);  // mapped → refreshed
        expect($sub->{$this->cityCol})->toBe($row2['CITY']);              // mapped → refreshed
        expect($sub->{$this->lastNameCol})->toBe($row1['LAST_NAME']);     // UNMAPPED → input1 value retained
    }
});

// ════════════════════════════════════════════════════════════════════════════
//  SCENARIO 11 — Email matching is case-insensitive (alice vs ALICE)
// ════════════════════════════════════════════════════════════════════════════

test('scenario 11 — input2 with UPPER-CASE email collides with the lower-case row from input1 (no duplicate row)', function () {
    scenarioSeedInput1($this);

    // Flip alice's email to [email protected] — must still be treated as the
    // same subscriber (the import code lowercases emails before tmp-table join).
    $rowsAlice = $this->input2Rows;
    $rowsAlice[0]['EMAIL'] = '[email protected]';

    $csv = scenarioMakeCsv($this->csvColumns, $rowsAlice);
    $this->list->import($csv, $this->mapping, ['mode' => 'overwrite']);

    // Only one alice row in the DB.
    $aliceRows = DB::table('subscribers')
        ->where('mail_list_id', $this->list->id)
        ->whereRaw('LOWER(email) = ?', ['[email protected]'])
        ->get();
    expect($aliceRows)->toHaveCount(1);

    // And it got refreshed (city was Austin in input1 → Atlanta in input2).
    expect($aliceRows->first()->{$this->cityCol})->toBe('Atlanta');
});