<?php
namespace App\Repositories;

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

class DealCommissionRepository
{
    private $db;

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

    public function findByDealDidarId(string $dealDidarId): ?DealCommission
    {
        if ($dealDidarId === '') {
            return null;
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM deal_commissions WHERE deal_didar_id = ? LIMIT 1");
        $stmt->execute([$dealDidarId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? new DealCommission($row) : null;
    }

    public function getAll(array $filters = []): array
    {
        $pdo = $this->db->getPdo();
        $sql = "
            SELECT dc.*,
                   d.title AS deal_title,
                   COALESCE(su.display_name, CONCAT(COALESCE(su.first_name, ''), ' ', COALESCE(su.last_name, ''))) AS salesperson_name,
                   p.name AS product_name,
                   COALESCE(mu.display_name, CONCAT(COALESCE(mu.first_name, ''), ' ', COALESCE(mu.last_name, ''))) AS manager_name,
                   COALESCE(fu.display_name, CONCAT(COALESCE(fu.first_name, ''), ' ', COALESCE(fu.last_name, ''))) AS finance_name,
                   COALESCE(pu.display_name, CONCAT(COALESCE(pu.first_name, ''), ' ', COALESCE(pu.last_name, ''))) AS settled_by_name
            FROM deal_commissions dc
            INNER JOIN deals d ON d.didar_deal_id = dc.deal_didar_id
            INNER JOIN users su ON su.id = dc.salesperson_user_id
            LEFT JOIN products p ON p.id = dc.product_id
            LEFT JOIN users mu ON mu.id = dc.manager_reviewed_by_user_id
            LEFT JOIN users fu ON fu.id = dc.finance_reviewed_by_user_id
            LEFT JOIN users pu ON pu.id = dc.settled_by_user_id
            WHERE 1=1
        ";
        $params = [];

        if (!empty($filters['status'])) {
            $sql .= " AND dc.status = ?";
            $params[] = trim((string)$filters['status']);
        }
        if (isset($filters['salesperson_user_id']) && (int)$filters['salesperson_user_id'] > 0) {
            $sql .= " AND dc.salesperson_user_id = ?";
            $params[] = (int)$filters['salesperson_user_id'];
        }

        $sql .= " ORDER BY dc.created_at DESC, dc.id DESC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return array_map(static function ($row) {
            return new DealCommission($row);
        }, $rows);
    }

    public function save(DealCommission $commission): int
    {
        $pdo = $this->db->getPdo();
        if (!empty($commission->id)) {
            $stmt = $pdo->prepare("
                UPDATE deal_commissions
                SET salesperson_user_id = ?,
                    product_id = ?,
                    commission_rule_id = ?,
                    ncr_amount = ?,
                    applied_rate_percent = ?,
                    kpi_multiplier = ?,
                    calculated_commission = ?,
                    status = ?,
                    manager_reviewed_by_user_id = ?,
                    manager_reviewed_at = ?,
                    finance_reviewed_by_user_id = ?,
                    finance_reviewed_at = ?,
                    settled_by_user_id = ?,
                    settled_at = ?,
                    notes = ?
                WHERE id = ?
            ");
            $stmt->execute([
                (int)$commission->salesperson_user_id,
                $commission->product_id ? (int)$commission->product_id : null,
                $commission->commission_rule_id ? (int)$commission->commission_rule_id : null,
                (float)$commission->ncr_amount,
                (float)$commission->applied_rate_percent,
                (float)$commission->kpi_multiplier,
                (float)$commission->calculated_commission,
                $commission->status,
                $commission->manager_reviewed_by_user_id ? (int)$commission->manager_reviewed_by_user_id : null,
                $commission->manager_reviewed_at ?: null,
                $commission->finance_reviewed_by_user_id ? (int)$commission->finance_reviewed_by_user_id : null,
                $commission->finance_reviewed_at ?: null,
                $commission->settled_by_user_id ? (int)$commission->settled_by_user_id : null,
                $commission->settled_at ?: null,
                $commission->notes ?: null,
                (int)$commission->id,
            ]);
            return (int)$commission->id;
        }

        $stmt = $pdo->prepare("
            INSERT INTO deal_commissions
                (deal_didar_id, salesperson_user_id, product_id, commission_rule_id, ncr_amount, applied_rate_percent, kpi_multiplier, calculated_commission, status, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE
                salesperson_user_id = VALUES(salesperson_user_id),
                product_id = VALUES(product_id),
                commission_rule_id = VALUES(commission_rule_id),
                ncr_amount = VALUES(ncr_amount),
                applied_rate_percent = VALUES(applied_rate_percent),
                kpi_multiplier = VALUES(kpi_multiplier),
                calculated_commission = VALUES(calculated_commission),
                status = VALUES(status),
                notes = VALUES(notes)
        ");
        $stmt->execute([
            $commission->deal_didar_id,
            (int)$commission->salesperson_user_id,
            $commission->product_id ? (int)$commission->product_id : null,
            $commission->commission_rule_id ? (int)$commission->commission_rule_id : null,
            (float)$commission->ncr_amount,
            (float)$commission->applied_rate_percent,
            (float)$commission->kpi_multiplier,
            (float)$commission->calculated_commission,
            $commission->status ?: 'PENDING_MANAGER_REVIEW',
            $commission->notes ?: null,
        ]);

        $id = (int)$pdo->lastInsertId();
        if ($id > 0) {
            return $id;
        }
        $existing = $this->findByDealDidarId((string)$commission->deal_didar_id);
        return $existing ? (int)$existing->id : 0;
    }

    public function updateStatus(int $id, string $status, int $actorUserId = 0): bool
    {
        if ($id <= 0 || $status === '') {
            return false;
        }
        $pdo = $this->db->getPdo();
        $status = strtoupper(trim($status));

        $setParts = ['status = ?'];
        $params = [$status];

        if ($status === 'PENDING_FINANCE') {
            $setParts[] = 'manager_reviewed_by_user_id = ?';
            $setParts[] = 'manager_reviewed_at = NOW()';
            $params[] = $actorUserId > 0 ? $actorUserId : null;
        } elseif ($status === 'READY_FOR_PAYOUT') {
            $setParts[] = 'finance_reviewed_by_user_id = ?';
            $setParts[] = 'finance_reviewed_at = NOW()';
            $params[] = $actorUserId > 0 ? $actorUserId : null;
        } elseif ($status === 'SETTLED') {
            $setParts[] = 'settled_by_user_id = ?';
            $setParts[] = 'settled_at = NOW()';
            $params[] = $actorUserId > 0 ? $actorUserId : null;
        }

        $params[] = $id;
        $sql = "UPDATE deal_commissions SET " . implode(', ', $setParts) . " WHERE id = ?";
        $stmt = $pdo->prepare($sql);
        return $stmt->execute($params);
    }
}
