<?php

namespace App\Console\Commands;

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

class VerifyCyclosMigration extends Command
{
    protected $signature = 'verify:cyclos-migration {source_db? : Name of the source Cyclos database}';
    protected $description = 'Verifies the Cyclos migration: member counts, account balances, gift accounts, and deleted profile cleanup.';

    private int $passed = 0;
    private int $failed = 0;
    private int $warnings = 0;

    public function handle()
    {
        $sourceDb = $this->argument('source_db') ?? cache()->get('cyclos_migration_source_db');

        if (empty($sourceDb)) {
            $sourceDb = $this->ask('Enter the name of the source Cyclos database');
        }

        if (empty($sourceDb)) {
            $this->error('Source database name is required.');
            return 1;
        }

        $destDb = env('DB_DATABASE');

        $this->info("=== Cyclos Migration Verification ===");
        $this->info("Source: {$sourceDb}  →  Destination: {$destDb}");
        $this->newLine();

        $this->checkMembers($sourceDb, $destDb);
        $this->newLine();
        $this->checkTransactions($sourceDb, $destDb);
        $this->newLine();
        $this->checkBalances($sourceDb, $destDb);
        $this->newLine();
        $this->checkGiftAccounts($destDb);
        $this->newLine();
        $this->checkDeletedProfiles($sourceDb, $destDb);
        $this->newLine();

        $this->info("=== Summary ===");
        $this->info("  <fg=green>PASS</>: {$this->passed}");
        if ($this->warnings > 0) {
            $this->info("  <fg=yellow>WARN</>: {$this->warnings}");
        }
        if ($this->failed > 0) {
            $this->error("  FAIL: {$this->failed}");
            return 1;
        }

        $this->info('<fg=green>All checks passed!</>');
        return 0;
    }

    // -------------------------------------------------------------------------
    // 1. MEMBER COUNTS
    // -------------------------------------------------------------------------
    private function checkMembers(string $sourceDb, string $destDb): void
    {
        $this->info('--- 1. Member counts ---');

        // Cyclos group_id mapping:
        //  5  = active users
        //  6  = inactive users
        //  8  = removed users
        // 13  = local banks (level I)
        // 14  = organizations
        // 15  = projects to create hours (level II banks)
        // 18  = TEST projects (organizations)
        // 22  = TEST users
        // 27  = inactive projects (organizations)

        $cyclosActive     = DB::table("{$sourceDb}.members")->where('group_id', 5)->count();
        $cyclosInactive   = DB::table("{$sourceDb}.members")->where('group_id', 6)->count();
        $cyclosRemoved    = DB::table("{$sourceDb}.members")->where('group_id', 8)->count();
        $cyclosBanksL1    = DB::table("{$sourceDb}.members")->where('group_id', 13)->count();
        $cyclosOrgs       = DB::table("{$sourceDb}.members")->where('group_id', 14)->count();
        $cyclosBanksL2    = DB::table("{$sourceDb}.members")->where('group_id', 15)->count();
        $cyclosTestOrgs   = DB::table("{$sourceDb}.members")->where('group_id', 18)->count();
        $cyclosTestUsers  = DB::table("{$sourceDb}.members")->where('group_id', 22)->count();
        $cyclosInactProj  = DB::table("{$sourceDb}.members")->where('group_id', 27)->count();

        $laravelUsers     = DB::table("{$destDb}.users")->whereNull('deleted_at')->whereNull('inactive_at')->whereNotNull('cyclos_id')->count();
        $laravelInactive  = DB::table("{$destDb}.users")->whereNotNull('inactive_at')->count();
        $laravelRemoved   = DB::table("{$destDb}.users")->whereNotNull('deleted_at')->count();
        $laravelBanks     = DB::table("{$destDb}.banks")->where('id', '!=', 1)->count(); // exclude source bank
        $laravelOrgs      = DB::table("{$destDb}.organizations")->whereNull('inactive_at')->count();
        $laravelInactOrgs = DB::table("{$destDb}.organizations")->whereNotNull('inactive_at')->count();

        $expectedUsers    = $cyclosActive + $cyclosTestUsers;
        $expectedOrgs     = $cyclosOrgs + $cyclosTestOrgs;
        $expectedBanks    = $cyclosBanksL1 + $cyclosBanksL2;

        $this->check('Active users',           $expectedUsers,   $laravelUsers);
        $this->check('Inactive users',         $cyclosInactive,  $laravelInactive);
        $this->check('Removed/deleted users',  $cyclosRemoved,   $laravelRemoved);
        $this->check('Banks (L1+L2)',          $expectedBanks,   $laravelBanks);
        $this->check('Active organizations',   $expectedOrgs,    $laravelOrgs);
        $this->check('Inactive organizations', $cyclosInactProj, $laravelInactOrgs);
    }

