<?php
namespace App\Repositories;

use App\Database\Connection;
use App\Models\CommissionPayoutCycle;
use PDO;

class CommissionPayoutCycleRepository
{
    private $db;

    public function __construct(Connection $db)
    {
        $this->db = $db;
    }

    public function getDb(): Connection
    {
        return $this->db;
    }

    public function findById(int $id): ?CommissionPayoutCycle
    {
        if ($id <= 0) {
            return null;
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT cpc.*,
                   COALESCE(u.display_name, CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,''))) AS salesperson_name,
                   COALESCE(m.display_name, CONCAT(COALESCE(m.first_name,''), ' ', COALESCE(m.last_name,''))) AS manager_name
            FROM commission_payout_cycles cpc
            INNER JOIN users u ON u.id = cpc.user_id
            LEFT JOIN users m ON m.id = cpc.manager_id
            WHERE cpc.id = ?
            LIMIT 1
        ");
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? new CommissionPayoutCycle($row) : null;
    }

    public function findByUserAndMonth(int $userId, string $billingMonth): ?CommissionPayoutCycle
    {
        if ($userId <= 0 || trim($billingMonth) === '') {
            return null;
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM commission_payout_cycles WHERE user_id = ? AND billing_month = ? LIMIT 1");
        $stmt->execute([$userId, $billingMonth]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? new CommissionPayoutCycle($row) : null;
    }

    public function save(CommissionPayoutCycle $cycle): int
    {
        $pdo = $this->db->getPdo();
        if (!empty($cycle->id)) {
            $stmt = $pdo->prepare("
                UPDATE commission_payout_cycles
                SET user_id = ?,
                    billing_month = ?,
                    calculated_amount = ?,
                    override_amount = ?,
                    final_payable_amount = ?,
                    manager_id = ?,
                    status = ?,
                    override_reason = ?,
                    approval_deadline = ?,
                    escalate_to_finance = ?
                WHERE id = ?
            ");
            $stmt->execute([
                (int)$cycle->user_id,
                $cycle->billing_month,
                (float)$cycle->calculated_amount,
                $cycle->override_amount !== null ? (float)$cycle->override_amount : null,
                (float)$cycle->final_payable_amount,
                $cycle->manager_id ? (int)$cycle->manager_id : null,
                $cycle->status,
                $cycle->override_reason ?: null,
                $cycle->approval_deadline ?: null,
                isset($cycle->escalate_to_finance) ? (int)$cycle->escalate_to_finance : 0,
                (int)$cycle->id,
            ]);
            return (int)$cycle->id;
        }

        $stmt = $pdo->prepare("
            INSERT INTO commission_payout_cycles
                (user_id, billing_month, calculated_amount, override_amount, final_payable_amount, manager_id, status, override_reason, approval_deadline, escalate_to_finance)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                calculated_amount = VALUES(calculated_amount),
                override_amount = VALUES(override_amount),
                final_payable_amount = VALUES(final_payable_amount),
                manager_id = VALUES(manager_id),
                status = VALUES(status),
                override_reason = VALUES(override_reason),
                approval_deadline = VALUES(approval_deadline),
                escalate_to_finance = VALUES(escalate_to_finance)
        ");
        $stmt->execute([
            (int)$cycle->user_id,
            $cycle->billing_month,
            (float)$cycle->calculated_amount,
            $cycle->override_amount !== null ? (float)$cycle->override_amount : null,
            (float)$cycle->final_payable_amount,
            $cycle->manager_id ? (int)$cycle->manager_id : null,
            $cycle->status ?: 'PENDING_APPROVAL',
            $cycle->override_reason ?: null,
            $cycle->approval_deadline ?: null,
            isset($cycle->escalate_to_finance) ? (int)$cycle->escalate_to_finance : 0,
        ]);

        $id = (int)$pdo->lastInsertId();
        if ($id > 0) {
            return $id;
        }
        $existing = $this->findByUserAndMonth((int)$cycle->user_id, (string)$cycle->billing_month);
        return $existing ? (int)$existing->id : 0;
    }

    public function getManagerQueue(string $billingMonth = '', string $status = 'PENDING_APPROVAL'): array
    {
        $pdo = $this->db->getPdo();
        $sql = "
            SELECT cpc.*,
                   COALESCE(u.display_name, CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,''))) AS salesperson_name,
                   COALESCE(m.display_name, CONCAT(COALESCE(m.first_name,''), ' ', COALESCE(m.last_name,''))) AS manager_name
            FROM commission_payout_cycles cpc
            INNER JOIN users u ON u.id = cpc.user_id
            LEFT JOIN users m ON m.id = cpc.manager_id
            WHERE 1=1
        ";
        $params = [];
        if (trim($status) !== '') {
            $sql .= " AND cpc.status = ?";
            $params[] = trim($status);
        }
        if (trim($billingMonth) !== '') {
            $sql .= " AND cpc.billing_month = ?";
            $params[] = trim($billingMonth);
        }
        $sql .= " ORDER BY cpc.approval_deadline ASC, cpc.id DESC";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return array_map(static function ($row) {
            return new CommissionPayoutCycle($row);
        }, $rows);
    }

    public function getQueueAdvanced(array $filters = []): array
    {
        $pdo = $this->db->getPdo();
        $sql = "
            SELECT cpc.*,
                   COALESCE(u.display_name, CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,''))) AS salesperson_name,
                   COALESCE(m.display_name, CONCAT(COALESCE(m.first_name,''), ' ', COALESCE(m.last_name,''))) AS manager_name
            FROM commission_payout_cycles cpc
            INNER JOIN users u ON u.id = cpc.user_id
            LEFT JOIN users m ON m.id = cpc.manager_id
            WHERE 1=1
        ";
        $params = [];
        if (!empty($filters['billing_month'])) {
            $sql .= " AND cpc.billing_month = ?";
            $params[] = trim((string)$filters['billing_month']);
        }
        if (!empty($filters['status'])) {
            $sql .= " AND cpc.status = ?";
            $params[] = trim((string)$filters['status']);
        }
        if (!empty($filters['salesperson_name'])) {
            $sql .= " AND COALESCE(u.display_name, CONCAT(COALESCE(u.first_name,''), ' ', COALESCE(u.last_name,''))) LIKE ?";
            $params[] = '%' . trim((string)$filters['salesperson_name']) . '%';
        }
        if (isset($filters['amount_min']) && $filters['amount_min'] !== '') {
            $sql .= " AND cpc.final_payable_amount >= ?";
            $params[] = (float)$filters['amount_min'];
        }
        if (isset($filters['amount_max']) && $filters['amount_max'] !== '') {
            $sql .= " AND cpc.final_payable_amount <= ?";
            $params[] = (float)$filters['amount_max'];
        }
        $sql .= " ORDER BY cpc.billing_month DESC, cpc.id DESC";

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return array_map(static function ($row) {
            return new CommissionPayoutCycle($row);
        }, $rows);
    }

    public function aggregateSettledByMonth(string $billingMonth): array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT dc.salesperson_user_id AS user_id,
                   SUM(dc.calculated_commission) AS calculated_amount
            FROM deal_commissions dc
            WHERE dc.status = 'SETTLED'
              AND DATE_FORMAT(COALESCE(dc.settled_at, dc.updated_at, dc.created_at), '%Y-%m') = ?
            GROUP BY dc.salesperson_user_id
        ");
        $stmt->execute([$billingMonth]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
}
