<?php

/**
 * MailListBulkUpsertTest — direct tests for the new core method.
 *
 * Unlike the CSV-driven tests, these tests feed canonical row arrays
 * (column-keyed, status validated, tags JSON-encoded) straight into
 * bulkUpsert() — no file I/O, no CSV parsing. This is the surface the
 * upcoming JSON API will exercise.
 *
 * Rationale: keeping a dedicated test layer for bulkUpsert lets us catch
 * regressions in the SQL pipeline independently of the CSV adapter. If a
 * CSV-shaped test fails, we now know whether the bug is in the adapter
 * (CSV-specific) or in the engine (SQL-level).
 */

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\Gate;
use Tests\TestCase;

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

beforeEach(function () {
    Gate::before(fn ($u, $a) => in_array($a, ['addMoreSubscribers', 'import'], true) ? true : null);

    $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('bu_c_'),
        'timezone' => 'UTC',
        'status'   => 'active',
    ]);
    $this->user = User::forceCreate([
        'customer_id' => $this->customer->id,
        'uid'         => uniqid('bu_u_'),
        'email'       => uniqid('u') . '@bulk.local',
        'password'    => bcrypt('x'),
    ]);
    $this->list = MailList::forceCreate([
        'uid'                      => uniqid('bu_l_'),
        'customer_id'              => $this->customer->id,
        'name'                     => 'Bulk Upsert Test List',
        'from_email'               => '[email protected]',
        'from_name'                => 'B',
        'send_welcome_email'       => false,
        'subscribe_confirmation'   => false,
        'unsubscribe_notification' => false,
    ]);

    $this->emailField     = $this->list->getEmailField();
    $this->firstNameField = $this->list->fields()->where('tag', 'FIRST_NAME')->first();
    $this->emailCol     = $this->emailField->custom_field_name;
    $this->firstNameCol = $this->firstNameField->custom_field_name;
});

afterEach(function () {
    // The tmp table inside bulkUpsert() is always DROPped in a finally{} block,
    // so this is defensive — only kicks in if a test bombs mid-flight.
    if (isset($this->list)) {
        DB::statement("DROP TABLE IF EXISTS " . DB::getTablePrefix() . "__tmp_subscribers_{$this->list->uid}");
    }
});

/**
 * Build a canonical row keyed by subscribers table columns.
 * Caller passes field-tag friendly inputs; helper translates to column names.
 */
function canon(array $tagged, string $emailCol, string $firstNameCol): array
{
    $out = [];
    foreach ($tagged as $k => $v) {
        $out[match (strtoupper($k)) {
            'EMAIL'      => $emailCol,
            'FIRST_NAME' => $firstNameCol,
            default      => $k,
        }] = $v;
    }
    return $out;
}

// ════════════════════════════════════════════════════════════════════════════
//  Happy path — insert into empty list
// ════════════════════════════════════════════════════════════════════════════

test('bulkUpsert into empty list inserts every row with correct counts', function () {
    $rows = [
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'A', 'status' => 'subscribed',   'tags' => json_encode(['vip'])], $this->emailCol, $this->firstNameCol),
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'B', 'status' => 'unsubscribed', 'tags' => json_encode([])     ], $this->emailCol, $this->firstNameCol),
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'C', 'status' => 'unconfirmed',  'tags' => json_encode(['x'])  ], $this->emailCol, $this->firstNameCol),
    ];

    $counts = $this->list->bulkUpsert($rows);

    expect($counts['inserted'])->toBe(3);
    expect($counts['updated'])->toBe(0);
    expect($counts['failed'])->toBe(0);
    expect($counts['batch_id'])->not->toBeEmpty();

    $sub = DB::table('subscribers')
        ->where('mail_list_id', $this->list->id)
        ->where('email', '[email protected]')
        ->first();
    expect($sub->status)->toBe('subscribed');
    expect(json_decode($sub->tags, true))->toBe(['vip']);
    expect($sub->{$this->firstNameCol})->toBe('A');
    expect($sub->subscription_type)->toBe(Subscriber::SUBSCRIPTION_TYPE_IMPORTED);
});

