<?php
/**
 * Refund Model
 * Maps to `refunds` table
 * Source of Truth: `refunds` (id, order_id, customer_id, store_id, amount, reason, status, admin_id, admin_note, processed_at, created_at, updated_at)
 */

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

class Refund extends Model {
    protected string $table = 'refunds';

    public function findById($id): ?array {
        $sql = "SELECT r.*, o.order_no, o.payment_id, o.payment_method, o.total_amount as order_total,
                       s.store_name, s.store_slug,
                       CONCAT(u.first_name, ' ', u.last_name) as requested_by_name, u.email as requested_by_email, u.phone as customer_phone,
                       CONCAT(a.first_name, ' ', a.last_name) as reviewed_by_name,
                       r.admin_note as review_note
                FROM `refunds` r
                JOIN `orders` o ON o.id = r.order_id
                JOIN `users` u ON u.id = r.customer_id
                LEFT JOIN `stores` s ON s.id = r.store_id
                LEFT JOIN `users` a ON a.id = r.admin_id
                WHERE r.id = :id
                LIMIT 1";
        return $this->fetchOne($sql, [':id' => $id]);
    }

    public function findByPaymentId(int $paymentId): array {
        $sql = "SELECT r.*, o.order_no,
                       CONCAT(u.first_name, ' ', u.last_name) as requested_by_name,
                       CONCAT(a.first_name, ' ', a.last_name) as reviewed_by_name
                FROM `refunds` r
                JOIN `orders` o ON o.id = r.order_id
                JOIN `users` u ON u.id = r.customer_id
                LEFT JOIN `users` a ON a.id = r.admin_id
                WHERE o.payment_id = :payment_id
                ORDER BY r.created_at DESC";
        return $this->fetchAll($sql, [':payment_id' => $paymentId]);
    }

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

        $sql = "SELECT r.*, o.order_no, s.store_name, s.store_slug,
                       CONCAT(u.first_name, ' ', u.last_name) as requested_by_name, u.email as requested_by_email,
                       CONCAT(a.first_name, ' ', a.last_name) as reviewed_by_name,
                       r.admin_note as review_note
                FROM `refunds` r
                JOIN `orders` o ON o.id = r.order_id
                JOIN `users` u ON u.id = r.customer_id
                LEFT JOIN `stores` s ON s.id = r.store_id
                LEFT JOIN `users` a ON a.id = r.admin_id
                WHERE {$whereSql}
                ORDER BY r.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 countAdminRefunds(array $filters = []): int {
        [$whereSql, $params] = $this->buildFilters($filters);
        $sql = "SELECT COUNT(*)
                FROM `refunds` r
                JOIN `orders` o ON o.id = r.order_id
                JOIN `users` u ON u.id = r.customer_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 buildFilters(array $filters): array {
        $conditions = ['1=1'];
        $params = [];

        if (!empty($filters['status']) && $filters['status'] !== 'all') {
            if ($filters['status'] === 'completed') {
                $conditions[] = "r.status IN ('completed','refunded')";
            } else {
                $conditions[] = "r.status = :status";
                $params[':status'] = $filters['status'];
            }
        }
        if (!empty($filters['q'])) {
            $conditions[] = "(o.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['date_from'])) {
            $conditions[] = "r.created_at >= :date_from";
            $params[':date_from'] = $filters['date_from'] . ' 00:00:00';
        }
        if (!empty($filters['date_to'])) {
            $conditions[] = "r.created_at <= :date_to";
            $params[':date_to'] = $filters['date_to'] . ' 23:59:59';
        }

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

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

    public function hasActiveRefund(int $orderId): bool {
        $sql = "SELECT COUNT(*) FROM `refunds`
                WHERE order_id = :order_id AND status IN ('pending','approved')";
        $stmt = $this->db->prepare($sql);
        $stmt->execute([':order_id' => $orderId]);
        return (int)$stmt->fetchColumn() > 0;
    }

    public function getRefundStats(): array {
        $sql = "SELECT
                  COUNT(*) as total,
                  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
                  SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) as approved,
                  SUM(CASE WHEN status IN ('completed','refunded') THEN 1 ELSE 0 END) as completed,
                  SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) as rejected,
                  SUM(CASE WHEN status IN ('completed','refunded') THEN amount ELSE 0 END) as total_refunded
                FROM `refunds`";
        return $this->fetchOne($sql) ?: [];
    }
}