<?php

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

    /**
     * Create a new payment record.
     *
     * @param array $data The payment data.
     * @return bool True on success, false on failure.
     */
    public function create(array $data): bool
    {
        $this->db->query(
            'INSERT INTO ' . $this->table . ' (user_id, amount, currency, transaction_ref, payment_gateway, status, metadata) 
             VALUES (:user_id, :amount, :currency, :transaction_ref, :payment_gateway, :status, :metadata)'
        );

        $this->db->bind(':user_id', $data['user_id']);
        $this->db->bind(':amount', $data['amount']);
        $this->db->bind(':currency', $data['currency']);
        $this->db->bind(':transaction_ref', $data['transaction_ref']);
        $this->db->bind(':payment_gateway', $data['payment_gateway']);
        $this->db->bind(':status', $data['status']);
        $this->db->bind(':metadata', $data['metadata'] ?? null);

        return $this->db->execute();
    }

    /**
     * Find a payment record by a key-value pair within its metadata JSON field.
     *
     * @param string $key The JSON key to search for (e.g., 'fine_ids').
     * @param string $value The value to match.
     * @param int $user_id The ID of the user who made the payment, for security.
     * @return object|null The payment object or null if not found.
     */
    public function findByMetadata(string $key, string $value, int $user_id): ?object
    {
        // The JSON_CONTAINS function checks if a value exists within a JSON document.
        // We construct the path to the key and the value to search for.
        $this->db->query("SELECT * FROM {$this->table} WHERE JSON_EXTRACT(metadata, '$.{$key}') = :value AND user_id = :user_id ORDER BY created_at DESC LIMIT 1");
        $this->db->bind(':value', $value);
        $this->db->bind(':user_id', $user_id);

        $row = $this->db->single();
        return $row ?: null;
    }

    /**
     * Find a payment by transaction reference.
     * @param string $ref
     * @param int $user_id
     * @return object|null
     */
    public function findByTransactionRef(string $ref, int $user_id): ?object
    {
        $this->db->query("SELECT * FROM {$this->table} WHERE transaction_ref = :ref AND user_id = :user_id");
        $this->db->bind(':ref', $ref);
        $this->db->bind(':user_id', $user_id);
        return $this->db->single() ?: null;
    }

    /**
     * Find a payment by transaction reference, regardless of user.
     * @param string $ref
     * @return object|null
     */
    public function findByRef(string $ref): ?object
    {
        $this->db->query("SELECT id FROM {$this->table} WHERE transaction_ref = :ref");
        $this->db->bind(':ref', $ref);
        $row = $this->db->single();
        return $row ?: null;
    }

    /**
     * Get payments by user ID.
     * @param int $user_id
     * @return array
     */
    public function getByUserId(int $user_id): array
    {
        $this->db->query("SELECT * FROM {$this->table} WHERE user_id = :user_id ORDER BY created_at DESC");
        $this->db->bind(':user_id', $user_id);
        return $this->db->resultSet();
    }

    /**
     * Get the total count of payments, with optional date filtering.
     *
     * @param string|null $start_date The start of the date range.
     * @param string|null $end_date The end of the date range.
     * @return int The total number of payments.
     */
    public function getTotalPaymentsCount(string $start_date = null, string $end_date = null): int
    {
        $sql = "SELECT COUNT(p.id) as total FROM {$this->table} p";
        $where_clauses = [];
        $params = [];

        if ($start_date) {
            $where_clauses[] = "p.created_at >= :start_date";
            $params[':start_date'] = $start_date . ' 00:00:00';
        }
        if ($end_date) {
            $where_clauses[] = "p.created_at <= :end_date";
            $params[':end_date'] = $end_date . ' 23:59:59';
        }

        if (!empty($where_clauses)) {
            $sql .= " WHERE " . implode(' AND ', $where_clauses);
        }

        $this->db->query($sql);
        $row = $this->db->single($params);
        return $row ? (int)$row->total : 0;
    }

    /**
     * Get all payments with user details for the admin panel.
     *
     * @param string|null $start_date The start of the date range.
     * @param string|null $end_date The end of the date range.
     * @return array An array of payment objects.
     */
    public function getAllWithDetails(string $start_date = null, string $end_date = null, int $limit = 50, int $offset = 0): array
    {
        $sql = "
            SELECT 
                p.id,
                p.transaction_ref,
                p.amount,
                p.currency,
                p.payment_gateway,
                p.status,
                p.created_at,
                COALESCE(CONCAT(u.first_name, ' ', u.last_name), '[Deleted User]') AS user_name
            FROM {$this->table} p
            LEFT JOIN users u ON p.user_id = u.id
        ";

        $where_clauses = [];
        $params = [];

        if ($start_date) {
            $where_clauses[] = "p.created_at >= :start_date";
            $params[':start_date'] = $start_date . ' 00:00:00';
        }
        if ($end_date) {
            $where_clauses[] = "p.created_at <= :end_date";
            $params[':end_date'] = $end_date . ' 23:59:59';
        }

        if (!empty($where_clauses)) {
            $sql .= " WHERE " . implode(' AND ', $where_clauses);
        }

        $sql .= " ORDER BY p.created_at DESC LIMIT :limit OFFSET :offset";

        $this->db->query($sql);
        foreach ($params as $key => $value) {
            $this->db->bind($key, $value);
        }

        $this->db->bind(':limit', $limit, PDO::PARAM_INT);
        $this->db->bind(':offset', $offset, PDO::PARAM_INT);

        return $this->db->resultSet();
    }
}