<?php
/**
 * Payment Model
 * Maps to `payments` table — one record per order
 */

require_once __DIR__ . '/../Core/Model.php';

class Payment extends Model {
    protected string $table = 'payments';

    // ── Retrieval ──────────────────────────────────────────────────

    public function findByOrderId(int $orderId): ?array {
        $sql = "SELECT p.*, pa.account_name, pa.account_no as account_number, pa.bank_name,
                       pa.promptpay_id, pa.type as account_method
                FROM `payments` p
                LEFT JOIN `payment_accounts` pa ON pa.id = p.payment_account_id
                WHERE p.order_id = :order_id
                LIMIT 1";
        return $this->fetchOne($sql, [':order_id' => $orderId]);
    }

    public function findByOrderNo(string $orderNo): ?array {
        $sql = "SELECT p.*, pa.account_name, pa.account_no as account_number, pa.bank_name,
                       pa.promptpay_id, pa.type as account_method
                FROM `payments` p
                LEFT JOIN `payment_accounts` pa ON pa.id = p.payment_account_id
                WHERE p.order_no = :order_no
                LIMIT 1";
        return $this->fetchOne($sql, [':order_no' => $orderNo]);
    }

    public function findByOrderNoForCustomer(string $orderNo, int $customerId): ?array {
        $sql = "SELECT p.*, pa.account_name, pa.account_no as account_number, pa.bank_name,
                       pa.promptpay_id, pa.type as account_method
                FROM `payments` p
                LEFT JOIN `payment_accounts` pa ON pa.id = p.payment_account_id
                WHERE p.order_no = :order_no AND p.customer_id = :customer_id
                LIMIT 1";
        return $this->fetchOne($sql, [':order_no' => $orderNo, ':customer_id' => $customerId]);
    }

    // ── Status Management ──────────────────────────────────────────

    public function updateStatus(int $paymentId, string $newStatus, array $extra = []): bool {
        $data = array_merge(['payment_status' => $newStatus, 'updated_at' => date('Y-m-d H:i:s')], $extra);
        return $this->update($paymentId, $data);
    }

    // ── Admin Listing ──────────────────────────────────────────────

    public function getAdminPayments(array $filters = [], int $limit = 20, int $offset = 0): array {
        [$whereSql, $params] = $this->buildAdminFilters($filters);

        $sql = "SELECT p.*,
                       CONCAT(u.first_name, ' ', u.last_name) as customer_name, u.email as customer_email,
                       o.order_status,
                       (SELECT COUNT(*) FROM `payment_attempts` pa WHERE pa.payment_id = p.id) as attempt_count,
                       (SELECT COUNT(*) FROM `payment_attempts` pa WHERE pa.payment_id = p.id AND pa.status = 'pending') as pending_attempts
                FROM `payments` p
                JOIN `users` u ON u.id = p.customer_id
                JOIN `orders` o ON o.id = p.order_id
                WHERE {$whereSql}
                ORDER BY p.created_at DESC
                LIMIT :limit OFFSET :offset";

        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function countAdminPayments(array $filters = []): int {
        [$whereSql, $params] = $this->buildAdminFilters($filters);
        $sql = "SELECT COUNT(*) FROM `payments` p
                JOIN `users` u ON u.id = p.customer_id
                JOIN `orders` o ON o.id = p.order_id
                WHERE {$whereSql}";
        $stmt = $this->db->prepare($sql);
        foreach ($params as $k => $v) {
            $stmt->bindValue($k, $v);
        }
        $stmt->execute();
        return (int)$stmt->fetchColumn();
    }

    private function buildAdminFilters(array $filters): array {
        $conditions = ['1=1'];
        $params = [];

        if (!empty($filters['q'])) {
            $conditions[] = "(p.order_no LIKE :q OR u.username LIKE :q OR u.first_name LIKE :q OR u.email LIKE :q)";
            $params[':q'] = '%' . trim($filters['q']) . '%';
        }
        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            $conditions[] = "p.payment_status = :status";
            $params[':status'] = $filters['status'];
        }
        if (!empty($filters['method']) && $filters['method'] !== 'all') {
            $conditions[] = "p.payment_method = :method";
            $params[':method'] = $filters['method'];
        }
        if (!empty($filters['date_from'])) {
            $conditions[] = "p.created_at >= :date_from";
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $conditions[] = "p.created_at <= :date_to";
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }

        return [implode(' AND ', $conditions), $params];
    }

    // ── Stats ──────────────────────────────────────────────────────

    public function getPaymentStats(): array {
        $sql = "SELECT
                  COUNT(*) as total,
                  SUM(CASE WHEN payment_status = 'pending' THEN 1 ELSE 0 END) as pending,
                  SUM(CASE WHEN payment_status = 'awaiting_verification' THEN 1 ELSE 0 END) as awaiting,
                  SUM(CASE WHEN payment_status = 'confirmed' THEN 1 ELSE 0 END) as confirmed,
                  SUM(CASE WHEN payment_status = 'failed' THEN 1 ELSE 0 END) as failed,
                  SUM(CASE WHEN payment_status = 'expired' THEN 1 ELSE 0 END) as expired,
                  SUM(CASE WHEN payment_status IN ('refund_pending','refunded') THEN 1 ELSE 0 END) as refunds,
                  SUM(CASE WHEN payment_status = 'confirmed' THEN amount ELSE 0 END) as total_confirmed_amount,
                  SUM(CASE WHEN DATE(created_at) = CURDATE() THEN 1 ELSE 0 END) as today_total,
                  SUM(CASE WHEN DATE(created_at) = CURDATE() AND payment_status = 'confirmed' THEN amount ELSE 0 END) as today_amount
                FROM `payments`";
        return $this->fetchOne($sql) ?: [];
    }

    // ── Expiry ──────────────────────────────────────────────────────

    public function getExpiredPayments(): array {
        $sql = "SELECT * FROM `payments`
                WHERE payment_status IN ('pending','awaiting_verification')
                AND expires_at < NOW()";
        return $this->fetchAll($sql);
    }
}