<?php
declare(strict_types=1);

namespace App\Models;

class WithdrawalRequest extends BaseModel
{
    protected string $table = 'withdrawal_requests';

    public const STATUSES = ['pending', 'approved', 'processing', 'sent', 'rejected', 'cancelled'];

    public function createRequest(array $data): array
    {
        $statement = $this->db()->prepare(
            'INSERT INTO withdrawal_requests
                (creator_id, user_id, bank_account_id, user_bank_account_id, site_bank_account_id, amount,
                 currency, status, user_note, requested_at, created_at, updated_at)
             VALUES
                (:creator_id, :user_id, :bank_account_id, :user_bank_account_id, :site_bank_account_id, :amount,
                 :currency, "pending", :user_note, NOW(), NOW(), NOW())'
        );
        $statement->execute([
            'creator_id' => (int) $data['user_id'],
            'user_id' => (int) $data['user_id'],
            'bank_account_id' => $data['bank_account_id'] ?? null,
            'user_bank_account_id' => (int) $data['user_bank_account_id'],
            'site_bank_account_id' => $data['site_bank_account_id'] ?? null,
            'amount' => round((float) $data['amount'], 2),
            'currency' => strtoupper(substr((string) ($data['currency'] ?? 'TRY'), 0, 3)) ?: 'TRY',
            'user_note' => $data['user_note'] ?? null,
        ]);

        return $this->findDetailed((int) $this->db()->lastInsertId()) ?? [];
    }

    public function findDetailed(int $id): ?array
    {
        $statement = $this->db()->prepare(
            'SELECT wr.*,
                    uba.account_holder AS user_account_holder,
                    uba.bank_name AS user_bank_name,
                    uba.iban AS user_iban,
                    sba.bank_name AS site_bank_name,
                    sba.account_holder AS site_account_holder,
                    sba.iban AS site_iban,
                    u.username,
                    COALESCE(up.display_name, p.display_name, u.nickname, u.username) AS creator_name
             FROM withdrawal_requests wr
             LEFT JOIN user_bank_accounts uba ON uba.id = wr.user_bank_account_id
             LEFT JOIN site_bank_accounts sba ON sba.id = wr.site_bank_account_id
             LEFT JOIN users u ON u.id = COALESCE(wr.user_id, wr.creator_id)
             LEFT JOIN user_profiles up ON up.user_id = u.id
             LEFT JOIN profiles p ON p.user_id = u.id
             WHERE wr.id = :id
             LIMIT 1'
        );
        $statement->execute(['id' => $id]);
        $row = $statement->fetch();

        return $row ?: null;
    }

    public function listForUser(int $userId, int $limit = 50): array
    {
        $statement = $this->db()->prepare(
            'SELECT wr.*,
                    uba.account_holder AS user_account_holder,
                    uba.bank_name AS user_bank_name,
                    uba.iban AS user_iban
             FROM withdrawal_requests wr
             LEFT JOIN user_bank_accounts uba ON uba.id = wr.user_bank_account_id
             WHERE wr.user_id = :user_id OR wr.creator_id = :creator_id
             ORDER BY wr.id DESC
             LIMIT :limit'
        );
        $statement->bindValue('user_id', $userId, \PDO::PARAM_INT);
        $statement->bindValue('creator_id', $userId, \PDO::PARAM_INT);
        $statement->bindValue('limit', max(1, min(150, $limit)), \PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function listForAdmin(?string $status = null, int $limit = 100): array
    {
        $sql = 'SELECT wr.*,
                       uba.account_holder AS user_account_holder,
                       uba.bank_name AS user_bank_name,
                       uba.iban AS user_iban,
                       sba.bank_name AS site_bank_name,
                       u.username,
                       u.email,
                       COALESCE(up.display_name, p.display_name, u.nickname, u.username) AS creator_name
                FROM withdrawal_requests wr
                LEFT JOIN user_bank_accounts uba ON uba.id = wr.user_bank_account_id
                LEFT JOIN site_bank_accounts sba ON sba.id = wr.site_bank_account_id
                LEFT JOIN users u ON u.id = COALESCE(wr.user_id, wr.creator_id)
                LEFT JOIN user_profiles up ON up.user_id = u.id
                LEFT JOIN profiles p ON p.user_id = u.id';
        $params = [];
        if ($status !== null && in_array($status, self::STATUSES, true)) {
            $sql .= ' WHERE wr.status = :status';
            $params['status'] = $status;
        }
        $sql .= ' ORDER BY wr.id DESC LIMIT :limit';

        $statement = $this->db()->prepare($sql);
        foreach ($params as $key => $value) {
            $statement->bindValue($key, $value);
        }
        $statement->bindValue('limit', max(1, min(300, $limit)), \PDO::PARAM_INT);
        $statement->execute();

        return $statement->fetchAll();
    }

    public function transition(int $id, string $status, array $data = []): ?array
    {
        if (!in_array($status, self::STATUSES, true)) {
            throw new \InvalidArgumentException('Gecersiz cekim durumu.');
        }

        $timeColumn = match ($status) {
            'approved' => 'approved_at',
            'processing' => 'processing_at',
            'sent' => 'sent_at',
            'rejected' => 'rejected_at',
            'cancelled' => 'cancelled_at',
            default => null,
        };

        $sql = 'UPDATE withdrawal_requests
                SET status = :status,
                    admin_id = COALESCE(:admin_id, admin_id),
                    admin_note = COALESCE(:admin_note, admin_note),
                    transaction_reference = COALESCE(:transaction_reference, transaction_reference),
                    receipt_file = COALESCE(:receipt_file, receipt_file),
                    updated_at = NOW()';
        if ($timeColumn !== null) {
            $sql .= ", {$timeColumn} = COALESCE({$timeColumn}, NOW())";
        }
        if ($status === 'sent') {
            $sql .= ', processed_at = COALESCE(processed_at, NOW())';
        }
        $sql .= ' WHERE id = :id';

        $statement = $this->db()->prepare($sql);
        $statement->execute([
            'id' => $id,
            'status' => $status,
            'admin_id' => $data['admin_id'] ?? null,
            'admin_note' => $data['admin_note'] ?? null,
            'transaction_reference' => $data['transaction_reference'] ?? null,
            'receipt_file' => $data['receipt_file'] ?? null,
        ]);

        return $this->findDetailed($id);
    }

    public function canTransition(string $from, string $to): bool
    {
        $from = $from === 'paid' ? 'sent' : $from;

        return match ($to) {
            'approved' => $from === 'pending',
            'processing' => $from === 'approved',
            'sent' => $from === 'processing',
            'rejected' => in_array($from, ['pending', 'approved', 'processing'], true),
            'cancelled' => $from === 'pending',
            default => false,
        };
    }
}
