<?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 AS customer_id,
                c.account_id,
                c.name,
                c.state,
                c.postcode,
                c.created_at,
                a.email,
                a.phone,

                COALESCE(pt.purchase_count, 0) AS purchase_count,
                COALESCE(pt.total_spend, 0) AS total_spend,

                COALESCE(la.points_balance, 0) AS points,
                COALESCE(la.stamps_balance, 0) AS stamps,

                pt.last_purchase_at

            FROM customers c

            JOIN accounts a
                ON a.id = c.account_id

            LEFT JOIN (
                SELECT
                    customer_id,
                    COUNT(*) AS purchase_count,
                    SUM(purchase_amount) AS total_spend,
                    MAX(created_at) AS 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
                    customer_id,
                    points_balance,
                    stamps_balance
                FROM loyalty_accounts
                WHERE merchant_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
                ) AS total_spend,

                COUNT(
                    CASE
                        WHEN pt.status = "COMPLETED"
                        THEN 1
                    END
                ) AS purchase_count,

                COALESCE(la.points_balance, 0) AS points,
                COALESCE(la.stamps_balance, 0) AS stamps

            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 = ?

            LEFT JOIN loyalty_accounts la
                ON la.customer_id = c.id
               AND la.merchant_id = ?

            WHERE c.id = ?

            GROUP BY
                c.id,
                a.id,
                la.id
            '
        );

        $s->execute([
            $merchantId,
            $merchantId,
            $customerId
        ]);

        return $s->fetch() ?: null;
    }

    public function purchases(int $merchantId, int $customerId): array
    {
        $s = $this->db->prepare(
            '
            SELECT
                pt.*,
                p.name AS program_name,
                b.name AS 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 AS reward_name,
                r.metric,
                r.threshold,
                p.name AS 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();
    }

    public function loyalty(int $merchantId, int $customerId): array
    {
        $s = $this->db->prepare(
            '
            SELECT
                lt.*,
                la.customer_id,
                la.merchant_id
            FROM loyalty_transactions lt

            JOIN loyalty_accounts la
                ON la.id = lt.loyalty_account_id

            WHERE la.merchant_id = ?
              AND la.customer_id = ?

            ORDER BY lt.created_at DESC
            LIMIT 100
            '
        );

        $s->execute([
            $merchantId,
            $customerId
        ]);

        return $s->fetchAll();
    }
}