<?php

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

    public function listAll(string $methodId = '', string $search = '', string $dateFrom = '', string $dateTo = ''): array
    {
        $sql = "SELECT p.*, pm.method_name, o.order_number, o.order_type,
                       c.full_name as customer_name, c.phone as customer_phone,
                       u.full_name as received_by_name
                FROM payments p
                JOIN payment_methods pm ON pm.id = p.payment_method_id
                JOIN orders o ON o.id = p.order_id
                LEFT JOIN customers c ON c.id = o.customer_id
                LEFT JOIN users u ON u.id = p.received_by
                WHERE 1=1";
        $params = [];

        if ($methodId !== '' && $methodId !== 'ALL') {
            $sql .= " AND p.payment_method_id = :method_id";
            $params['method_id'] = $methodId;
        }
        if ($search !== '') {
            $sql .= " AND (o.order_number LIKE :s1 OR c.phone LIKE :s2 OR c.full_name LIKE :s3)";
            $params['s1'] = "%{$search}%";
            $params['s2'] = "%{$search}%";
            $params['s3'] = "%{$search}%";
        }
        if ($dateFrom !== '') {
            $sql .= " AND DATE(p.paid_at) >= :date_from";
            $params['date_from'] = $dateFrom;
        }
        if ($dateTo !== '') {
            $sql .= " AND DATE(p.paid_at) <= :date_to";
            $params['date_to'] = $dateTo;
        }
        $sql .= " ORDER BY p.id DESC LIMIT 200";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function stats(): array
    {
        $stmt = $this->db->query(
            "SELECT
                COALESCE(SUM(CASE WHEN DATE(paid_at) = CURDATE() THEN amount ELSE 0 END),0) as today_total,
                COALESCE(SUM(CASE WHEN MONTH(paid_at) = MONTH(CURDATE()) AND YEAR(paid_at) = YEAR(CURDATE()) THEN amount ELSE 0 END),0) as month_total,
                COALESCE(SUM(CASE WHEN DATE(paid_at) = CURDATE() THEN fee_amount ELSE 0 END),0) as today_fees,
                COUNT(CASE WHEN DATE(paid_at) = CURDATE() THEN 1 END) as today_count
             FROM payments WHERE status = 'PAID'"
        );
        return $stmt->fetch();
    }

    public function methodBreakdownToday(): array
    {
        return $this->db->query(
            "SELECT pm.method_name, COALESCE(SUM(p.amount),0) as total, COUNT(p.id) as cnt
             FROM payment_methods pm
             LEFT JOIN payments p ON p.payment_method_id = pm.id AND DATE(p.paid_at) = CURDATE() AND p.status = 'PAID'
             WHERE pm.is_active = 1
             GROUP BY pm.id, pm.method_name
             ORDER BY total DESC"
        )->fetchAll();
    }

    public function unpaidOrdersCount(): int
    {
        $stmt = $this->db->query(
            "SELECT COUNT(*) as cnt FROM orders o
             WHERE o.status != 'CANCELLED' AND o.id NOT IN (SELECT order_id FROM payments WHERE status='PAID')"
        );
        return (int) $stmt->fetch()['cnt'];
    }

    public function collectionTrend(int $days = 7): array
    {
        $stmt = $this->db->prepare(
            "SELECT DATE(paid_at) as d, COALESCE(SUM(amount),0) as total
             FROM payments WHERE status='PAID' AND paid_at >= DATE_SUB(CURDATE(), INTERVAL :days DAY)
             GROUP BY DATE(paid_at)"
        );
        $stmt->execute(['days' => $days - 1]);
        $map = [];
        foreach ($stmt->fetchAll() as $row) {
            $map[$row['d']] = (float) $row['total'];
        }
        $labels = [];
        $data = [];
        for ($i = $days - 1; $i >= 0; $i--) {
            $d = date('Y-m-d', strtotime("-{$i} days"));
            $labels[] = date('D', strtotime($d));
            $data[] = $map[$d] ?? 0;
        }
        return ['labels' => $labels, 'data' => $data];
    }

    public function oldestUnpaidHours(): ?float
    {
        $stmt = $this->db->query(
            "SELECT MIN(o.created_at) as oldest FROM orders o
             WHERE o.status != 'CANCELLED' AND o.id NOT IN (SELECT order_id FROM payments WHERE status='PAID')"
        );
        $row = $stmt->fetch();
        if (!$row['oldest']) {
            return null;
        }
        return round((time() - strtotime($row['oldest'])) / 3600, 1);
    }
}
