<?php
namespace App\Repositories;

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

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

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

    public function findByDealDidarId(string $dealDidarId): ?DealCommission
    {
        if ($dealDidarId === '') {
            return null;
        }
        $pdo = $this->db->getPdo();
        $sql = "SELECT * FROM deal_commissions WHERE deal_didar_id = ?";
        if ($this->hasColumn('deleted_at')) {
            $sql .= " AND deleted_at IS NULL";
        }
        $sql .= " ORDER BY COALESCE(deposit_date, DATE(created_at)) DESC, id DESC LIMIT 1";
        $stmt = $pdo->prepare($sql);
        $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 ($this->hasColumn('deleted_at')) {
            $sql .= " AND dc.deleted_at IS NULL";
        }

        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)) {
            $payload = $this->buildWritablePayload($commission, false);
            if (!empty($payload)) {
                $setParts = array_map(static function ($column) {
                    return "{$column} = ?";
                }, array_keys($payload));
                $params = array_values($payload);
                $params[] = (int)$commission->id;
                $stmt = $pdo->prepare('UPDATE deal_commissions SET ' . implode(', ', $setParts) . ' WHERE id = ?');
                $stmt->execute($params);
            }
            return (int)$commission->id;
        }

        $payload = $this->buildWritablePayload($commission, true);
        $columns = array_keys($payload);
        $updateColumns = array_values(array_filter($columns, static function ($column) {
            return $column !== 'deal_didar_id' && $column !== 'transaction_id' && $column !== 'deal_payment_id';
        }));
        $updates = array_map(static function ($column) {
            return "{$column} = VALUES({$column})";
        }, $updateColumns);
        $stmt = $pdo->prepare("
            INSERT INTO deal_commissions (" . implode(',', $columns) . ")
            VALUES (" . implode(',', array_fill(0, count($columns), '?')) . ")
            ON DUPLICATE KEY UPDATE " . implode(', ', $updates)
        );
        $stmt->execute(array_values($payload));

        $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);
    }

    private function buildWritablePayload(DealCommission $commission, bool $includeDealId): array
    {
        $values = [
            'deal_didar_id' => trim((string)$commission->deal_didar_id),
            'transaction_id' => $commission->transaction_id ? (int)$commission->transaction_id : null,
            'deal_payment_id' => $commission->deal_payment_id ? (int)$commission->deal_payment_id : null,
            'deposit_date' => $commission->deposit_date ?: null,
            'billing_month' => $commission->billing_month ?: ($commission->deposit_date ? substr((string)$commission->deposit_date, 0, 7) : null),
            'salesperson_user_id' => (int)$commission->salesperson_user_id,
            'product_id' => $commission->product_id ? (int)$commission->product_id : null,
            'commission_rule_id' => $commission->commission_rule_id ? (int)$commission->commission_rule_id : null,
            'commission_rule_assignment_id' => $commission->commission_rule_assignment_id ? (int)$commission->commission_rule_assignment_id : null,
            'ncr_amount' => (float)$commission->ncr_amount,
            'applied_rate_percent' => (float)$commission->applied_rate_percent,
            'calculated_commission' => (float)$commission->calculated_commission,
            'payout_cycle_id' => $commission->payout_cycle_id ? (int)$commission->payout_cycle_id : null,
            'status' => $commission->status ?: 'PENDING_MANAGER_REVIEW',
            'manager_reviewed_by_user_id' => $commission->manager_reviewed_by_user_id ? (int)$commission->manager_reviewed_by_user_id : null,
            'manager_reviewed_at' => $commission->manager_reviewed_at ?: null,
            'finance_reviewed_by_user_id' => $commission->finance_reviewed_by_user_id ? (int)$commission->finance_reviewed_by_user_id : null,
            'finance_reviewed_at' => $commission->finance_reviewed_at ?: null,
            'settled_by_user_id' => $commission->settled_by_user_id ? (int)$commission->settled_by_user_id : null,
            'settled_at' => $commission->settled_at ?: null,
            'deleted_by_user_id' => $commission->deleted_by_user_id ? (int)$commission->deleted_by_user_id : null,
            'notes' => $commission->notes ?: null,
            'deleted_at' => $commission->deleted_at ?: null,
        ];

        $payload = [];
        foreach ($values as $column => $value) {
            if (!$includeDealId && $column === 'deal_didar_id') {
                continue;
            }
            if ($this->hasColumn($column)) {
                $payload[$column] = $value;
            }
        }
        return $payload;
    }

    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 = 'deal_commissions'
              AND COLUMN_NAME = ?
        ");
        $stmt->execute([$column]);
        $exists = ((int)($stmt->fetch(PDO::FETCH_ASSOC)['cnt'] ?? 0)) > 0;
        $this->columnCache[$column] = $exists;
        return $exists;
    }
}
