<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class ServiceGovernanceRepository
{
    private const RAW_MAPPING_CONFIG_KEY = 'service_raw_mappings_json';

    private Connection $db;
    private ConfigRepository $configRepo;

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

    public function getServiceCatalog(): array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT li.id, li.group_id, li.code, li.title, li.value, li.meta_json, li.is_active, li.sort_order,
                   COALESCE(COUNT(p.id), 0) AS linked_product_count
            FROM lookup_items li
            INNER JOIN lookup_groups lg ON lg.id = li.group_id
            LEFT JOIN products p ON p.service_lookup_item_id = li.id
            WHERE lg.code = 'allowed_services'
            GROUP BY li.id, li.group_id, li.code, li.title, li.value, li.meta_json, li.is_active, li.sort_order
            ORDER BY li.sort_order ASC, li.title ASC
        ");
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        return array_map(function (array $row): array {
            $meta = $this->decodeMetaJson($row['meta_json'] ?? null);
            return [
                'id' => (int)($row['id'] ?? 0),
                'group_id' => (int)($row['group_id'] ?? 0),
                'code' => (string)($row['code'] ?? ''),
                'title' => (string)($row['title'] ?? ''),
                'value' => (string)($row['value'] ?? ''),
                'is_active' => (int)($row['is_active'] ?? 1),
                'sort_order' => (int)($row['sort_order'] ?? 0),
                'linked_product_count' => (int)($row['linked_product_count'] ?? 0),
                'meta_json' => $row['meta_json'] ?? null,
                'meta' => [
                    'service_family' => (string)($meta['service_family'] ?? ''),
                    'support_mode' => (string)($meta['support_mode'] ?? ''),
                    'requires_full_payment_for_access' => !empty($meta['requires_full_payment_for_access']),
                    'external_access_code' => (string)($meta['external_access_code'] ?? ''),
                ],
            ];
        }, $rows);
    }

    public function saveServiceCatalogItem(array $data): int
    {
        $serviceId = (int)($data['id'] ?? 0);
        if ($serviceId <= 0) {
            throw new \RuntimeException('شناسه خدمت نامعتبر است');
        }

        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT li.id, li.meta_json
            FROM lookup_items li
            INNER JOIN lookup_groups lg ON lg.id = li.group_id
            WHERE li.id = ? AND lg.code = 'allowed_services'
            LIMIT 1
        ");
        $stmt->execute([$serviceId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) {
            throw new \RuntimeException('خدمت انتخاب‌شده نامعتبر است');
        }

        $meta = $this->decodeMetaJson($row['meta_json'] ?? null);
        $meta['service_family'] = trim((string)($data['service_family'] ?? ''));
        $meta['support_mode'] = trim((string)($data['support_mode'] ?? ''));
        $meta['requires_full_payment_for_access'] = !empty($data['requires_full_payment_for_access']);
        $meta['external_access_code'] = trim((string)($data['external_access_code'] ?? ''));
        $metaJson = json_encode($meta, JSON_UNESCAPED_UNICODE);

        $upd = $pdo->prepare("UPDATE lookup_items SET meta_json = ?, updated_at = NOW() WHERE id = ?");
        $upd->execute([$metaJson, $serviceId]);

        return $serviceId;
    }

    public function getRawMappings(): array
    {
        $raw = (string)$this->configRepo->getValue(self::RAW_MAPPING_CONFIG_KEY, '{}');
        $decoded = json_decode($raw, true);
        return is_array($decoded) ? $decoded : [];
    }

    public function saveRawMapping(string $rawTitle, int $serviceLookupItemId, array $context = []): void
    {
        $rawTitle = trim($rawTitle);
        if ($rawTitle === '') {
            throw new \RuntimeException('عنوان خام الزامی است');
        }
        if ($serviceLookupItemId <= 0) {
            throw new \RuntimeException('خدمت استاندارد نامعتبر است');
        }

        $service = $this->findAllowedServiceById($serviceLookupItemId);
        if (!$service) {
            throw new \RuntimeException('خدمت استاندارد انتخاب‌شده یافت نشد');
        }

        $mappings = $this->getRawMappings();
        $key = $this->normalizeText($rawTitle);
        $mappings[$key] = [
            'raw_title' => $rawTitle,
            'service_lookup_item_id' => $serviceLookupItemId,
            'service_title' => (string)($service['title'] ?? ''),
            'updated_at' => date('Y-m-d H:i:s'),
            'updated_by' => (string)($context['updated_by'] ?? ''),
            'updated_by_user_id' => (int)($context['updated_by_user_id'] ?? 0),
        ];

        $this->configRepo->setValue(self::RAW_MAPPING_CONFIG_KEY, json_encode($mappings, JSON_UNESCAPED_UNICODE));
    }

    public function getGovernanceSettings(): array
    {
        return [
            'app_source_code' => trim((string)$this->configRepo->getValue('app_source_code', 'mobile_app')),
            'app_default_owner_didar_id' => trim((string)$this->configRepo->getValue('app_default_owner_didar_id', '')),
            'vip_effective_deal_strategy' => trim((string)$this->configRepo->getValue('vip_effective_deal_strategy', 'latest_vip_deal')),
            'vip_match_strategy' => trim((string)$this->configRepo->getValue('vip_match_strategy', 'mobile_phone')),
        ];
    }

    public function saveGovernanceSettings(array $settings): array
    {
        $appSourceCode = trim((string)($settings['app_source_code'] ?? 'mobile_app'));
        $ownerDidarId = trim((string)($settings['app_default_owner_didar_id'] ?? ''));
        $vipDealStrategy = trim((string)($settings['vip_effective_deal_strategy'] ?? 'latest_vip_deal'));
        $vipMatchStrategy = trim((string)($settings['vip_match_strategy'] ?? 'mobile_phone'));

        $this->configRepo->setValue('app_source_code', $appSourceCode !== '' ? $appSourceCode : 'mobile_app');
        $this->configRepo->setValue('app_default_owner_didar_id', $ownerDidarId);
        $this->configRepo->setValue('vip_effective_deal_strategy', $vipDealStrategy !== '' ? $vipDealStrategy : 'latest_vip_deal');
        $this->configRepo->setValue('vip_match_strategy', $vipMatchStrategy !== '' ? $vipMatchStrategy : 'mobile_phone');

        return $this->getGovernanceSettings();
    }

    public function getRawServiceCandidates(): array
    {
        $candidates = [];
        $this->accumulateCandidatesFromDeals($candidates);
        $this->accumulateCandidatesFromPersons($candidates);

        $mapped = $this->getRawMappings();
        $services = $this->getServiceCatalog();
        $serviceIndex = [];
        foreach ($services as $service) {
            $serviceIndex[(int)$service['id']] = $service;
        }

        $rows = array_values($candidates);
        usort($rows, static function (array $a, array $b): int {
            $countCompare = (int)($b['usage_count'] ?? 0) <=> (int)($a['usage_count'] ?? 0);
            if ($countCompare !== 0) {
                return $countCompare;
            }
            return strcmp((string)($a['raw_title'] ?? ''), (string)($b['raw_title'] ?? ''));
        });

        foreach ($rows as &$row) {
            $key = $this->normalizeText((string)$row['raw_title']);
            $mapping = $mapped[$key] ?? null;
            $serviceId = (int)($mapping['service_lookup_item_id'] ?? 0);
            $row['mapping'] = $mapping;
            $row['mapped_service_id'] = $serviceId > 0 ? $serviceId : null;
            $row['mapped_service_title'] = $serviceId > 0 && isset($serviceIndex[$serviceId])
                ? (string)($serviceIndex[$serviceId]['title'] ?? '')
                : (string)($mapping['service_title'] ?? '');
            $row['suggested_service_id'] = $row['mapped_service_id'] ?: $this->suggestServiceId((string)$row['raw_title'], $services);
            $row['sources'] = array_values(array_keys($row['sources']));
        }
        unset($row);

        return $rows;
    }

    public function getDataQualityReport(): array
    {
        $services = $this->getServiceCatalog();
        $products = $this->getProductsWithServices();
        $rawCandidates = $this->getRawServiceCandidates();

        $serviceFamilyMissing = array_values(array_filter($services, static function (array $service): bool {
            return trim((string)($service['meta']['service_family'] ?? '')) === '';
        }));
        $productsWithoutLinks = array_values(array_filter($products, static function (array $product): bool {
            return (int)($product['service_lookup_item_id'] ?? 0) <= 0;
        }));
        $unmappedCandidates = array_values(array_filter($rawCandidates, static function (array $candidate): bool {
            return empty($candidate['mapped_service_id']);
        }));

        $serviceIndex = [];
        foreach ($services as $service) {
            $serviceIndex[$this->normalizeText((string)($service['title'] ?? ''))] = $service;
            $value = trim((string)($service['value'] ?? ''));
            if ($value !== '') {
                $serviceIndex[$this->normalizeText($value)] = $service;
            }
        }
        $mappingIndex = $this->getRawMappings();

        $vipOutstanding = [];
        $vipEligible = [];
        $vipUnknown = [];

        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("
            SELECT d.id, d.didar_deal_id, d.contact_didar_id, d.title, d.status, d.pipeline_stage, d.payable_amount, d.payment_amount,
                   d.service_name, d.requested_services, d.register_time,
                   p.first_name, p.last_name, p.mobile_phone,
                   (
                       SELECT COALESCE(SUM(t.amount), 0)
                       FROM transactions t
                       WHERE t.deal_id = d.id AND t.status = 'confirmed'
                   ) AS confirmed_amount
            FROM deals d
            LEFT JOIN persons p ON p.didar_contact_id = d.contact_didar_id
            ORDER BY d.register_time DESC, d.id DESC
        ");
        $deals = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($deals as $deal) {
            $tokens = $this->extractDealTokens($deal);
            if (empty($tokens)) {
                continue;
            }

            $matchedService = null;
            foreach ($tokens as $token) {
                $normalized = $this->normalizeText($token);
                $mapping = $mappingIndex[$normalized] ?? null;
                if ($mapping && !empty($mapping['service_lookup_item_id'])) {
                    $serviceId = (int)$mapping['service_lookup_item_id'];
                    foreach ($services as $service) {
                        if ((int)$service['id'] === $serviceId) {
                            $matchedService = $service;
                            break 2;
                        }
                    }
                }
                if (isset($serviceIndex[$normalized])) {
                    $matchedService = $serviceIndex[$normalized];
                    break;
                }
            }

            $hasVipKeyword = false;
            foreach ($tokens as $token) {
                if ($this->containsVipKeyword($token)) {
                    $hasVipKeyword = true;
                    break;
                }
            }

            if (!$matchedService && !$hasVipKeyword) {
                continue;
            }

            if (!$matchedService) {
                $vipUnknown[] = $this->buildVipDealSample($deal, null, null);
                continue;
            }

            $family = trim((string)($matchedService['meta']['service_family'] ?? ''));
            if ($family !== 'vip_channel') {
                if ($hasVipKeyword) {
                    $vipUnknown[] = $this->buildVipDealSample($deal, $matchedService, 'service_family_not_vip');
                }
                continue;
            }

            $payable = (float)($deal['payable_amount'] ?? 0);
            $confirmed = (float)($deal['confirmed_amount'] ?? 0);
            $fallbackPayment = (float)($deal['payment_amount'] ?? 0);
            $paidAmount = $confirmed > 0 ? $confirmed : $fallbackPayment;
            $isPaidInFull = $payable <= 0 || $paidAmount >= $payable;

            if ($isPaidInFull) {
                $vipEligible[] = $this->buildVipDealSample($deal, $matchedService, null);
            } else {
                $sample = $this->buildVipDealSample($deal, $matchedService, null);
                $sample['outstanding_balance'] = max($payable - $paidAmount, 0);
                $vipOutstanding[] = $sample;
            }
        }

        return [
            'summary' => [
                'services_total' => count($services),
                'services_missing_family' => count($serviceFamilyMissing),
                'products_total' => count($products),
                'products_without_link' => count($productsWithoutLinks),
                'raw_candidates_total' => count($rawCandidates),
                'raw_candidates_unmapped' => count($unmappedCandidates),
                'vip_deals_outstanding' => count($vipOutstanding),
                'vip_deals_eligible' => count($vipEligible),
                'vip_deals_unknown' => count($vipUnknown),
            ],
            'services_missing_family' => array_slice($serviceFamilyMissing, 0, 50),
            'products_without_link' => array_slice($productsWithoutLinks, 0, 50),
            'raw_candidates_unmapped' => array_slice($unmappedCandidates, 0, 100),
            'vip_deals_outstanding' => array_slice($vipOutstanding, 0, 50),
            'vip_deals_eligible' => array_slice($vipEligible, 0, 50),
            'vip_deals_unknown' => array_slice($vipUnknown, 0, 50),
        ];
    }

    public function getProductsWithServices(): array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("
            SELECT p.id, p.name, p.service_lookup_item_id, li.title AS service_title, li.value AS service_value
            FROM products p
            LEFT JOIN lookup_items li ON li.id = p.service_lookup_item_id
            ORDER BY p.name ASC
        ");
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function findAllowedServiceById(int $serviceLookupItemId): ?array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT li.id, li.title, li.value
            FROM lookup_items li
            INNER JOIN lookup_groups lg ON lg.id = li.group_id
            WHERE li.id = ? AND lg.code = 'allowed_services'
            LIMIT 1
        ");
        $stmt->execute([$serviceLookupItemId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function accumulateCandidatesFromDeals(array &$candidates): void
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("SELECT service_name, requested_services, register_time FROM deals");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($rows as $row) {
            $tokens = $this->extractDealTokens($row);
            foreach ($tokens as $token) {
                $this->accumulateCandidate($candidates, $token, 'deals', (string)($row['register_time'] ?? ''));
            }
        }
    }

    private function accumulateCandidatesFromPersons(array &$candidates): void
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->query("SELECT requested_services, register_time FROM persons");
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($rows as $row) {
            $tokens = $this->extractTokens((string)($row['requested_services'] ?? ''));
            foreach ($tokens as $token) {
                $this->accumulateCandidate($candidates, $token, 'persons', (string)($row['register_time'] ?? ''));
            }
        }
    }

    private function accumulateCandidate(array &$candidates, string $token, string $source, string $lastSeen): void
    {
        $token = trim($token);
        if ($token === '') {
            return;
        }
        $key = $this->normalizeText($token);
        if (!isset($candidates[$key])) {
            $candidates[$key] = [
                'raw_title' => $token,
                'usage_count' => 0,
                'last_seen_at' => $lastSeen,
                'sources' => [],
            ];
        }
        $candidates[$key]['usage_count']++;
        $candidates[$key]['sources'][$source] = true;
        if ($lastSeen !== '' && ($candidates[$key]['last_seen_at'] === '' || $lastSeen > $candidates[$key]['last_seen_at'])) {
            $candidates[$key]['last_seen_at'] = $lastSeen;
        }
    }

    private function extractDealTokens(array $deal): array
    {
        $tokens = [];
        $serviceName = trim((string)($deal['service_name'] ?? ''));
        if ($serviceName !== '') {
            $tokens[] = $serviceName;
        }

        return array_values(array_unique(array_merge($tokens, $this->extractTokens((string)($deal['requested_services'] ?? '')))));
    }

    private function extractTokens(string $raw): array
    {
        $raw = trim($raw);
        if ($raw === '') {
            return [];
        }

        $decoded = json_decode($raw, true);
        if (is_array($decoded)) {
            $tokens = [];
            foreach ($decoded as $item) {
                if (is_string($item) || is_numeric($item)) {
                    $token = trim((string)$item);
                    if ($token !== '') {
                        $tokens[] = $token;
                    }
                }
            }
            if (!empty($tokens)) {
                return array_values(array_unique($tokens));
            }
        }

        $normalized = preg_replace('/[\r\n\t]+/u', ',', $raw);
        $normalized = preg_replace('/[\[\]"]+/u', '', (string)$normalized);
        $parts = preg_split('/[|،,;]+/u', (string)$normalized) ?: [];
        $tokens = [];
        foreach ($parts as $part) {
            $token = trim((string)$part);
            if ($token !== '') {
                $tokens[] = $token;
            }
        }
        return array_values(array_unique($tokens));
    }

    private function decodeMetaJson(?string $metaJson): array
    {
        if (!$metaJson) {
            return [];
        }
        $decoded = json_decode($metaJson, true);
        return is_array($decoded) ? $decoded : [];
    }

    private function normalizeText(string $value): string
    {
        $value = trim($value);
        if ($value === '') {
            return '';
        }

        $map = [
            'ي' => 'ی',
            'ك' => 'ک',
            'ة' => 'ه',
            'ۀ' => 'ه',
            'أ' => 'ا',
            'إ' => 'ا',
            'ؤ' => 'و',
        ];
        $value = strtr($value, $map);
        $value = preg_replace('/\s+/u', ' ', $value);
        $value = preg_replace('/[|،,;]+/u', ' ', (string)$value);
        return function_exists('mb_strtolower')
            ? mb_strtolower(trim((string)$value), 'UTF-8')
            : strtolower(trim((string)$value));
    }

    private function suggestServiceId(string $rawTitle, array $services): ?int
    {
        $normalizedRaw = $this->normalizeText($rawTitle);
        if ($normalizedRaw === '') {
            return null;
        }
        foreach ($services as $service) {
            $title = $this->normalizeText((string)($service['title'] ?? ''));
            $value = $this->normalizeText((string)($service['value'] ?? ''));
            if ($normalizedRaw === $title || $normalizedRaw === $value) {
                return (int)$service['id'];
            }
        }
        foreach ($services as $service) {
            $title = $this->normalizeText((string)($service['title'] ?? ''));
            $value = $this->normalizeText((string)($service['value'] ?? ''));
            if ($title !== '' && strpos($normalizedRaw, $title) !== false) {
                return (int)$service['id'];
            }
            if ($value !== '' && strpos($normalizedRaw, $value) !== false) {
                return (int)$service['id'];
            }
        }
        return null;
    }

    private function containsVipKeyword(string $value): bool
    {
        $normalized = $this->normalizeText($value);
        if ($normalized === '') {
            return false;
        }
        return strpos($normalized, 'vip') !== false || strpos($normalized, 'وی ای پی') !== false || strpos($normalized, 'کانال vip') !== false;
    }

    private function buildVipDealSample(array $deal, ?array $service, ?string $issueCode): array
    {
        $firstName = trim((string)($deal['first_name'] ?? ''));
        $lastName = trim((string)($deal['last_name'] ?? ''));
        $fullName = trim($firstName . ' ' . $lastName);

        return [
            'deal_id' => (int)($deal['id'] ?? 0),
            'didar_deal_id' => (string)($deal['didar_deal_id'] ?? ''),
            'contact_didar_id' => (string)($deal['contact_didar_id'] ?? ''),
            'contact_name' => $fullName,
            'mobile_phone' => (string)($deal['mobile_phone'] ?? ''),
            'title' => (string)($deal['title'] ?? ''),
            'pipeline_stage' => (string)($deal['pipeline_stage'] ?? ''),
            'status' => (string)($deal['status'] ?? ''),
            'register_time' => (string)($deal['register_time'] ?? ''),
            'matched_service_title' => $service ? (string)($service['title'] ?? '') : '',
            'service_family' => $service ? (string)($service['meta']['service_family'] ?? '') : '',
            'support_mode' => $service ? (string)($service['meta']['support_mode'] ?? '') : '',
            'issue_code' => $issueCode,
        ];
    }
}
