<?php
declare(strict_types=1);
namespace Favo\Services;

use PDO;

final class CRMService
{
    public function __construct(private PDO $db) {}

    public function customers(int $merchantId, string $q='', int $limit=100): array
    {
        $limit=max(1,min(200,$limit)); $like='%'.trim($q).'%';
        $sql='SELECT c.id customer_id,c.account_id,c.name,c.state,c.postcode,c.created_at,
                     a.email,a.phone,
                     COALESCE(pt.purchase_count,0) purchase_count,
                     COALESCE(pt.total_spend,0) total_spend,
                     COALESCE(la.points,0) points,
                     COALESCE(la.stamps,0) stamps,
                     pt.last_purchase_at
              FROM customers c
              JOIN accounts a ON a.id=c.account_id
              LEFT JOIN (
                SELECT customer_id,COUNT(*) purchase_count,SUM(purchase_amount) total_spend,MAX(created_at) last_purchase_at
                FROM purchase_transactions WHERE merchant_id=? AND status="COMPLETED"
                GROUP BY customer_id
              ) pt ON pt.customer_id=c.id
              LEFT JOIN (
                SELECT la.customer_id,MAX(la.points) points,MAX(la.stamps) stamps
                FROM loyalty_accounts la WHERE la.merchant_id=? GROUP BY la.customer_id
              ) la ON la.customer_id=c.id
              WHERE EXISTS (SELECT 1 FROM purchase_transactions px WHERE px.merchant_id=? AND px.customer_id=c.id)
                 OR EXISTS (SELECT 1 FROM customer_events ce WHERE ce.merchant_id=? AND ce.customer_id=c.id)
              AND (c.name LIKE ? OR a.email LIKE ? OR a.phone LIKE ?)
              ORDER BY COALESCE(pt.last_purchase_at,c.created_at) DESC
              LIMIT '.$limit;
        $s=$this->db->prepare($sql); $s->execute([$merchantId,$merchantId,$merchantId,$merchantId,$like,$like,$like]);
        return $s->fetchAll();
    }

    public function profile(int $merchantId,int $customerId): ?array
    {
        $s=$this->db->prepare(
            'SELECT c.*,a.email,a.phone,
                    COALESCE(SUM(CASE WHEN pt.status="COMPLETED" THEN pt.purchase_amount ELSE 0 END),0) total_spend,
                    COUNT(CASE WHEN pt.status="COMPLETED" THEN 1 END) purchase_count
             FROM customers c JOIN accounts a ON a.id=c.account_id
             LEFT JOIN purchase_transactions pt ON pt.customer_id=c.id AND pt.merchant_id=?
             WHERE c.id=? GROUP BY c.id,a.id'
        );
        $s->execute([$merchantId,$customerId]); return $s->fetch() ?: null;
    }

    public function purchases(int $merchantId,int $customerId): array
    {
        $s=$this->db->prepare(
            'SELECT pt.*,p.name program_name,b.name branch_name
             FROM purchase_transactions pt
             LEFT JOIN programs p ON p.id=pt.program_id
             LEFT JOIN branches b ON b.id=pt.branch_id
             WHERE pt.merchant_id=? AND pt.customer_id=?
             ORDER BY pt.created_at DESC LIMIT 100'
        ); $s->execute([$merchantId,$customerId]); return $s->fetchAll();
    }

    public function events(int $merchantId,int $customerId): array
    {
        $s=$this->db->prepare(
            'SELECT * FROM customer_events WHERE merchant_id=? AND customer_id=? ORDER BY created_at DESC LIMIT 100'
        ); $s->execute([$merchantId,$customerId]); return $s->fetchAll();
    }

    public function rewards(int $merchantId,int $customerId): array
    {
        $s=$this->db->prepare(
            'SELECT ru.*,r.name reward_name,r.metric,r.threshold,p.name program_name
             FROM reward_unlocks ru
             JOIN rewards r ON r.id=ru.reward_id
             LEFT JOIN programs p ON p.id=ru.program_id
             WHERE ru.merchant_id=? AND ru.customer_id=?
             ORDER BY ru.created_at DESC LIMIT 100'
        ); $s->execute([$merchantId,$customerId]); return $s->fetchAll();
    }
}