<?php

namespace App\Console\Commands;

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

final class AuditFinancialChainCommand extends Command
{
    protected $signature = 'lottery:audit-financial-chain {--json : Output JSON only}';

    protected $description = '只读审计钱包、转账、授信、结算与收付款资金链闭环';

    public function handle(): int
    {
        $issues = array_merge(
            $this->walletIssues(),
            $this->transferIssues(),
            $this->creditIssues(),
            $this->settlementIssues(),
            $this->paymentIssues(),
        );

        if ($this->option('json')) {
            $this->line(json_encode(['issues' => $issues], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT));

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

        if ($issues === []) {
            $this->info('Financial chain audit passed.');

            return self::SUCCESS;
        }

        $this->error(sprintf('Financial chain audit found %d issue(s).', count($issues)));
        foreach ($issues as $issue) {
            $this->line(sprintf('- [%s] %s', $issue['type'], $issue['message']));
        }

        return self::FAILURE;
    }

    /**
     * @return list<array{type: string, message: string, count: int}>
     */
    private function walletIssues(): array
    {
        return $this->countIssues([
            'wallet_txn_math_bad' => [
                'message' => '已入账钱包流水的前后余额不匹配',
                'sql' => "select count(*) as cnt from wallet_txns where status = 'posted' and ((direction = 1 and balance_after <> balance_before + amount) or (direction = 2 and balance_after <> balance_before - amount) or direction not in (1,2))",
            ],
            'wallet_negative' => [
                'message' => '玩家钱包余额或冻结余额为负数',
                'sql' => 'select count(*) as cnt from player_wallets where balance < 0 or frozen_balance < 0',
            ],
            'wallet_latest_mismatch' => [
                'message' => '玩家钱包当前余额与最新已入账流水余额不一致',
                'sql' => "select count(*) as cnt from player_wallets w join wallet_txns t on t.wallet_id = w.id where t.status = 'posted' and t.id = (select max(t2.id) from wallet_txns t2 where t2.wallet_id = w.id and t2.status = 'posted') and w.balance <> t.balance_after",
            ],
        ]);
    }

    /**
     * @return list<array{type: string, message: string, count: int}>
     */
    private function transferIssues(): array
    {
        return $this->countIssues([
            'success_transfer_missing_txn' => [
                'message' => '成功转账单缺少对应钱包流水',
                'sql' => "select count(*) as cnt from transfer_orders o left join wallet_txns t on t.biz_no = o.transfer_no and t.biz_type = case when o.direction = 'out' then 'transfer_out' else 'transfer_in' end and t.status = 'posted' where o.status = 'success' and t.id is null",
            ],
            'transfer_txn_missing_order' => [
                'message' => '转账类钱包流水找不到对应转账单',
                'sql' => "select count(*) as cnt from wallet_txns t left join transfer_orders o on o.transfer_no = t.biz_no where t.status = 'posted' and t.biz_type in ('transfer_in','transfer_out','transfer_out_refund','reversal') and o.id is null",
            ],
            'transfer_amount_mismatch' => [
                'message' => '成功转账单金额与对应钱包流水金额不一致',
                'sql' => "select count(*) as cnt from transfer_orders o join wallet_txns t on t.biz_no = o.transfer_no and t.status = 'posted' and t.biz_type = case when o.direction = 'out' then 'transfer_out' else 'transfer_in' end where o.status = 'success' and o.amount <> t.amount",
            ],
        ]);
    }

    /**
     * @return list<array{type: string, message: string, count: int}>
     */
    private function creditIssues(): array
    {
        $checks = [
            'credit_account_negative' => [
                'message' => '玩家授信账户额度、已用或冻结为负数',
                'sql' => 'select count(*) as cnt from player_credit_accounts where credit_limit < 0 or used_credit < 0 or frozen_credit < 0',
            ],
            'credit_account_over_limit' => [
                'message' => '玩家已用授信加冻结授信超过授信额度',
                'sql' => 'select count(*) as cnt from player_credit_accounts where used_credit + frozen_credit > credit_limit',
            ],
            'credit_players_without_account' => [
                'message' => '信用盘玩家缺少授信账户',
                'sql' => "select count(*) as cnt from players p left join player_credit_accounts a on a.player_id = p.id where p.funding_mode = 'credit' and a.player_id is null",
            ],
            'orphan_player_credit_ledger' => [
                'message' => '玩家信用流水引用了不存在的玩家',
                'sql' => "select count(*) as cnt from credit_ledger cl left join players p on p.id = cl.owner_id where cl.owner_type = 'player' and p.id is null",
            ],
            'orphan_credit_ticket_item_ref' => [
                'message' => '信用流水引用了不存在的注单明细',
                'sql' => "select count(*) as cnt from credit_ledger cl where cl.ref_type = 'ticket_item' and not exists (select 1 from ticket_items ti where ti.id = cl.ref_id)",
            ],
            'orphan_credit_bill_ref' => [
                'message' => '信用流水引用了不存在的结算账单',
                'sql' => "select count(*) as cnt from credit_ledger cl where cl.ref_type = 'settlement_bill' and not exists (select 1 from settlement_bills sb where sb.id = cl.ref_id)",
            ],
        ];

        if (! Schema::hasColumn('players', 'funding_mode')) {
            unset($checks['credit_players_without_account']);
        }

        return $this->countIssues($checks);
    }

    /**
     * @return list<array{type: string, message: string, count: int}>
     */
    private function settlementIssues(): array
    {
        return $this->countIssues([
            'settlement_bill_math_bad' => [
                'message' => '结算账单金额闭环异常（已付 + 未付 + 坏账核销不等于应结绝对值）',
                'sql' => "select count(*) as cnt from settlement_bills where abs(net_amount) <> paid_amount + unpaid_amount + case when bill_type <> 'bad_debt' then coalesce(cast(meta_json ->> 'written_off_amount' as bigint), 0) else 0 end",
            ],
            'settlement_bill_negative_paid_unpaid' => [
                'message' => '结算账单已付或未付金额为负数',
                'sql' => 'select count(*) as cnt from settlement_bills where paid_amount < 0 or unpaid_amount < 0',
            ],
        ]);
    }

    /**
     * @return list<array{type: string, message: string, count: int}>
     */
    private function paymentIssues(): array
    {
        return $this->countIssues([
            'payment_amount_nonpositive' => [
                'message' => '收付款记录金额小于等于 0',
                'sql' => 'select count(*) as cnt from payment_records where amount <= 0',
            ],
            'confirmed_payment_missing_time' => [
                'message' => '已确认收付款记录缺少确认时间',
                'sql' => "select count(*) as cnt from payment_records where status = 'confirmed' and confirmed_at is null",
            ],
            'bill_paid_mismatch_confirmed_payments' => [
                'message' => '账单已付金额与已确认收付款汇总不一致',
                'sql' => "with p as (select settlement_bill_id, coalesce(sum(amount),0) confirmed_amount from payment_records where status = 'confirmed' group by settlement_bill_id) select count(*) as cnt from settlement_bills b left join p on p.settlement_bill_id = b.id where b.paid_amount <> coalesce(p.confirmed_amount,0)",
            ],
        ]);
    }

    /**
     * @param  array<string, array{message: string, sql: string}>  $checks
     * @return list<array{type: string, message: string, count: int}>
     */
    private function countIssues(array $checks): array
    {
        $issues = [];

        foreach ($checks as $type => $check) {
            $count = (int) (DB::selectOne($check['sql'])->cnt ?? 0);
            if ($count > 0) {
                $issues[] = [
                    'type' => $type,
                    'message' => $check['message'],
                    'count' => $count,
                ];
            }
        }

        return $issues;
    }
}
