<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * Check one client's data against the branch boundary, and say what is wrong.
 *
 *     php artisan branch:audit
 *
 * Branch isolation is enforced in the model layer, so the code is the same
 * everywhere — but the *data* is not, and the data is what decides whether the
 * enforcement helps or hurts. Two shapes cause real damage and neither raises an
 * error on its own:
 *
 *   A strictly-scoped row with no branch is invisible from every branch at once.
 *   It is not a leak, it is a disappearance: the record is still there, still
 *   counted in the database, and on no screen in the product.
 *
 *   A child pointing at a parent in another branch renders blank and, for a
 *   group whose programme is elsewhere, cannot be priced — PricingService treats
 *   a missing base price as a hard failure, so the sale stops.
 *
 * The migrations fix both for the shapes they can reach. This exists because
 * there are ~30 client databases, no staging between a push and all of them, and
 * no way to inspect them from a laptop. Run it against a tenant after a deploy
 * and it will say plainly whether that tenant is intact.
 *
 * Exits non-zero when anything is found, so it can gate a deploy check.
 */
class AuditBranchIntegrity extends Command
{
    protected $signature = 'branch:audit {--json : Emit machine-readable output}';

    protected $description = 'Report rows that belong to no branch, or point at another branch';

    /**
     * Tables where every row belongs to exactly one branch. A null here is the
     * disappearance described above.
     */
    private const STRICT = [
        'participants', 'training_groups', 'enrollments', 'training_sessions',
        'attendance_records', 'evaluations', 'waitlists', 'assignments',
        'invoices', 'payments', 'transactions', 'expenses', 'cash_sessions',
        'pos_transactions', 'facilities', 'space_reservations', 'space_layouts',
        'inventory_levels', 'inventory_movements', 'stock_counts',
        'training_programs', 'base_prices', 'products', 'warehouses',
        'documents', 'service_requests', 'payslips',
    ];

    /**
     * Child → parent links that must agree on the branch.
     *
     * [child table, child FK, parent table, what breaks when they disagree]
     */
    private const LINKS = [
        ['training_groups', 'training_program_id', 'training_programs', 'the group cannot be priced or renewed'],
        ['enrollments', 'training_group_id', 'training_groups', 'the enrolment is filed away from its group'],
        ['training_sessions', 'training_group_id', 'training_groups', 'the session is missing from the schedule'],
        ['attendance_records', 'training_session_id', 'training_sessions', 'attendance is missing from the register'],
        ['inventory_levels', 'warehouse_id', 'warehouses', 'stock shows against the wrong shelf'],
        ['inventory_movements', 'warehouse_id', 'warehouses', 'the movement is missing from the ledger'],
        ['stock_counts', 'warehouse_id', 'warehouses', 'the count cannot be finalised'],
        ['space_reservations', 'facility_id', 'facilities', 'the booking is invisible on the pitch'],
        ['cash_sessions', 'branch_id', 'branches', 'the till belongs to no branch'],
    ];

    public function handle(): int
    {
        $findings = array_merge($this->unattributedRows(), $this->crossBranchLinks(), $this->unpriceable());

        if ($this->option('json')) {
            $this->line(json_encode($findings, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE));

            return $findings === [] ? self::SUCCESS : self::FAILURE;
        }

        if ($findings === []) {
            $this->info('✓ Branch integrity intact — every row has a branch, and every child agrees with its parent.');

            return self::SUCCESS;
        }

        $this->newLine();
        $this->error(count($findings).' problem(s) found:');
        $this->newLine();

        $this->table(
            ['Kind', 'Where', 'Rows', 'What it costs'],
            array_map(fn ($f) => [$f['kind'], $f['where'], $f['rows'], $f['impact']], $findings)
        );

        $this->newLine();
        $this->line('See docs/agent-rules/19-branch-isolation.md. Rows with no branch are');
        $this->line('usually a migration that ran before its backfill; cross-branch links');
        $this->line('usually mean a catalogue row needs splitting per branch.');

        return self::FAILURE;
    }

    /** @return array<int, array{kind: string, where: string, rows: int, impact: string}> */
    private function unattributedRows(): array
    {
        $out = [];

        foreach (self::STRICT as $table) {
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, 'branch_id')) {
                continue;
            }

            $count = DB::table($table)
                ->whereNull('branch_id')
                ->when(
                    Schema::hasColumn($table, 'deleted_at'),
                    fn ($q) => $q->whereNull('deleted_at')
                )
                ->count();

            if ($count > 0) {
                $out[] = [
                    'kind' => 'no branch',
                    'where' => $table,
                    'rows' => $count,
                    'impact' => 'invisible from every branch',
                ];
            }
        }

        return $out;
    }

    /** @return array<int, array{kind: string, where: string, rows: int, impact: string}> */
    private function crossBranchLinks(): array
    {
        $out = [];

        foreach (self::LINKS as [$child, $fk, $parent, $impact]) {
            if (! Schema::hasTable($child) || ! Schema::hasTable($parent)) {
                continue;
            }

            if (! Schema::hasColumn($child, 'branch_id') || ! Schema::hasColumn($parent, 'branch_id')) {
                continue;
            }

            if (! Schema::hasColumn($child, $fk)) {
                continue;
            }

            // `branches` is its own parent for the cash-session check; comparing
            // a branch to itself would be nonsense, so that link only tests that
            // the FK resolves.
            $parentKey = $parent === 'branches' ? 'id' : 'id';

            $query = DB::table($child.' as c')
                ->join($parent.' as p', 'p.'.$parentKey, '=', 'c.'.$fk)
                ->whereNotNull('c.branch_id');

            if ($parent === 'branches') {
                $query->whereColumn('c.branch_id', '!=', 'p.id');
            } else {
                $query->whereNotNull('p.branch_id')->whereColumn('c.branch_id', '!=', 'p.branch_id');
            }

            if (Schema::hasColumn($child, 'deleted_at')) {
                $query->whereNull('c.deleted_at');
            }

            $count = $query->count();

            if ($count > 0) {
                $out[] = [
                    'kind' => 'crosses branches',
                    'where' => $child.' → '.$parent,
                    'rows' => $count,
                    'impact' => $impact,
                ];
            }
        }

        return $out;
    }

    /**
     * A programme with live enrolments and no active price in its own branch.
     *
     * Not strictly a branch-integrity problem — a programme can simply have been
     * set up without a price — but it is the failure the branch work is most
     * often blamed for, because the symptom is identical: `لا يوجد سعر محدد` at
     * the till. Worth naming so the two are told apart.
     */
    private function unpriceable(): array
    {
        if (! Schema::hasTable('base_prices') || ! Schema::hasTable('enrollments')) {
            return [];
        }

        $count = DB::table('training_programs as p')
            ->join('training_groups as g', 'g.training_program_id', '=', 'p.id')
            ->join('enrollments as e', 'e.training_group_id', '=', 'g.id')
            ->where('e.status', 'active')
            ->whereNull('g.deleted_at')
            ->whereNotExists(function ($q) {
                $q->selectRaw('1')->from('base_prices as bp')
                    ->whereColumn('bp.priceable_id', 'p.id')
                    ->where('bp.priceable_type', 'like', '%TrainingProgram')
                    ->where('bp.is_active', true)
                    ->whereNull('bp.deleted_at')
                    ->whereColumn('bp.branch_id', 'g.branch_id');
            })
            ->distinct()
            ->count('p.id');

        return $count > 0
            ? [[
                'kind' => 'no price',
                'where' => 'training_programs',
                'rows' => $count,
                'impact' => 'active enrolments that cannot be billed',
            ]]
            : [];
    }
}
