<?php
namespace App\Repositories;

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

class CommissionRuleRepository
{
    private $db;

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

    public function getAll(array $filters = []): array
    {
        $pdo = $this->db->getPdo();
        $sql = "
            SELECT cr.*,
                   COALESCE(u.display_name, CONCAT(COALESCE(u.first_name, ''), ' ', COALESCE(u.last_name, ''))) AS salesperson_name,
                   p.name AS product_name
            FROM commission_rules cr
            INNER JOIN users u ON u.id = cr.salesperson_user_id
            INNER JOIN products p ON p.id = cr.product_id
            WHERE 1=1
        ";
        $params = [];

        if (isset($filters['salesperson_user_id']) && (int)$filters['salesperson_user_id'] > 0) {
            $sql .= " AND cr.salesperson_user_id = ?";
            $params[] = (int)$filters['salesperson_user_id'];
        }
        if (isset($filters['product_id']) && (int)$filters['product_id'] > 0) {
            $sql .= " AND cr.product_id = ?";
            $params[] = (int)$filters['product_id'];
        }
        if (isset($filters['is_active']) && $filters['is_active'] !== '') {
            $sql .= " AND cr.is_active = ?";
            $params[] = (int)$filters['is_active'];
        }

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

    public function findById(int $id): ?CommissionRule
    {
        if ($id <= 0) {
            return null;
        }
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM commission_rules WHERE id = ? LIMIT 1");
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? new CommissionRule($row) : null;
    }

    public function findApplicableRule(int $salespersonUserId, int $productId, string $dealDate): ?CommissionRule
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT *
            FROM commission_rules
            WHERE salesperson_user_id = ?
              AND product_id = ?
              AND is_active = 1
              AND valid_from <= ?
              AND (valid_to IS NULL OR valid_to >= ?)
            ORDER BY valid_from DESC, id DESC
            LIMIT 1
        ");
        $stmt->execute([$salespersonUserId, $productId, $dealDate, $dealDate]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? new CommissionRule($row) : null;
    }

    public function save(CommissionRule $rule): int
    {
        $pdo = $this->db->getPdo();
        if (!empty($rule->id)) {
            $stmt = $pdo->prepare("
                UPDATE commission_rules
                SET salesperson_user_id = ?,
                    product_id = ?,
                    base_rate_percent = ?,
                    valid_from = ?,
                    valid_to = ?,
                    is_active = ?,
                    created_by_user_id = ?
                WHERE id = ?
            ");
            $stmt->execute([
                (int)$rule->salesperson_user_id,
                (int)$rule->product_id,
                (float)$rule->base_rate_percent,
                $rule->valid_from,
                $rule->valid_to ?: null,
                isset($rule->is_active) ? (int)$rule->is_active : 1,
                $rule->created_by_user_id ? (int)$rule->created_by_user_id : null,
                (int)$rule->id,
            ]);
            return (int)$rule->id;
        }

        $stmt = $pdo->prepare("
            INSERT INTO commission_rules
                (salesperson_user_id, product_id, base_rate_percent, valid_from, valid_to, is_active, created_by_user_id)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            (int)$rule->salesperson_user_id,
            (int)$rule->product_id,
            (float)$rule->base_rate_percent,
            $rule->valid_from,
            $rule->valid_to ?: null,
            isset($rule->is_active) ? (int)$rule->is_active : 1,
            $rule->created_by_user_id ? (int)$rule->created_by_user_id : null,
        ]);
        return (int)$pdo->lastInsertId();
    }
}
