<?php
namespace App\Repositories;

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

class CommissionRuleRepository
{
    private $db;
    private $columnCache = [];
    private $columnMetaCache = [];

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

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

    public function getAll(array $filters = []): array
    {
        $sql = "SELECT * FROM commission_rules WHERE 1=1";
        $params = [];
        if ($this->hasColumn('deleted_at')) {
            $sql .= " AND deleted_at IS NULL";
        }

        if (!empty($filters['status']) && $this->hasColumn('status')) {
            $sql .= " AND status = ?";
            $params[] = (string)$filters['status'];
        }
        if (!empty($filters['kpi_source'])) {
            if ($this->hasColumn('kpi_source')) {
                $sql .= " AND kpi_source = ?";
                $params[] = (string)$filters['kpi_source'];
            } elseif ($this->hasColumn('kpi_key')) {
                $sql .= " AND kpi_key = ?";
                $params[] = (string)$filters['kpi_source'];
            }
        }

        $sql .= " ORDER BY updated_at DESC, id DESC";
        $stmt = $this->db->getPdo()->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;
        }
        $sql = "SELECT * FROM commission_rules WHERE id = ?";
        if ($this->hasColumn('deleted_at')) {
            $sql .= " AND deleted_at IS NULL";
        }
        $sql .= " LIMIT 1";
        $stmt = $this->db->getPdo()->prepare($sql);
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ? new CommissionRule($row) : null;
    }

    public function findActiveByKpiSource(string $kpiSource): array
    {
        $kpiSource = trim($kpiSource);
        $where = [];
        $params = [];
        if ($this->hasColumn('status')) {
            $where[] = "status = 'active'";
        } elseif ($this->hasColumn('is_active')) {
            $where[] = "is_active = 1";
        }
        if ($this->hasColumn('kpi_source')) {
            $where[] = "kpi_source = ?";
            $params[] = $kpiSource;
        } elseif ($this->hasColumn('kpi_key')) {
            $where[] = "kpi_key = ?";
            $params[] = $kpiSource;
        }
        if ($this->hasColumn('deleted_at')) {
            $where[] = "deleted_at IS NULL";
        }
        if (empty($where)) {
            return [];
        }
        $orderBy = $this->hasColumn('kpi_min_threshold') ? "kpi_min_threshold ASC, id DESC" : "id DESC";
        $stmt = $this->db->getPdo()->prepare("
            SELECT *
            FROM commission_rules
            WHERE " . implode(' AND ', $where) . "
            ORDER BY " . $orderBy
        );
        $stmt->execute($params);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return array_map(static function ($row) {
            return new CommissionRule($row);
        }, $rows);
    }

    public function save(CommissionRule $rule): int
    {
        $pdo = $this->db->getPdo();
        $kpiColumn = $this->hasColumn('kpi_source') ? 'kpi_source' : ($this->hasColumn('kpi_key') ? 'kpi_key' : null);
        $ruleTypeColumn = $this->hasColumn('rule_type') ? 'rule_type' : null;
        $statusColumn = $this->hasColumn('status') ? 'status' : ($this->hasColumn('is_active') ? 'is_active' : null);
        $titleColumn = $this->hasColumn('title') ? 'title' : null;
        $salespersonColumn = $this->hasColumn('salesperson_user_id') ? 'salesperson_user_id' : null;
        $productColumn = $this->hasColumn('product_id') ? 'product_id' : null;
        $validFromColumn = $this->hasColumn('valid_from') ? 'valid_from' : null;
        $validToColumn = $this->hasColumn('valid_to') ? 'valid_to' : null;
        $targetUsersColumn = $this->hasColumn('target_users') ? 'target_users' : null;
        $requestedServicesColumn = $this->hasColumn('requested_services') ? 'requested_services' : null;
        $minColumn = $this->hasColumn('kpi_min_threshold') ? 'kpi_min_threshold' : ($this->hasColumn('min_achievement_percent') ? 'min_achievement_percent' : null);
        $maxColumn = $this->hasColumn('kpi_max_threshold') ? 'kpi_max_threshold' : ($this->hasColumn('max_achievement_percent') ? 'max_achievement_percent' : null);
        $baseAmountColumn = $this->hasColumn('base_amount') ? 'base_amount' : ($this->hasColumn('fixed_budget_amount') ? 'fixed_budget_amount' : null);
        $rewardPercentColumn = $this->hasColumn('reward_percentage') ? 'reward_percentage' : ($this->hasColumn('base_rate_percent') ? 'base_rate_percent' : null);

        if (!empty($rule->id)) {
            $setParts = [];
            $params = [];

            if ($kpiColumn !== null) {
                $setParts[] = "{$kpiColumn} = ?";
                $params[] = trim((string)$rule->kpi_source);
            }
            if ($ruleTypeColumn !== null) {
                $setParts[] = "{$ruleTypeColumn} = ?";
                $params[] = trim((string)($rule->rule_type ?? 'TIERED_CASHFLOW')) ?: 'TIERED_CASHFLOW';
            }
            if ($targetUsersColumn !== null) {
                $setParts[] = "{$targetUsersColumn} = ?";
                $params[] = is_array($rule->target_users) ? json_encode($rule->target_users, JSON_UNESCAPED_UNICODE) : (string)$rule->target_users;
            }
            if ($titleColumn !== null) {
                $titleValue = trim((string)($rule->title ?? ''));
                if ($titleValue === '') {
                    $titleValue = $this->buildAutoTitle($rule);
                }
                $setParts[] = "{$titleColumn} = ?";
                $params[] = $titleValue;
            }
            if ($salespersonColumn !== null) {
                $firstTarget = isset($rule->salesperson_user_id) && (int)$rule->salesperson_user_id > 0
                    ? (int)$rule->salesperson_user_id
                    : null;
                if ($firstTarget === null) {
                    $targets = is_array($rule->target_users) ? $rule->target_users : json_decode((string)$rule->target_users, true);
                    $targets = is_array($targets) ? $targets : [];
                    foreach ($targets as $target) {
                        if (is_numeric($target) && (int)$target > 0) {
                            $firstTarget = (int)$target;
                            break;
                        }
                    }
                }
                $setParts[] = "{$salespersonColumn} = ?";
                $params[] = $firstTarget;
            }
            if ($productColumn !== null) {
                $setParts[] = "{$productColumn} = ?";
                $params[] = $this->extractFirstProductId($rule);
            }
            if ($validFromColumn !== null) {
                $setParts[] = "{$validFromColumn} = ?";
                $params[] = trim((string)($rule->valid_from ?? '')) ?: date('Y-m-d');
            }
            if ($validToColumn !== null) {
                $setParts[] = "{$validToColumn} = ?";
                $params[] = trim((string)($rule->valid_to ?? '')) ?: null;
            }
            if ($requestedServicesColumn !== null) {
                $setParts[] = "{$requestedServicesColumn} = ?";
                $params[] = is_array($rule->requested_services) ? json_encode($rule->requested_services, JSON_UNESCAPED_UNICODE) : (string)$rule->requested_services;
            }
            if ($minColumn !== null) {
                $setParts[] = "{$minColumn} = ?";
                $params[] = (float)$rule->kpi_min_threshold;
            }
            if ($maxColumn !== null) {
                $setParts[] = "{$maxColumn} = ?";
                $params[] = (float)$rule->kpi_max_threshold;
            }
            if ($baseAmountColumn !== null) {
                $setParts[] = "{$baseAmountColumn} = ?";
                $params[] = (float)$rule->base_amount;
            }
            if ($rewardPercentColumn !== null) {
                $setParts[] = "{$rewardPercentColumn} = ?";
                $params[] = (float)$rule->reward_percentage;
            }
            if ($statusColumn !== null) {
                $setParts[] = "{$statusColumn} = ?";
                $params[] = ($statusColumn === 'is_active')
                    ? (trim((string)$rule->status) === 'active' ? 1 : 0)
                    : (trim((string)$rule->status) ?: 'active');
            }

            if (empty($setParts)) {
                return (int)$rule->id;
            }

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

        $columns = [];
        $params = [];
        if ($kpiColumn !== null) {
            $columns[] = $kpiColumn;
            $params[] = trim((string)$rule->kpi_source);
        }
        if ($ruleTypeColumn !== null) {
            $columns[] = $ruleTypeColumn;
            $params[] = trim((string)($rule->rule_type ?? 'TIERED_CASHFLOW')) ?: 'TIERED_CASHFLOW';
        }
        if ($targetUsersColumn !== null) {
            $columns[] = $targetUsersColumn;
            $params[] = is_array($rule->target_users) ? json_encode($rule->target_users, JSON_UNESCAPED_UNICODE) : (string)$rule->target_users;
        }
        if ($titleColumn !== null) {
            $titleValue = trim((string)($rule->title ?? ''));
            if ($titleValue === '') {
                $titleValue = $this->buildAutoTitle($rule);
            }
            $columns[] = $titleColumn;
            $params[] = $titleValue;
        }
        if ($salespersonColumn !== null) {
            $firstTarget = isset($rule->salesperson_user_id) && (int)$rule->salesperson_user_id > 0
                ? (int)$rule->salesperson_user_id
                : null;
            if ($firstTarget === null) {
                $targets = is_array($rule->target_users) ? $rule->target_users : json_decode((string)$rule->target_users, true);
                $targets = is_array($targets) ? $targets : [];
                foreach ($targets as $target) {
                    if (is_numeric($target) && (int)$target > 0) {
                        $firstTarget = (int)$target;
                        break;
                    }
                }
            }
            $columns[] = $salespersonColumn;
            $params[] = $firstTarget;
        }
        if ($productColumn !== null) {
            $columns[] = $productColumn;
            $params[] = $this->extractFirstProductId($rule);
        }
        if ($validFromColumn !== null) {
            $columns[] = $validFromColumn;
            $params[] = trim((string)($rule->valid_from ?? '')) ?: date('Y-m-d');
        }
        if ($validToColumn !== null) {
            $columns[] = $validToColumn;
            $params[] = trim((string)($rule->valid_to ?? '')) ?: null;
        }
        if ($requestedServicesColumn !== null) {
            $columns[] = $requestedServicesColumn;
            $params[] = is_array($rule->requested_services) ? json_encode($rule->requested_services, JSON_UNESCAPED_UNICODE) : (string)$rule->requested_services;
        }
        if ($minColumn !== null) {
            $columns[] = $minColumn;
            $params[] = (float)$rule->kpi_min_threshold;
        }
        if ($maxColumn !== null) {
            $columns[] = $maxColumn;
            $params[] = (float)$rule->kpi_max_threshold;
        }
        if ($baseAmountColumn !== null) {
            $columns[] = $baseAmountColumn;
            $params[] = (float)$rule->base_amount;
        }
        if ($rewardPercentColumn !== null) {
            $columns[] = $rewardPercentColumn;
            $params[] = (float)$rule->reward_percentage;
        }
        if ($statusColumn !== null) {
            $columns[] = $statusColumn;
            $params[] = ($statusColumn === 'is_active')
                ? (trim((string)$rule->status) === 'active' ? 1 : 0)
                : (trim((string)$rule->status) ?: 'active');
        }

        if (empty($columns)) {
            throw new \RuntimeException('commission_rules schema mismatch: no writable columns detected');
        }

        $placeholders = implode(',', array_fill(0, count($columns), '?'));
        $stmt = $pdo->prepare("INSERT INTO commission_rules (" . implode(',', $columns) . ") VALUES ($placeholders)");
        $stmt->execute($params);
        return (int)$pdo->lastInsertId();
    }

    private function hasColumn(string $column): bool
    {
        if (isset($this->columnCache[$column])) {
            return $this->columnCache[$column];
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT COUNT(*) AS cnt
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'commission_rules'
              AND COLUMN_NAME = ?
        ");
        $stmt->execute([$column]);
        $exists = ((int)($stmt->fetch(PDO::FETCH_ASSOC)['cnt'] ?? 0)) > 0;
        $this->columnCache[$column] = $exists;
        return $exists;
    }

    private function buildAutoTitle(CommissionRule $rule): string
    {
        $kpi = trim((string)($rule->kpi_source ?? $rule->kpi_key ?? 'rule'));
        return 'AUTO-' . $kpi . '-' . date('YmdHis');
    }

    private function extractFirstProductId(CommissionRule $rule): ?int
    {
        $services = is_array($rule->requested_services)
            ? $rule->requested_services
            : json_decode((string)$rule->requested_services, true);
        if (!is_array($services)) {
            return null;
        }
        foreach ($services as $svc) {
            if (!is_array($svc)) {
                continue;
            }
            $candidate = trim((string)($svc['product_ids'] ?? ''));
            if ($candidate === '') {
                continue;
            }
            $first = trim((string)explode(',', $candidate)[0]);
            if (ctype_digit($first) && (int)$first > 0) {
                return (int)$first;
            }
        }
        return null;
    }
}
