<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class CustomerModel
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    // ── List ──────────────────────────────────────────────────────────────────

    public function list(
        int $page,
        int $perPage,
        string $search = '',
        string $filter = 'all',
        string $sort   = 'spent'
    ): array {
        $where  = ['1=1'];
        $params = [];

        if ($search !== '') {
            $where[]  = '(u.name LIKE ? OR u.email LIKE ? OR u.phone LIKE ?)';
            $like     = '%' . $search . '%';
            $params[] = $like; $params[] = $like; $params[] = $like;
        }

        match ($filter) {
            'active'      => ($where[] = 'u.is_active = 1 AND c.blacklisted = 0'),
            'inactive'    => ($where[] = 'u.is_active = 0'),
            'blacklisted' => ($where[] = 'c.blacklisted = 1'),
            'vip'         => ($where[] = 'delivered_orders >= 15'),
            default       => null,
        };

        $orderBy = match ($sort) {
            'orders'  => 'total_orders DESC',
            'joined'  => 'u.created_at DESC',
            'name'    => 'u.name ASC',
            default   => 'total_spent DESC',
        };

        $whereStr = implode(' AND ', $where);

        $total = (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM (
                SELECT c.id
                  FROM customers c
                  JOIN users u ON u.id = c.user_id
                 WHERE $whereStr
             ) sub",
            $params
        );

        $offset = ($page - 1) * $perPage;
        $rows   = $this->db->fetchAll(
            "SELECT c.id, c.blacklisted, c.blacklisted_at,
                    u.name, u.email, u.phone, u.is_active, u.created_at, u.last_login,
                    COUNT(o.id)                                        AS total_orders,
                    COALESCE(SUM(CASE WHEN o.status='delivered' THEN 1 ELSE 0 END), 0) AS delivered_orders,
                    COALESCE(SUM(CASE WHEN o.status='delivered' THEN o.total_amount END), 0) AS total_spent,
                    COALESCE(SUM(CASE WHEN o.status='cancelled' THEN 1 ELSE 0 END), 0) AS cancelled_orders
               FROM customers c
               JOIN users u ON u.id = c.user_id
               LEFT JOIN orders o ON o.customer_id = c.id
              WHERE $whereStr
              GROUP BY c.id
              ORDER BY $orderBy
              LIMIT ? OFFSET ?",
            [...$params, $perPage, $offset]
        );

        return ['total' => $total, 'rows' => $rows];
    }

    public function count(): int
    {
        return (int) $this->db->fetchValue("SELECT COUNT(*) FROM customers");
    }

    public function newThisWeek(): int
    {
        return (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM customers WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)"
        );
    }

    // ── Detail ────────────────────────────────────────────────────────────────

    public function find(int $id): ?array
    {
        return $this->db->fetchOne(
            "SELECT c.*,
                    u.name, u.email, u.phone, u.is_active, u.created_at, u.last_login,
                    bu.name AS blacklisted_by_name,
                    COUNT(o.id)                                                          AS total_orders,
                    COALESCE(SUM(CASE WHEN o.status='delivered' THEN 1    ELSE 0 END),0) AS delivered_orders,
                    COALESCE(SUM(CASE WHEN o.status='delivered' THEN o.total_amount END),0) AS total_spent,
                    COALESCE(SUM(CASE WHEN o.status='cancelled' THEN 1    ELSE 0 END),0) AS cancelled_orders,
                    COALESCE(AVG(CASE WHEN o.status='delivered' THEN o.total_amount END),0) AS avg_order
               FROM customers c
               JOIN users u ON u.id = c.user_id
               LEFT JOIN orders o ON o.customer_id = c.id
               LEFT JOIN users bu ON bu.id = c.blacklisted_by
              WHERE c.id = ?
              GROUP BY c.id",
            [$id]
        );
    }

    public function addresses(int $customerId): array
    {
        return $this->db->fetchAll(
            "SELECT * FROM customer_addresses WHERE customer_id = ? ORDER BY is_default DESC, id ASC",
            [$customerId]
        );
    }

    public function recentOrders(int $customerId, int $limit = 20): array
    {
        return $this->db->fetchAll(
            "SELECT id, order_number, status, total_amount, payment_method, order_type, placed_at
               FROM orders WHERE customer_id = ?
              ORDER BY placed_at DESC LIMIT ?",
            [$customerId, $limit]
        );
    }

    // ── Update ────────────────────────────────────────────────────────────────

    public function update(int $customerId, array $userData, string $note): void
    {
        $userId = (int) $this->db->fetchValue(
            "SELECT user_id FROM customers WHERE id = ?", [$customerId]
        );

        $this->db->update('users', $userData, ['id' => $userId]);
        $this->db->update('customers', ['admin_note' => $note ?: null], ['id' => $customerId]);
    }

    public function toggle(int $customerId): string
    {
        $row = $this->db->fetchOne(
            "SELECT u.id AS uid, u.is_active FROM customers c JOIN users u ON u.id = c.user_id WHERE c.id = ?",
            [$customerId]
        );
        $new = $row['is_active'] ? 0 : 1;
        $this->db->update('users', ['is_active' => $new], ['id' => $row['uid']]);
        return $new ? 'activated' : 'deactivated';
    }

    public function blacklist(int $customerId, string $reason, int $adminId): void
    {
        $this->db->update('customers', [
            'blacklisted'      => 1,
            'blacklist_reason' => $reason,
            'blacklisted_at'   => date('Y-m-d H:i:s'),
            'blacklisted_by'   => $adminId,
        ], ['id' => $customerId]);

        // Also deactivate account
        $userId = (int) $this->db->fetchValue(
            "SELECT user_id FROM customers WHERE id = ?", [$customerId]
        );
        $this->db->update('users', ['is_active' => 0], ['id' => $userId]);
    }

    public function unblacklist(int $customerId): void
    {
        $this->db->update('customers', [
            'blacklisted'      => 0,
            'blacklist_reason' => null,
            'blacklisted_at'   => null,
            'blacklisted_by'   => null,
        ], ['id' => $customerId]);

        $userId = (int) $this->db->fetchValue(
            "SELECT user_id FROM customers WHERE id = ?", [$customerId]
        );
        $this->db->update('users', ['is_active' => 1], ['id' => $userId]);
    }

    public function delete(int $customerId): bool
    {
        $hasOrders = (int) $this->db->fetchValue(
            "SELECT COUNT(*) FROM orders WHERE customer_id = ?", [$customerId]
        );
        if ($hasOrders) return false;

        $userId = (int) $this->db->fetchValue(
            "SELECT user_id FROM customers WHERE id = ?", [$customerId]
        );
        $this->db->query("DELETE FROM users WHERE id = ?", [$userId]);
        return true;
    }

    // ── Export ────────────────────────────────────────────────────────────────

    public function exportAll(): array
    {
        return $this->db->fetchAll(
            "SELECT u.name, u.email, u.phone, u.is_active,
                    c.blacklisted, c.created_at,
                    COUNT(o.id) AS total_orders,
                    COALESCE(SUM(CASE WHEN o.status='delivered' THEN o.total_amount END),0) AS total_spent
               FROM customers c
               JOIN users u ON u.id = c.user_id
               LEFT JOIN orders o ON o.customer_id = c.id
              GROUP BY c.id
              ORDER BY total_spent DESC"
        );
    }

    // ── Blacklist check (used by ShopController) ──────────────────────────────

    public function isBlacklisted(int $userId): bool
    {
        return (bool) $this->db->fetchValue(
            "SELECT c.blacklisted FROM customers c JOIN users u ON u.id = c.user_id WHERE u.id = ?",
            [$userId]
        );
    }
}