    // -------------------------------------------------------------------------
    // 2. TRANSACTION COUNTS
    // -------------------------------------------------------------------------
    private function checkTransactions(string $sourceDb, string $destDb): void
    {
        $this->info('--- 2. Transaction counts ---');

        $cyclosCount  = DB::table("{$sourceDb}.transfers")->count();
        $laravelTotal = DB::table("{$destDb}.transactions")->count();

        // Post-import transactions added after Cyclos import:
        //   type 5 = currency removals (for deleted profiles)
        //   type 6 = gift account migrations (migrate:cyclos-gift-accounts moves gift balances to personal accounts)
        //   type 7 = rounding corrections (one per account per year, inserted by migrate:cyclos)
        $giftMigCount      = DB::table("{$destDb}.transactions")->where('transaction_type_id', 6)->count();
        $currRemovalCount  = DB::table("{$destDb}.transactions")->where('transaction_type_id', 5)->count();
        $roundingCorrCount = DB::table("{$destDb}.transactions")->where('transaction_type_id', 7)->count();
        $laravelImported   = $laravelTotal - $giftMigCount - $currRemovalCount - $roundingCorrCount;

        $this->check('Imported transactions match Cyclos transfers', $cyclosCount, $laravelImported);
        $this->info("  (Total Laravel: {$laravelTotal} = {$laravelImported} imported + {$giftMigCount} gift migrations + {$currRemovalCount} currency removals + {$roundingCorrCount} rounding corrections)");

        // NULL account IDs — should be zero
        $nullTx = DB::select("
            SELECT COUNT(*) as cnt FROM {$destDb}.transactions
            WHERE from_account_id IS NULL OR to_account_id IS NULL
        ")[0]->cnt;
        $this->check('No transactions with NULL account IDs', 0, $nullTx);
    }

    // -------------------------------------------------------------------------
    // 3. ACCOUNT BALANCES
    // -------------------------------------------------------------------------
    private function checkBalances(string $sourceDb, string $destDb): void
    {
        $this->info('--- 3. Account balances ---');

        // Laravel system must be balanced (sum of all net balances = 0)
        $laravelNetBalance = DB::select("
            SELECT SUM(net) as total FROM (
                SELECT a.id,
                    COALESCE(SUM(CASE WHEN t.to_account_id = a.id THEN t.amount ELSE -t.amount END), 0) as net
                FROM {$destDb}.accounts a
                LEFT JOIN {$destDb}.transactions t ON t.from_account_id = a.id OR t.to_account_id = a.id
                GROUP BY a.id
            ) balances
        ")[0]->total;

        $this->check('Laravel system is balanced (net = 0)', 0, (int) $laravelNetBalance);

        // Compare per-account balances directly via cyclos_id mapping.
        // Cyclos stores amounts in hours, Laravel in minutes.
        // Exclude post-import transactions so we compare only the imported data:
        //   type 5 = currency removals (deleted profiles)
        //   type 6 = gift migrations (migrate:cyclos-gift-accounts moves gift balances to personal accounts)
        //   type 7 = rounding corrections (inserted by migrate:cyclos, one per account per year)
        $rows = DB::select("
            SELECT
                cyclos_type.type_id,
                ROUND(cyclos_type.cyclos_hours * 60) as cyclos_min,
                COALESCE(laravel_type.laravel_min, 0) as laravel_min
            FROM (
                SELECT a.type_id,
                    COALESCE(SUM(CASE WHEN t.to_account_id = a.id THEN t.amount ELSE -t.amount END), 0) as cyclos_hours
                FROM {$sourceDb}.accounts a
                LEFT JOIN {$sourceDb}.transfers t ON t.from_account_id = a.id OR t.to_account_id = a.id
                GROUP BY a.type_id
            ) cyclos_type
            LEFT JOIN (
                SELECT ca.type_id,
                    COALESCE(SUM(CASE WHEN t.to_account_id = la.id THEN t.amount ELSE -t.amount END), 0) as laravel_min
                FROM {$sourceDb}.accounts ca
                INNER JOIN {$destDb}.accounts la ON ca.id = la.cyclos_id
                LEFT JOIN {$destDb}.transactions t ON (t.from_account_id = la.id OR t.to_account_id = la.id)
                    AND t.transaction_type_id NOT IN (5, 6, 7)
                GROUP BY ca.type_id
            ) laravel_type ON cyclos_type.type_id = laravel_type.type_id
            ORDER BY cyclos_type.type_id
        ");

        $typeNames = [
            1 => 'Debit account',
            2 => 'Community account',
            3 => 'Voucher account',
            4 => 'Organization account',
            5 => 'Work accounts (all owners)',
            6 => 'Gift accounts',
            7 => 'Project accounts',
        ];

        // Types 5 and 7 (work and project accounts) are checked combined because
        // some profiles are intentionally remapped between these types during migration.
        $combined = [5 => ['cyclos' => 0, 'laravel' => 0], 7 => ['cyclos' => 0, 'laravel' => 0]];
        foreach ($rows as $row) {
            if (in_array($row->type_id, [5, 7])) {
                $combined[$row->type_id]['cyclos'] = $row->cyclos_min;
                $combined[$row->type_id]['laravel'] = $row->laravel_min;
                continue;
            }
            $label = $typeNames[$row->type_id] ?? "Account type {$row->type_id}";
            $this->checkBalance($label, $row->cyclos_min / 60, $row->laravel_min / 60);
        }
        $combinedCyclos = ($combined[5]['cyclos'] + $combined[7]['cyclos']) / 60;
        $combinedLaravel = ($combined[5]['laravel'] + $combined[7]['laravel']) / 60;
        $this->checkBalance('Work + Project accounts combined (remappings allowed)', $combinedCyclos, $combinedLaravel);
    }

    // -------------------------------------------------------------------------
    // 4. GIFT ACCOUNTS
    // -------------------------------------------------------------------------
    private function checkGiftAccounts(string $destDb): void
    {
        $this->info('--- 4. Gift account cleanup ---');

        // All gift accounts should be marked inactive
        $activeGiftAccounts = DB::table("{$destDb}.accounts")
            ->where('name', 'gift')
            ->whereNull('inactive_at')
            ->count();
        $this->check('All gift accounts marked inactive', 0, $activeGiftAccounts);

        // All gift account net balances should be 0 (migrated away)
        $nonZeroGiftBalances = DB::select("
            SELECT COUNT(*) as cnt
            FROM (
                SELECT a.id,
                    COALESCE(SUM(CASE WHEN t.to_account_id = a.id THEN t.amount ELSE -t.amount END), 0) as net
                FROM {$destDb}.accounts a
                LEFT JOIN {$destDb}.transactions t ON t.from_account_id = a.id OR t.to_account_id = a.id
                WHERE a.name = 'gift'
                GROUP BY a.id
                HAVING ABS(net) > 0
            ) nonzero
        ");
        $this->check('All gift account balances are zero after migration', 0, $nonZeroGiftBalances[0]->cnt);

        // Gift migration transactions (type 6) should exist and move from gift → personal/org
        $giftMigrations = DB::table("{$destDb}.transactions as t")
            ->join("{$destDb}.accounts as fa", 't.from_account_id', '=', 'fa.id')
            ->join("{$destDb}.accounts as ta", 't.to_account_id', '=', 'ta.id')
            ->where('t.transaction_type_id', 6)
            ->where('fa.name', 'gift')
            ->whereIn('ta.name', ['personal', 'organization', 'banking system'])
            ->count();
        $totalGiftMigrations = DB::table("{$destDb}.transactions")->where('transaction_type_id', 6)->count();
        $this->check('Gift migration transactions go from gift → work account', $totalGiftMigrations, $giftMigrations);
    }

    // -------------------------------------------------------------------------
    // 5. DELETED PROFILE CLEANUP
    // -------------------------------------------------------------------------
    private function checkDeletedProfiles(string $sourceDb, string $destDb): void
    {
        $this->info('--- 5. Deleted profile cleanup ---');

        // Removed users (group_id 8) should be soft-deleted in Laravel
        $removedCyclos = DB::table("{$sourceDb}.members")->where('group_id', 8)->count();
        $deletedLaravel = DB::table("{$destDb}.users")->whereNotNull('deleted_at')->count();
        $this->check('Removed Cyclos users are soft-deleted in Laravel', $removedCyclos, $deletedLaravel);

        // Deleted users should have had their balances removed (currency removals, type 5).
        // Tolerance of 6 minutes to account for rounding artifacts from hours→minutes conversion.
        $deletedUsersWithBalance = DB::select("
            SELECT COUNT(*) as cnt
            FROM (
                SELECT u.id,
                    COALESCE(SUM(CASE WHEN t.to_account_id = a.id THEN t.amount ELSE -t.amount END), 0) as net
                FROM {$destDb}.users u
                INNER JOIN {$destDb}.accounts a ON a.accountable_id = u.id AND a.accountable_type = 'App\\\\Models\\\\User'
                LEFT JOIN {$destDb}.transactions t ON t.from_account_id = a.id OR t.to_account_id = a.id
                WHERE u.deleted_at IS NOT NULL
                GROUP BY u.id
                HAVING ABS(net) > 6
            ) nonzero
        ");
        $this->check('Deleted users have zero remaining balance (tolerance: 6min)', 0, $deletedUsersWithBalance[0]->cnt);

        // Accounts of deleted users should exist but with zero balance
        $deletedUserAccountsCount = DB::table("{$destDb}.accounts as a")
            ->join("{$destDb}.users as u", function ($join) use ($destDb) {
                $join->on('a.accountable_id', '=', 'u.id')
                     ->where('a.accountable_type', '=', 'App\\Models\\User');
            })
            ->whereNotNull('u.deleted_at')
            ->count();

        if ($deletedUserAccountsCount > 0) {
            $this->warn("  Deleted users still have {$deletedUserAccountsCount} account records (expected — accounts are kept for transaction history)");
            $this->warnings++;
        } else {
            $this->info("  <fg=green>PASS</> No accounts found for deleted users (all cleaned up)");
            $this->passed++;
        }

        // Currency removal transactions (type 5) are optional — only present if deleted users had balances.
        // Balance check above already confirms deleted users have zero balance, so 0 here is also valid.
        $currRemovalCount = DB::table("{$destDb}.transactions")->where('transaction_type_id', 5)->count();
        $this->info("  <fg=green>INFO</> Currency removal transactions: {$currRemovalCount}");
    }

    // -------------------------------------------------------------------------
    // Helpers
    // -------------------------------------------------------------------------
    private function check(string $label, $expected, $actual): void
    {
        if ($expected == $actual) {
            $this->info("  <fg=green>PASS</> {$label}: {$actual}");
            $this->passed++;
        } else {
            $this->error("  FAIL  {$label}: expected={$expected} actual={$actual} (diff=" . ($actual - $expected) . ")");
            $this->failed++;
        }
    }

    private function checkBalance(string $label, float $cyclosHours, float $laravelHours, float $toleranceHours = 0.1): void
    {
        $diff = abs($cyclosHours - $laravelHours);
        if ($diff <= $toleranceHours) {
            $this->info(sprintf("  <fg=green>PASS</> %s: %.2fh (diff: %.4fh)", $label, $laravelHours, $cyclosHours - $laravelHours));
            $this->passed++;
        } else {
            $this->error(sprintf("  FAIL  %s: cyclos=%.2fh laravel=%.2fh diff=%.4fh", $label, $cyclosHours, $laravelHours, $cyclosHours - $laravelHours));
            $this->failed++;
        }
    }
}