<?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,
                   (
                       SELECT COUNT(*)
                       FROM commission_payout_cycle_lines cpcl_count
                       WHERE cpcl_count.payout_cycle_id = cpc.id
                   ) AS commission_line_count,
                   (
                       SELECT GROUP_CONCAT(DISTINCT cl_status.status ORDER BY cl_status.status SEPARATOR ',')
                       FROM commission_payout_cycle_lines cpcl_status
                       INNER JOIN commission_lines cl_status ON cl_status.id = cpcl_status.commission_line_id
                       WHERE cpcl_status.payout_cycle_id = cpc.id
                   ) AS commission_line_statuses
            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 cl.beneficiary_user_id AS user_id,
                   SUM(cl.calculated_commission) AS calculated_amount,
                   GROUP_CONCAT(cl.id ORDER BY cl.id SEPARATOR ',') AS commission_line_ids,
                   COUNT(cl.id) AS commission_line_count
            FROM commission_lines cl
            LEFT JOIN commission_payout_cycle_lines cpcl ON cpcl.commission_line_id = cl.id
            LEFT JOIN commission_payout_cycles cpc ON cpc.id = cpcl.payout_cycle_id
            LEFT JOIN payment_records pr ON pr.id = cl.payment_record_id
            WHERE cl.billing_month = ?
              AND cl.status = 'CALCULATED'
              AND (cpcl.id IS NULL OR cpc.status = 'PENDING_APPROVAL')
              AND NOT (
                  COALESCE(pr.payment_sequence, 1) > 1
                  AND NOT EXISTS (
                      SELECT 1
                      FROM payment_records prev
                      INNER JOIN commission_lines prior_line
                        ON prior_line.payment_record_id = prev.id
                       AND COALESCE(prior_line.status, 'CALCULATED') <> 'VOID'
                       AND prior_line.calculated_commission > 0
                       AND prior_line.id < cl.id
                      WHERE (
                              (pr.deal_id IS NOT NULL AND prev.deal_id = pr.deal_id)
                           OR (COALESCE(pr.deal_didar_id, '') <> '' AND prev.deal_didar_id = pr.deal_didar_id)
                      )
                        AND prev.id <> pr.id
                        AND prev.amount > 0
                        AND UPPER(COALESCE(prev.refund_status, 'NONE')) <> 'FULL'
                        AND COALESCE(prev.is_deleted_placeholder, 0) = 0
                        AND COALESCE(prev.payment_sequence, 1) <= 1
                        AND COALESCE(DATE(prev.effective_date), DATE(prev.deposit_date), DATE(prev.payment_date), DATE(prev.cheque_collection_date), DATE(prev.registered_at), DATE(prev.created_at))
                            <= COALESCE(DATE(pr.effective_date), DATE(pr.deposit_date), DATE(pr.payment_date), DATE(pr.cheque_collection_date), DATE(pr.registered_at), DATE(pr.created_at))
                  )
              )
            GROUP BY cl.beneficiary_user_id
        ");
        $stmt->execute([$billingMonth]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function aggregateSettledByEffectiveDateRange(string $dateFrom, string $dateTo): array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT cl.beneficiary_user_id AS user_id,
                   SUM(cl.calculated_commission) AS calculated_amount,
                   GROUP_CONCAT(cl.id ORDER BY cl.id SEPARATOR ',') AS commission_line_ids,
                   COUNT(cl.id) AS commission_line_count
            FROM commission_lines cl
            LEFT JOIN commission_payout_cycle_lines cpcl ON cpcl.commission_line_id = cl.id
            LEFT JOIN commission_payout_cycles cpc ON cpc.id = cpcl.payout_cycle_id
            WHERE cl.effective_date >= ?
              AND cl.effective_date <= ?
              AND cl.status = 'CALCULATED'
              AND (cpcl.id IS NULL OR cpc.status = 'PENDING_APPROVAL')
            GROUP BY cl.beneficiary_user_id
        ");
        $stmt->execute([$dateFrom, $dateTo]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function attachLinesToCycle(int $cycleId, array $lineIds): int
    {
        if ($cycleId <= 0 || empty($lineIds)) {
            return 0;
        }
        $lineIds = array_values(array_unique(array_filter(array_map('intval', $lineIds), static function ($id) {
            return $id > 0;
        })));
        if (empty($lineIds)) {
            return 0;
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            INSERT IGNORE INTO commission_payout_cycle_lines (payout_cycle_id, commission_line_id)
            VALUES (?, ?)
        ");
        $attached = 0;
        foreach ($lineIds as $lineId) {
            $stmt->execute([$cycleId, $lineId]);
            $attached += $stmt->rowCount();
        }
        return $attached;
    }

    public function getCycleLines(int $cycleId): array
    {
        if ($cycleId <= 0) {
            return [];
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT cl.*,
                   pr.payment_uuid,
                   pr.payment_sequence,
                   pr.amount AS payment_amount,
                   pr.method AS payment_method,
                   pr.payment_date,
                   pr.deposit_date,
                   pr.effective_date AS payment_effective_date,
                   pr.payment_owner_name_snapshot,
                   pr.cheque_status,
                   pr.cheque_collection_status,
                   pr.cheque_collection_date,
                   pr.raw_snapshot_json AS payment_raw_snapshot_json,
                   d.id AS local_deal_id,
                   d.didar_deal_id AS local_deal_didar_id,
                   d.title AS deal_title,
                   d.code AS deal_code,
                   d.invoice_code AS deal_invoice_code,
                   d.contact_didar_id AS deal_contact_didar_id,
                   d.person_id AS deal_person_id,
                   CONCAT(COALESCE(pe.first_name, ''), ' ', COALESCE(pe.last_name, '')) AS customer_name,
                   pe.id AS person_id,
                   pe.didar_contact_id AS contact_didar_id,
                   pe.customer_code,
                   pe.mobile_phone AS customer_mobile,
                   COALESCE(
                       NULLIF(lead_owner.display_name, ''),
                       NULLIF(TRIM(CONCAT(COALESCE(lead_owner.first_name, ''), ' ', COALESCE(lead_owner.last_name, ''))), ''),
                       pe.owner_didar_id,
                       ''
                   ) AS current_lead_owner_name,
                   p.name AS product_name,
                   cr.title AS rule_title,
                   cr.rule_type AS rule_type,
                   cr.valid_from AS rule_valid_from,
                   cr.valid_to AS rule_valid_to,
                   cra.assigned_at AS assignment_assigned_at,
                   cra.disabled_at AS assignment_disabled_at
            FROM commission_lines cl
            INNER JOIN commission_payout_cycle_lines cpcl ON cpcl.commission_line_id = cl.id
            LEFT JOIN payment_records pr ON pr.id = cl.payment_record_id
            LEFT JOIN deals d ON d.id = pr.deal_id
            LEFT JOIN persons pe ON pe.id = d.person_id
            LEFT JOIN users lead_owner ON lead_owner.didar_user_id = pe.owner_didar_id
            LEFT JOIN products p ON p.id = cl.product_id
            LEFT JOIN commission_rules cr ON cr.id = cl.commission_rule_id
            LEFT JOIN commission_rule_assignments cra ON cra.id = cl.commission_rule_assignment_id
            WHERE cpcl.payout_cycle_id = ?
            ORDER BY cl.effective_date ASC, cl.payment_record_id ASC, cl.id ASC
        ");
        $stmt->execute([$cycleId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function getCycleLineStatusCounts(int $cycleId): array
    {
        if ($cycleId <= 0) {
            return [];
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT cl.status, COUNT(*) AS row_count
            FROM commission_lines cl
            INNER JOIN commission_payout_cycle_lines cpcl ON cpcl.commission_line_id = cl.id
            WHERE cpcl.payout_cycle_id = ?
            GROUP BY cl.status
        ");
        $stmt->execute([$cycleId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        $counts = [];
        foreach ($rows as $row) {
            $counts[(string)$row['status']] = (int)$row['row_count'];
        }
        return $counts;
    }

    public function updateCycleLinesStatus(int $cycleId, string $nextStatus, array $allowedCurrentStatuses): int
    {
        if ($cycleId <= 0 || trim($nextStatus) === '' || empty($allowedCurrentStatuses)) {
            return 0;
        }
        $allowedCurrentStatuses = array_values(array_unique(array_filter(array_map(static function ($status) {
            return strtoupper(trim((string)$status));
        }, $allowedCurrentStatuses))));
        if (empty($allowedCurrentStatuses)) {
            return 0;
        }
        $placeholders = implode(',', array_fill(0, count($allowedCurrentStatuses), '?'));
        $params = array_merge([strtoupper(trim($nextStatus)), $cycleId], $allowedCurrentStatuses);
        $stmt = $this->db->getPdo()->prepare("
            UPDATE commission_lines cl
            INNER JOIN commission_payout_cycle_lines cpcl ON cpcl.commission_line_id = cl.id
            SET cl.status = ?,
                cl.updated_at = NOW()
            WHERE cpcl.payout_cycle_id = ?
              AND cl.status IN ({$placeholders})
        ");
        $stmt->execute($params);
        return $stmt->rowCount();
    }
}
