<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class LookupRepository
{
    private Connection $db;
    private const PROTECTED_SYSTEM_GROUP_CODES = [
        'lead_sources',
        'lead_source_details',
        'content_topics',
        'pipeline_stages',
        'allowed_services',
        'failure_reasons',
        'customer_levels',
        'financial_levels',
        'asset_ranges',
        'income_ranges',
        'discount_types',
        'discount_occasions',
        'user_roles',
        'deal_statuses',
        'activity_stages',
        'reminder_hours',
        'reminder_minutes',
        'activity_types_fallback',
        'virtual_stages',
        'activity_directions',
        'payment_methods',
        'payment_destination_banks',
    ];

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

    public function getGroups(bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT id, code, title, description, is_active, sort_order, is_system
                FROM lookup_groups";
        if (!$includeInactive) {
            $sql .= " WHERE is_active = 1";
        }
        $sql .= " ORDER BY sort_order ASC, title ASC";
        return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    public function getGroupByCode(string $code): ?array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM lookup_groups WHERE code = ?");
        $stmt->execute([$code]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getGroupById(int $id): ?array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT * FROM lookup_groups WHERE id = ?");
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getItemById(int $id): ?array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare(
            "SELECT i.*, g.code AS group_code, g.title AS group_title
             FROM lookup_items i
             INNER JOIN lookup_groups g ON g.id = i.group_id
             WHERE i.id = ?"
        );
        $stmt->execute([$id]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function getItemsByGroupCode(string $groupCode, bool $includeInactive = false): array
    {
        $group = $this->getGroupByCode($groupCode);
        if (!$group) {
            return [];
        }
        return $this->getItemsByGroupId((int)$group['id'], $includeInactive);
    }

    public function getItemsByGroupId(int $groupId, bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT i.id, i.group_id, i.parent_id, i.code, i.title, i.value,
                       i.meta_json, i.is_active, COALESCE(i.is_visible, 1) AS is_visible, i.sort_order, i.is_system,
                       p.value AS parent_value, p.title AS parent_title
                FROM lookup_items i
                LEFT JOIN lookup_items p ON p.id = i.parent_id
                WHERE i.group_id = ?";
        if (!$includeInactive) {
            $sql .= " AND i.is_active = 1 AND COALESCE(i.is_visible, 1) = 1";
        }
        $sql .= " ORDER BY i.sort_order ASC, i.title ASC";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$groupId]);
        $items = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($items as &$item) {
            $item['usage_summary'] = $this->getItemUsageSummary((array)$item);
        }
        unset($item);
        return $items;
    }

    public function getGroupUsageSummary(int $groupId): array
    {
        $group = $this->getGroupById($groupId);
        if (!$group) {
            return ['total' => 0, 'details' => []];
        }

        $items = $this->getItemsByGroupId($groupId, true);
        $itemCount = count($items);
        $systemItemCount = 0;
        $knownUsageTotal = 0;
        foreach ($items as $item) {
            if ((int)($item['is_system'] ?? 0) === 1) {
                $systemItemCount++;
            }
            $knownUsageTotal += (int)($item['usage_summary']['total'] ?? 0);
        }

        $details = [];
        if ($itemCount > 0) {
            $details[] = ['label' => 'گزینه', 'count' => $itemCount];
        }
        if ($systemItemCount > 0) {
            $details[] = ['label' => 'گزینه سیستمی', 'count' => $systemItemCount];
        }
        if ($knownUsageTotal > 0) {
            $details[] = ['label' => 'استفاده شناخته‌شده', 'count' => $knownUsageTotal];
        }

        return [
            'total' => $itemCount + $knownUsageTotal,
            'details' => $details
        ];
    }

    public function getItemUsageSummary(array $item): array
    {
        $groupCode = (string)($item['group_code'] ?? '');
        if ($groupCode === '' && !empty($item['group_id'])) {
            $group = $this->getGroupById((int)$item['group_id']);
            $groupCode = (string)($group['code'] ?? '');
        }

        $candidates = $this->lookupValueCandidates($item);
        $details = [];

        switch ($groupCode) {
            case 'lead_sources':
                $this->appendUsageCount($details, 'لید', 'persons', 'acquaintance_source', $candidates);
                $this->appendUsageCount($details, 'معامله', 'deals', 'acquaintance_source', $candidates);
                break;
            case 'lead_source_details':
                $this->appendUsageCount($details, 'لید', 'persons', 'acquaintance_detail', $candidates);
                $this->appendUsageCount($details, 'معامله', 'deals', 'acquaintance_detail', $candidates);
                break;
            case 'content_topics':
                $this->appendUsageCount($details, 'لید', 'persons', 'content_topic', $candidates);
                $this->appendUsageCount($details, 'معامله', 'deals', 'content_topic', $candidates);
                break;
            case 'pipeline_stages':
                $this->appendUsageCount($details, 'معامله', 'deals', 'pipeline_stage', $candidates);
                break;
            case 'payment_methods':
                $this->appendUsageCount($details, 'پرداخت', 'transactions', 'method', $candidates);
                $this->appendUsageCount($details, 'پرداخت تاییدشده', 'payment_records', 'payment_method', $candidates);
                break;
            case 'payment_destination_banks':
                $this->appendUsageCount($details, 'پرداخت', 'transactions', 'destination_bank', $candidates);
                $this->appendUsageCount($details, 'پرداخت تاییدشده', 'payment_records', 'destination_bank', $candidates);
                break;
            case 'allowed_services':
                $this->appendExactUsageCount($details, 'محصول لینک‌شده', 'products', 'service_lookup_item_id', (string)($item['id'] ?? ''));
                $this->appendUsageCount($details, 'معامله', 'deals', 'title', $candidates, true);
                break;
        }

        $total = array_sum(array_map(static function ($row) {
            return (int)($row['count'] ?? 0);
        }, $details));

        return [
            'total' => $total,
            'details' => $details
        ];
    }

    public function getAllLookups(bool $includeInactive = false): array
    {
        $pdo = $this->db->getPdo();
        $sql = "SELECT g.id AS group_id, g.code AS group_code, g.title AS group_title,
                       g.is_active AS group_active, g.sort_order AS group_sort,
                       i.id AS item_id, i.parent_id, i.code AS item_code, i.title AS item_title,
                       i.value AS item_value, i.meta_json, i.is_active AS item_active,
                       COALESCE(i.is_visible, 1) AS item_visible,
                       i.sort_order AS item_sort, p.value AS parent_value, p.title AS parent_title
                FROM lookup_groups g
                LEFT JOIN lookup_items i ON i.group_id = g.id
                LEFT JOIN lookup_items p ON p.id = i.parent_id";
        if (!$includeInactive) {
            $sql .= " WHERE g.is_active = 1 AND ((i.is_active = 1 AND COALESCE(i.is_visible, 1) = 1) OR i.id IS NULL)";
        }
        $sql .= " ORDER BY g.sort_order ASC, g.title ASC, i.sort_order ASC, i.title ASC";
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC) ?: [];

        $result = [];
        foreach ($rows as $row) {
            $groupCode = $row['group_code'];
            if (!isset($result[$groupCode])) {
                $result[$groupCode] = [
                    'group' => [
                        'id' => (int)$row['group_id'],
                        'code' => $groupCode,
                        'title' => $row['group_title'],
                        'is_active' => (int)$row['group_active'],
                        'sort_order' => (int)$row['group_sort']
                    ],
                    'items' => []
                ];
            }
            if (!empty($row['item_id'])) {
                $result[$groupCode]['items'][] = [
                    'id' => (int)$row['item_id'],
                    'group_id' => (int)$row['group_id'],
                    'parent_id' => $row['parent_id'] ? (int)$row['parent_id'] : null,
                    'code' => $row['item_code'],
                    'title' => $row['item_title'],
                    'value' => $row['item_value'],
                    'meta_json' => $row['meta_json'],
                    'is_active' => (int)$row['item_active'],
                    'is_visible' => (int)$row['item_visible'],
                    'sort_order' => (int)$row['item_sort'],
                    'parent_value' => $row['parent_value'],
                    'parent_title' => $row['parent_title']
                ];
            }
        }
        return $result;
    }

    public function filterItemsForDidarUser(array $items, string $didarUserId): array
    {
        $didarUserId = trim($didarUserId);
        if ($didarUserId === '') {
            return $items;
        }

        return array_values(array_filter($items, function ($item) use ($didarUserId) {
            return !$this->isLookupItemDisabledForDidarUser((array)$item, $didarUserId);
        }));
    }

    public function isLookupItemDisabledForDidarUser(array $item, string $didarUserId): bool
    {
        $didarUserId = trim($didarUserId);
        if ($didarUserId === '') {
            return false;
        }

        $meta = $this->decodeLookupMeta($item['meta_json'] ?? null);
        $disabledIds = $meta['disabled_user_didar_ids'] ?? [];
        if (!is_array($disabledIds)) {
            return false;
        }

        foreach ($disabledIds as $candidate) {
            if (trim((string)$candidate) === $didarUserId) {
                return true;
            }
        }

        return false;
    }

    public function decodeLookupMeta($metaJson): array
    {
        if (!is_string($metaJson) || trim($metaJson) === '') {
            return [];
        }

        $decoded = json_decode($metaJson, true);
        return is_array($decoded) ? $decoded : [];
    }

    public function saveGroup(array $data): int
    {
        $pdo = $this->db->getPdo();
        $id = isset($data['id']) ? (int)$data['id'] : 0;
        $code = $data['code'] ?? '';
        $title = $data['title'] ?? '';
        $description = $data['description'] ?? null;
        $sortOrder = isset($data['sort_order']) ? (int)$data['sort_order'] : 0;
        $isActive = isset($data['is_active']) ? (int)$data['is_active'] : 1;

        if ($id > 0) {
            $existing = $this->getGroupById($id);
            if (!$existing) {
                throw new \RuntimeException('Lookup group not found');
            }
            $stmt = $pdo->prepare("UPDATE lookup_groups
                                   SET code = ?, title = ?, description = ?, sort_order = ?, is_active = ?, updated_at = NOW()
                                   WHERE id = ?");
            $stmt->execute([$code, $title, $description, $sortOrder, $isActive, $id]);
            return $id;
        }

        $stmt = $pdo->prepare("INSERT INTO lookup_groups (code, title, description, sort_order, is_active, is_system, created_at, updated_at)
                               VALUES (?, ?, ?, ?, ?, 0, NOW(), NOW())");
        $stmt->execute([$code, $title, $description, $sortOrder, $isActive]);
        return (int)$pdo->lastInsertId();
    }

    public function saveItem(array $data): int
    {
        $pdo = $this->db->getPdo();
        $id = isset($data['id']) ? (int)$data['id'] : 0;
        $groupId = isset($data['group_id']) ? (int)$data['group_id'] : 0;
        $parentId = isset($data['parent_id']) ? (int)$data['parent_id'] : null;
        $code = $data['code'] ?? null;
        $title = $data['title'] ?? '';
        $value = $data['value'] ?? '';
        $metaJson = $data['meta_json'] ?? null;
        $sortOrder = isset($data['sort_order']) ? (int)$data['sort_order'] : 0;
        $isActive = isset($data['is_active']) ? (int)$data['is_active'] : 1;
        $isVisible = isset($data['is_visible']) ? (int)$data['is_visible'] : 1;

        if ($value === '') {
            $value = $title;
        }

        if ($id > 0) {
            $existing = $this->getItemById($id);
            if (!$existing) {
                throw new \RuntimeException('Lookup item not found');
            }
            $stmt = $pdo->prepare("UPDATE lookup_items
                                   SET parent_id = ?, code = ?, title = ?, value = ?, meta_json = ?, sort_order = ?, is_active = ?, is_visible = ?, updated_at = NOW()
                                   WHERE id = ?");
            $stmt->execute([$parentId, $code, $title, $value, $metaJson, $sortOrder, $isActive, $isVisible, $id]);
            return $id;
        }

        $stmt = $pdo->prepare("INSERT INTO lookup_items (group_id, parent_id, code, title, value, meta_json, sort_order, is_active, is_visible, is_system, created_at, updated_at)
                               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW(), NOW())");
        $stmt->execute([$groupId, $parentId, $code, $title, $value, $metaJson, $sortOrder, $isActive, $isVisible]);
        return (int)$pdo->lastInsertId();
    }

    public function deleteGroup(int $id): void
    {
        $group = $this->getGroupById($id);
        if (!$group) {
            throw new \RuntimeException('Lookup group not found');
        }
        if ((int)($group['is_system'] ?? 0) === 1 || $this->isProtectedSystemGroupCode((string)($group['code'] ?? ''))) {
            throw new \RuntimeException('System lookup groups cannot be deleted. Disable or hide their items instead.');
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM lookup_groups WHERE id = ?");
        $stmt->execute([$id]);
    }

    public function deleteItem(int $id): void
    {
        $item = $this->getItemById($id);
        if (!$item) {
            throw new \RuntimeException('Lookup item not found');
        }
        if ((int)($item['is_system'] ?? 0) === 1) {
            throw new \RuntimeException('System lookup items cannot be deleted. Use inactive/hidden instead.');
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("DELETE FROM lookup_items WHERE id = ?");
        $stmt->execute([$id]);
    }

    private function lookupValueCandidates(array $item): array
    {
        $values = [
            $item['value'] ?? null,
            $item['code'] ?? null,
            $item['title'] ?? null,
        ];

        $out = [];
        foreach ($values as $value) {
            $value = trim((string)$value);
            if ($value !== '') {
                $out[$value] = true;
            }
        }
        return array_keys($out);
    }

    private function isProtectedSystemGroupCode(string $code): bool
    {
        return in_array($code, self::PROTECTED_SYSTEM_GROUP_CODES, true);
    }

    private function appendUsageCount(array &$details, string $label, string $table, string $column, array $candidates, bool $contains = false): void
    {
        if (empty($candidates) || !$this->db->hasColumn($table, $column)) {
            return;
        }

        $pdo = $this->db->getPdo();
        $conditions = [];
        $params = [];
        foreach ($candidates as $candidate) {
            if ($contains) {
                $conditions[] = "$column LIKE ?";
                $params[] = '%' . $candidate . '%';
            } else {
                $conditions[] = "$column = ?";
                $params[] = $candidate;
            }
        }

        $stmt = $pdo->prepare("SELECT COUNT(*) FROM $table WHERE " . implode(' OR ', $conditions));
        $stmt->execute($params);
        $count = (int)$stmt->fetchColumn();
        if ($count > 0) {
            $details[] = ['label' => $label, 'count' => $count];
        }
    }

    private function appendExactUsageCount(array &$details, string $label, string $table, string $column, string $value): void
    {
        if ($value === '' || !$this->db->hasColumn($table, $column)) {
            return;
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM $table WHERE $column = ?");
        $stmt->execute([$value]);
        $count = (int)$stmt->fetchColumn();
        if ($count > 0) {
            $details[] = ['label' => $label, 'count' => $count];
        }
    }
}