// ════════════════════════════════════════════════════════════════════════════
//  Empty input — no-op
// ════════════════════════════════════════════════════════════════════════════

test('bulkUpsert with empty rows returns zero counts without touching DB', function () {
    DB::table('subscribers')->insert([
        'uid'             => uniqid(),
        'mail_list_id'    => $this->list->id,
        'email'           => '[email protected]',
        'status'          => 'subscribed',
        $this->emailCol   => '[email protected]',
        'created_at'      => now(),
        'updated_at'      => now(),
    ]);

    $counts = $this->list->bulkUpsert([]);

    expect($counts)->toMatchArray(['inserted' => 0, 'updated' => 0, 'failed' => 0]);
    expect(DB::table('subscribers')->where('mail_list_id', $this->list->id)->count())->toBe(1);
});

// ════════════════════════════════════════════════════════════════════════════
//  Invalid mode — explicit guard
// ════════════════════════════════════════════════════════════════════════════

test('bulkUpsert with invalid mode throws LogicException (no fall-through)', function () {
    $rows = [canon(['EMAIL' => '[email protected]', 'status' => 'subscribed'], $this->emailCol, $this->firstNameCol)];

    expect(fn () => $this->list->bulkUpsert($rows, ['mode' => 'wipe-everything']))
        ->toThrow(\LogicException::class);
});

// ════════════════════════════════════════════════════════════════════════════
//  Email column missing — refuse the call
// ════════════════════════════════════════════════════════════════════════════

test('bulkUpsert without email column in rows throws', function () {
    $rows = [[ $this->firstNameCol => 'Joe', 'status' => 'subscribed' ]]; // no email

    expect(fn () => $this->list->bulkUpsert($rows))
        ->toThrow(\Exception::class, "email column");
});

// ════════════════════════════════════════════════════════════════════════════
//  Upsert behavior matrix on canonical rows
// ════════════════════════════════════════════════════════════════════════════

test('bulkUpsert mode=overwrite default: custom fields refreshed, status + tags preserved', function () {
    // Seed an existing subscriber.
    DB::table('subscribers')->insert([
        'uid'             => uniqid(),
        'mail_list_id'    => $this->list->id,
        'email'           => '[email protected]',
        'status'          => 'unsubscribed',
        'tags'            => json_encode(['oldtag']),
        $this->emailCol   => '[email protected]',
        $this->firstNameCol => 'OldAlice',
        'created_at'      => now(),
        'updated_at'      => now(),
    ]);

    $rows = [
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'NewAlice', 'status' => 'subscribed', 'tags' => json_encode(['newtag'])], $this->emailCol, $this->firstNameCol),
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'NewBee', 'status' => 'subscribed', 'tags' => json_encode([])      ], $this->emailCol, $this->firstNameCol),
    ];
    $counts = $this->list->bulkUpsert($rows);

    expect($counts)->toMatchArray(['inserted' => 1, 'updated' => 1]);

    $alice = DB::table('subscribers')->where('mail_list_id', $this->list->id)->where('email', '[email protected]')->first();
    expect($alice->status)->toBe('unsubscribed');                          // preserved (default flags off)
    expect(json_decode($alice->tags, true))->toBe(['oldtag']);             // preserved
    expect($alice->{$this->firstNameCol})->toBe('NewAlice');               // refreshed

    $new = DB::table('subscribers')->where('mail_list_id', $this->list->id)->where('email', '[email protected]')->first();
    expect($new)->not->toBeNull();
    expect($new->status)->toBe('subscribed');
});

test('bulkUpsert mode=overwrite with overwriteStatus + overwriteTags: everything replaced', function () {
    DB::table('subscribers')->insert([
        'uid'             => uniqid(),
        'mail_list_id'    => $this->list->id,
        'email'           => '[email protected]',
        'status'          => 'unsubscribed',
        'tags'            => json_encode(['oldtag']),
        $this->emailCol   => '[email protected]',
        $this->firstNameCol => 'OldAlice',
        'created_at'      => now(),
        'updated_at'      => now(),
    ]);

    $rows = [
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'NewAlice', 'status' => 'subscribed', 'tags' => json_encode(['newtag'])], $this->emailCol, $this->firstNameCol),
    ];
    $this->list->bulkUpsert($rows, ['mode' => 'overwrite', 'overwriteStatus' => true, 'overwriteTags' => true]);

    $alice = DB::table('subscribers')->where('mail_list_id', $this->list->id)->where('email', '[email protected]')->first();
    expect($alice->status)->toBe('subscribed');
    expect(json_decode($alice->tags, true))->toBe(['newtag']);
    expect($alice->{$this->firstNameCol})->toBe('NewAlice');
});

