<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

/**
 * Revenue routing — which ledger account every collected piastre lands in.
 *
 * Until now that answer was a `match` statement inside LedgerAccountResolver:
 * five itemable classes hardcoded to five revenue codes, no tax anywhere, and
 * no way for the academy's own accountant to say "no, half of that goes to
 * 4010" without a deploy. Every ERP that survives an audit has this screen —
 * SAP calls it revenue account determination, Dynamics calls it the general
 * posting setup, Odoo puts an income account on the product category. This is
 * ours.
 *
 * Two tables:
 *
 *   revenue_mappings       one rule per revenue source, optionally narrowed to
 *                          a single product / programme / event, carrying the
 *                          tax treatment for that source.
 *   revenue_mapping_lines  how the net splits: a flat number of piasters, a
 *                          percentage, or "whatever is left".
 *
 * The seed below reproduces today's behaviour exactly — same account for the
 * same itemable, no tax anywhere — so this migration changes where nothing
 * lands. What it changes is that the routing is now data an accountant can
 * edit, instead of a `match` an engineer has to redeploy.
 */
return new class extends Migration
{
    /**
     * source key => [Arabic label, default account code, active on install].
     *
     * `service_fee` ships inactive on purpose. The platform fee is not an
     * invoice line: today it rides along inside the proportional split of the
     * lines that are there, and carving it out to its own account on the night
     * of a deploy would move money between accounts in every client's books
     * without anyone asking for it. Inactive means "keep folding it in"; an
     * accountant who wants it separated turns it on and says where.
     */
    private const SOURCES = [
        ['subscription',      'اشتراك تدريبي',            '4000', true],
        ['training_program',  'اشتراك برنامج مرتبط',       '4000', true],
        ['product',           'بيع منتج',                  '4020', true],
        ['kit',               'بيع طقم',                   '4020', true],
        ['event',             'رسوم إيفنت أو بطولة',       '4050', true],
        ['space_reservation', 'حجز ملعب أو مساحة',         '4030', true],
        ['external',          'إيراد خارجي',               '4060', true],
        ['other',             'إيرادات أخرى',              '4060', true],
        ['service_fee',       'رسوم خدمة المنصة',          '4060', false],
    ];

    /**
     * VAT collected from a customer is money held for the Tax Authority, so it
     * is a liability — never a slice of revenue. The chart had nowhere to put
     * it, which is why the tax question could not even be asked before now.
     */
    private const TAX_ACCOUNTS = [
        ['code' => '2030', 'name' => 'VAT Payable', 'name_ar' => 'ضريبة القيمة المضافة المستحقة', 'type' => 'liability', 'category' => 'current_liability'],
        ['code' => '2040', 'name' => 'Withholding Tax Payable', 'name_ar' => 'ضرائب خصم وإضافة مستحقة', 'type' => 'liability', 'category' => 'current_liability'],
    ];

    public function up(): void
    {
        if (! Schema::hasTable('financial_accounts') || ! Schema::hasTable('academies')) {
            return;
        }

        $this->createMappings();
        $this->createLines();
        $this->seedTaxAccounts();
        $this->seedDefaults();
    }

    private function createMappings(): void
    {
        if (Schema::hasTable('revenue_mappings')) {
            return;
        }

        Schema::create('revenue_mappings', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('academy_id')->constrained()->cascadeOnDelete();

            $table->string('source_key', 40);

            // A rule narrowed to one product / programme / event. Null is the
            // academy-wide default for the source.
            $table->string('scope_type', 255)->nullable();
            $table->unsignedBigInteger('scope_id')->nullable();

            $table->string('label', 160)->nullable();
            $table->boolean('is_active')->default(true);

            $table->string('tax_mode', 20)->default('none');
            $table->integer('tax_rate_bp')->default(0);
            $table->foreignId('tax_account_id')->nullable()->constrained('financial_accounts')->nullOnDelete();

            $table->text('notes')->nullable();
            $table->foreignId('updated_by')->nullable()->constrained('users')->nullOnDelete();
            $table->timestamps();

            $table->index(['academy_id', 'source_key']);
            $table->index(['scope_type', 'scope_id']);
        });

        if (DB::getDriverName() === 'pgsql') {
            DB::statement("ALTER TABLE revenue_mappings ADD CONSTRAINT revenue_mappings_tax_mode_check CHECK (tax_mode IN ('none', 'inclusive', 'exclusive'))");
            DB::statement('ALTER TABLE revenue_mappings ADD CONSTRAINT revenue_mappings_tax_rate_check CHECK (tax_rate_bp >= 0 AND tax_rate_bp <= 10000)');

            // A scope is a pair or it is nothing; half of one is a rule that
            // matches everything or nothing depending on which half survived.
            DB::statement('ALTER TABLE revenue_mappings ADD CONSTRAINT revenue_mappings_scope_pair_check CHECK ((scope_type IS NULL) = (scope_id IS NULL))');

            // A tax rate with no account to hold it would post nowhere and
            // silently unbalance the entry.
            DB::statement("ALTER TABLE revenue_mappings ADD CONSTRAINT revenue_mappings_tax_account_check CHECK (tax_mode = 'none' OR tax_account_id IS NOT NULL)");
        }

        // Two partial uniques rather than one composite: in SQL, NULL is not
        // equal to NULL, so a plain UNIQUE(academy_id, source_key, scope_type,
        // scope_id) would happily accept a hundred academy-wide defaults for
        // the same source and the resolver would pick whichever came back first.
        DB::statement('CREATE UNIQUE INDEX revenue_mappings_default_unique ON revenue_mappings (academy_id, source_key) WHERE scope_type IS NULL');
        DB::statement('CREATE UNIQUE INDEX revenue_mappings_scoped_unique ON revenue_mappings (academy_id, source_key, scope_type, scope_id) WHERE scope_type IS NOT NULL');
    }

    private function createLines(): void
    {
        if (Schema::hasTable('revenue_mapping_lines')) {
            return;
        }

        Schema::create('revenue_mapping_lines', function (Blueprint $table) {
            $table->id();
            $table->uuid('uuid')->unique();
            $table->foreignId('academy_id')->constrained()->cascadeOnDelete();
            $table->foreignId('revenue_mapping_id')->constrained('revenue_mappings')->cascadeOnDelete();
            $table->foreignId('account_id')->constrained('financial_accounts');

            $table->string('allocation_type', 20);
            $table->integer('percentage_bp')->nullable();
            $table->bigInteger('fixed_amount')->nullable();

            $table->string('label', 160)->nullable();
            $table->unsignedSmallInteger('sort_order')->default(0);
            $table->timestamps();

            $table->index(['revenue_mapping_id', 'sort_order']);
            $table->index('account_id');
        });

        if (DB::getDriverName() === 'pgsql') {
            DB::statement("ALTER TABLE revenue_mapping_lines ADD CONSTRAINT revenue_mapping_lines_type_check CHECK (allocation_type IN ('percentage', 'fixed', 'remainder'))");
            DB::statement('ALTER TABLE revenue_mapping_lines ADD CONSTRAINT revenue_mapping_lines_percentage_check CHECK (percentage_bp IS NULL OR (percentage_bp >= 0 AND percentage_bp <= 10000))');
            DB::statement('ALTER TABLE revenue_mapping_lines ADD CONSTRAINT revenue_mapping_lines_fixed_check CHECK (fixed_amount IS NULL OR fixed_amount >= 0)');

            // Each allocation type carries exactly the value it needs and no
            // other, so a line can never be read two ways.
            DB::statement(<<<'SQL'
                ALTER TABLE revenue_mapping_lines ADD CONSTRAINT revenue_mapping_lines_shape_check CHECK (
                    (allocation_type = 'percentage' AND percentage_bp IS NOT NULL AND fixed_amount IS NULL)
                    OR (allocation_type = 'fixed' AND fixed_amount IS NOT NULL AND percentage_bp IS NULL)
                    OR (allocation_type = 'remainder' AND fixed_amount IS NULL AND percentage_bp IS NULL)
                )
            SQL);

            // At most one "whatever is left" per rule, or the leftover has two
            // homes and the entry stops balancing.
            DB::statement("CREATE UNIQUE INDEX revenue_mapping_lines_one_remainder ON revenue_mapping_lines (revenue_mapping_id) WHERE allocation_type = 'remainder'");
        }
    }

    private function seedTaxAccounts(): void
    {
        $academyIds = DB::table('academies')->pluck('id');
        $now = now();

        foreach ($academyIds as $academyId) {
            foreach (self::TAX_ACCOUNTS as $account) {
                $exists = DB::table('financial_accounts')
                    ->where('academy_id', $academyId)
                    ->where('code', $account['code'])
                    ->exists();

                if ($exists) {
                    continue;
                }

                DB::table('financial_accounts')->insert($account + [
                    'uuid' => (string) Str::uuid(),
                    'academy_id' => $academyId,
                    'is_system' => true,
                    'is_active' => true,
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);
            }
        }
    }

    /**
     * One default rule per academy per source, each a single "everything that
     * is left" line pointing at the account the old `match` would have chosen.
     *
     * Written with firstOrCreate semantics so a container restarting for the
     * fifth time neither duplicates a rule nor overwrites one an accountant has
     * since edited.
     */
    private function seedDefaults(): void
    {
        $academyIds = DB::table('academies')->pluck('id');
        $now = now();

        foreach ($academyIds as $academyId) {
            $accounts = DB::table('financial_accounts')
                ->where('academy_id', $academyId)
                ->pluck('id', 'code');

            foreach (self::SOURCES as [$key, $label, $code, $active]) {
                $accountId = $accounts[$code] ?? null;

                // No chart for this academy yet: the accounts migration runs
                // before this one, so this only happens on a tenant whose chart
                // was deliberately pruned. Skipping is right — the resolver
                // falls back to the legacy behaviour and nothing breaks.
                if (! $accountId) {
                    continue;
                }

                $existing = DB::table('revenue_mappings')
                    ->where('academy_id', $academyId)
                    ->where('source_key', $key)
                    ->whereNull('scope_type')
                    ->value('id');

                if ($existing) {
                    continue;
                }

                $mappingId = DB::table('revenue_mappings')->insertGetId([
                    'uuid' => (string) Str::uuid(),
                    'academy_id' => $academyId,
                    'source_key' => $key,
                    'scope_type' => null,
                    'scope_id' => null,
                    'label' => $label,
                    'is_active' => $active,
                    'tax_mode' => 'none',
                    'tax_rate_bp' => 0,
                    'tax_account_id' => null,
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);

                DB::table('revenue_mapping_lines')->insert([
                    'uuid' => (string) Str::uuid(),
                    'academy_id' => $academyId,
                    'revenue_mapping_id' => $mappingId,
                    'account_id' => $accountId,
                    'allocation_type' => 'remainder',
                    'percentage_bp' => null,
                    'fixed_amount' => null,
                    'label' => null,
                    'sort_order' => 0,
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);
            }
        }
    }

    public function down(): void
    {
        Schema::dropIfExists('revenue_mapping_lines');
        Schema::dropIfExists('revenue_mappings');

        // The two tax accounts stay. Once a single transaction has credited
        // 2030 it cannot be removed without orphaning the ledger, and a rolled
        // back migration is not a reason to lose a VAT liability.
    }
};
