<?php
namespace App\Repositories;

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

class CommissionLedgerRepository
{
    private $db;
    private $columnCache = [];

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

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

    public function store(array $data): int
    {
        $stmt = $this->db->getPdo()->prepare("
            INSERT INTO user_commissions_ledger
                (user_id, rule_id, kpi_score_recorded, calculated_amount, calculation_date)
            VALUES (?, ?, ?, ?, ?)
        ");
        $stmt->execute([
            (int)$data['user_id'],
            (int)$data['rule_id'],
            (float)$data['kpi_score_recorded'],
            (float)$data['calculated_amount'],
            (string)$data['calculation_date'],
        ]);
        return (int)$this->db->getPdo()->lastInsertId();
    }

    public function getByUser(int $userId): array
    {
        $ruleLabelSql = "'-' AS rule_title";
        if ($this->hasRuleColumn('title')) {
            $ruleLabelSql = "r.title AS rule_title";
        } elseif ($this->hasRuleColumn('kpi_source')) {
            $ruleLabelSql = "r.kpi_source AS rule_title";
        } elseif ($this->hasRuleColumn('kpi_key')) {
            $ruleLabelSql = "r.kpi_key AS rule_title";
        }

        $stmt = $this->db->getPdo()->prepare("
            SELECT l.*, {$ruleLabelSql}
            FROM user_commissions_ledger l
            INNER JOIN commission_rules r ON r.id = l.rule_id
            WHERE l.user_id = ?
            ORDER BY l.calculation_date DESC, l.id DESC
        ");
        $stmt->execute([$userId]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        return array_map(static function ($row) {
            return new CommissionLedger($row);
        }, $rows);
    }

    private function hasRuleColumn(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;
    }
}
