<?php
namespace App\Repositories;

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

class CommissionRuleRepository
{
    private $db;
    private $columnCache = [];
    private const WRITABLE_COLUMNS = [
        'rule_type',
        'formula_type',
        'title',
        'salesperson_user_id',
        'product_id',
        'valid_from',
        'valid_to',
        'target_users',
        'requested_services',
        'base_amount',
        'fixed_budget_amount',
        'base_rate_percent',
        'reward_percentage',
        'budget_source_type',
        'standard_tier_rate_percent',
        'collection_modifier_percent',
        'allow_collection_modifier_override',
        'is_active',
        'status',
        'created_by_user_id',
        'updated_by_user_id',
        'deleted_by_user_id',
        'deleted_at',
    ];

    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'];
        }

        $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 save(CommissionRule $rule): int
    {
        $pdo = $this->db->getPdo();
        $payload = $this->buildWritablePayload($rule);

        if (!empty($rule->id)) {
            if (empty($payload)) {
                return (int)$rule->id;
            }
            $setParts = array_map(static function ($column) {
                return "{$column} = ?";
            }, array_keys($payload));
            $params = array_values($payload);
            $params[] = (int)$rule->id;
            $stmt = $pdo->prepare('UPDATE commission_rules SET ' . implode(', ', $setParts) . ' WHERE id = ?');
            $stmt->execute($params);
            return (int)$rule->id;
        }

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

        $columns = array_keys($payload);
        $params = array_values($payload);
        $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 buildWritablePayload(CommissionRule $rule): array
    {
        $values = [
            'rule_type' => trim((string)($rule->rule_type ?? 'TIERED_RULE')) ?: 'TIERED_RULE',
            'formula_type' => trim((string)($rule->formula_type ?? '')) ?: $this->inferFormulaType($rule),
            'title' => trim((string)($rule->title ?? '')) ?: $this->buildAutoTitle($rule),
            'salesperson_user_id' => $this->resolveSalespersonUserId($rule),
            'product_id' => isset($rule->product_id) && (int)$rule->product_id > 0 ? (int)$rule->product_id : null,
            'valid_from' => trim((string)($rule->valid_from ?? '')) ?: ($this->isColumnNullable('valid_from') ? null : '1970-01-01'),
            'valid_to' => trim((string)($rule->valid_to ?? '')) ?: null,
            'target_users' => $this->jsonOrString($rule->target_users),
            'requested_services' => $this->jsonOrString($rule->requested_services),
            'base_amount' => $this->nullableFloat($rule->base_amount),
            'fixed_budget_amount' => $this->nullableFloat($rule->fixed_budget_amount ?? $rule->base_amount),
            'base_rate_percent' => $this->nullableFloat($rule->base_rate_percent ?? $rule->reward_percentage) ?? 0.0,
            'reward_percentage' => $this->nullableFloat($rule->reward_percentage ?? $rule->base_rate_percent),
            'budget_source_type' => trim((string)($rule->budget_source_type ?? 'FIXED')) ?: 'FIXED',
            'standard_tier_rate_percent' => $this->nullableFloat($rule->standard_tier_rate_percent),
            'collection_modifier_percent' => $this->nullableFloat($rule->collection_modifier_percent),
            'allow_collection_modifier_override' => !empty($rule->allow_collection_modifier_override) ? 1 : 0,
            'is_active' => $this->resolveIsActive($rule),
            'status' => $this->resolveStatus($rule),
            'created_by_user_id' => isset($rule->created_by_user_id) && (int)$rule->created_by_user_id > 0 ? (int)$rule->created_by_user_id : null,
            'updated_by_user_id' => isset($rule->updated_by_user_id) && (int)$rule->updated_by_user_id > 0 ? (int)$rule->updated_by_user_id : null,
            'deleted_by_user_id' => isset($rule->deleted_by_user_id) && (int)$rule->deleted_by_user_id > 0 ? (int)$rule->deleted_by_user_id : null,
            'deleted_at' => trim((string)($rule->deleted_at ?? '')) ?: null,
        ];

        $payload = [];
        foreach (self::WRITABLE_COLUMNS as $column) {
            if ($this->hasColumn($column) && array_key_exists($column, $values)) {
                $payload[$column] = $values[$column];
            }
        }
        return $payload;
    }

    private function inferFormulaType(CommissionRule $rule): string
    {
        return strtoupper((string)($rule->rule_type ?? '')) === 'FIXED_RULE' ? 'FIXED' : 'TIERED_MARGINAL';
    }

    private function jsonOrString($value): ?string
    {
        if (is_array($value)) {
            return json_encode(array_values($value), JSON_UNESCAPED_UNICODE);
        }
        $value = trim((string)$value);
        return $value === '' ? null : $value;
    }

    private function nullableFloat($value): ?float
    {
        return $value === null || $value === '' ? null : (float)$value;
    }

    private function resolveStatus(CommissionRule $rule): string
    {
        $status = strtolower(trim((string)($rule->status ?? '')));
        if ($status === '') {
            $status = isset($rule->is_active) && (int)$rule->is_active === 0 ? 'inactive' : 'active';
        }
        return in_array($status, ['active', 'inactive'], true) ? $status : 'active';
    }

    private function resolveIsActive(CommissionRule $rule): int
    {
        if (isset($rule->is_active)) {
            return (int)$rule->is_active ? 1 : 0;
        }
        return $this->resolveStatus($rule) === 'active' ? 1 : 0;
    }

    private function resolveSalespersonUserId(CommissionRule $rule): ?int
    {
        if (isset($rule->salesperson_user_id) && (int)$rule->salesperson_user_id > 0) {
            return (int)$rule->salesperson_user_id;
        }
        $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) {
                return (int)$target;
            }
        }
        return null;
    }

    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 isColumnNullable(string $column): bool
    {
        $cacheKey = 'nullable:' . $column;
        if (isset($this->columnCache[$cacheKey])) {
            return $this->columnCache[$cacheKey];
        }
        $stmt = $this->db->getPdo()->prepare("
            SELECT IS_NULLABLE
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'commission_rules'
              AND COLUMN_NAME = ?
            LIMIT 1
        ");
        $stmt->execute([$column]);
        $nullable = strtoupper((string)($stmt->fetchColumn() ?: 'YES')) === 'YES';
        $this->columnCache[$cacheKey] = $nullable;
        return $nullable;
    }

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