test('bulkUpsert mode=skip: existing rows untouched, new inserted', function () {
    DB::table('subscribers')->insert([
        'uid'               => uniqid(),
        'mail_list_id'      => $this->list->id,
        'email'             => '[email protected]',
        'status'            => 'unsubscribed',
        'tags'              => json_encode(['oldtag']),
        $this->emailCol     => '[email protected]',
        $this->firstNameCol => 'OldAlice',
        'created_at'        => now(),
        'updated_at'        => now(),
    ]);

    $rows = [
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'WouldBeIgnored', 'status' => 'subscribed', 'tags' => json_encode(['z'])], $this->emailCol, $this->firstNameCol),
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'Fresh',          'status' => 'subscribed', 'tags' => json_encode([])  ], $this->emailCol, $this->firstNameCol),
    ];
    $counts = $this->list->bulkUpsert($rows, ['mode' => 'skip']);

    expect($counts)->toMatchArray(['inserted' => 1, 'updated' => 0]);

    $alice = DB::table('subscribers')->where('mail_list_id', $this->list->id)->where('email', '[email protected]')->first();
    expect($alice->status)->toBe('unsubscribed');
    expect($alice->{$this->firstNameCol})->toBe('OldAlice');               // untouched
});

// ════════════════════════════════════════════════════════════════════════════
//  Tmp table collision fix — two parallel bulkUpsert calls on the same list
// ════════════════════════════════════════════════════════════════════════════

test('heterogeneous row shape (some rows missing custom_field keys) — INSERT still works', function () {
    // Regression: live API test caught a `SQLSTATE[21S01] column count does
    // not match value count` when one row had EMAIL+FIRST_NAME and another
    // had only EMAIL. bulkUpsert now pads missing columns with NULL so the
    // multi-row INSERT lines up.
    $rows = [
        // Row A — has both email + first_name
        canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'Anna', 'status' => 'subscribed'], $this->emailCol, $this->firstNameCol),
        // Row B — only email (no first_name key at all)
        [$this->emailCol => '[email protected]', 'status' => 'subscribed'],
        // Row C — only email + status
        [$this->emailCol => '[email protected]', 'status' => 'unsubscribed'],
    ];

    $counts = $this->list->bulkUpsert($rows);

    expect($counts['inserted'])->toBe(3);
    expect($counts['updated'])->toBe(0);

    // Row B + C should land with first_name = NULL (the missing key padded).
    $b = DB::table('subscribers')
        ->where('mail_list_id', $this->list->id)
        ->where('email', '[email protected]')->first();
    expect($b)->not->toBeNull();
    expect($b->{$this->firstNameCol})->toBeNull();
});

test('parallel bulkUpsert calls on same list do not collide on tmp table name', function () {
    // Simulate two interleaved imports: open call A's tmp table, then run B
    // start-to-finish, then finish A. Pre-refactor this would fail because
    // both tmp tables would collide on `__tmp_subscribers_{uid}`. Post-fix
    // the uniqid() suffix keeps them separate.
    $rowsA = [canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'A', 'status' => 'subscribed', 'tags' => json_encode([])], $this->emailCol, $this->firstNameCol)];
    $rowsB = [canon(['EMAIL' => '[email protected]', 'FIRST_NAME' => 'B', 'status' => 'subscribed', 'tags' => json_encode([])], $this->emailCol, $this->firstNameCol)];

    $this->list->bulkUpsert($rowsA);
    $this->list->bulkUpsert($rowsB);

    // Both rows landed → tmp tables didn't collide.
    expect(DB::table('subscribers')->where('mail_list_id', $this->list->id)->count())->toBe(2);
});