<?php
namespace App\Repositories;

use App\Database\Connection;
use PDO;

class ServiceGovernanceRepository
{
    private const RAW_MAPPING_CONFIG_KEY = 'service_raw_mappings_json';
    private const DECISION_VERSION = 'v1';

    private Connection $db;
    private ConfigRepository $configRepo;

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

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

    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'));

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

        return $this->getGovernanceSettings();
    }

    public function resolveVipAccessByContactId(string $contactDidarId): array
    {
        $contactDidarId = trim($contactDidarId);
        if ($contactDidarId === '') {
            return $this->emptyVipDecision();
        }

        $person = $this->findPersonByContactId($contactDidarId);
        if (!$person) {
            return $this->emptyVipDecision();
        }

        $strategy = $this->getGovernanceSettings()['vip_effective_deal_strategy'] ?? 'latest_vip_deal';
        $deals = $this->getDealsForContact($contactDidarId);
        $bestDecision = null;
        $bestFallback = null;

        foreach ($deals as $deal) {
            $decision = $this->evaluateDealVipDecision($deal, $person);
            if (($decision['service_family'] ?? null) === 'vip_channel') {
                if ($bestDecision === null || $this->isBetterDecision($decision, $bestDecision, $strategy)) {
                    $bestDecision = $decision;
                }
                continue;
            }

            if ($bestFallback === null || $this->isBetterDecision($decision, $bestFallback, $strategy)) {
                $bestFallback = $decision;
            }
        }

        $selected = $bestDecision ?? $bestFallback ?? $this->emptyVipDecision();
        $selected['lead_found'] = true;
        $selected['decision_version'] = self::DECISION_VERSION;
        return $selected;
    }

    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' => [
                    '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 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) ?: [];
    }

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

    private function findPersonByContactId(string $contactDidarId): ?array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            SELECT id, didar_contact_id, customer_code, first_name, last_name, mobile_phone, requested_services, customer_products
            FROM persons
            WHERE didar_contact_id = ?
            LIMIT 1
        ");
        $stmt->execute([$contactDidarId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    private function getDealsForContact(string $contactDidarId): array
    {
        $pdo = $this->db->getPdo();
        $stmt = $pdo->prepare("
            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,
                d.won_time,
                (
                    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
            WHERE d.contact_didar_id = ?
            ORDER BY d.register_time DESC, d.id DESC
        ");
        $stmt->execute([$contactDidarId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    private function evaluateDealVipDecision(array $deal, array $person): array
    {
        $matched = $this->resolveCanonicalServiceForDeal($deal, $person);
        $service = $matched['service'] ?? null;
        $serviceMeta = is_array($service) ? ($service['meta'] ?? []) : [];

        $payable = (float)($deal['payable_amount'] ?? 0);
        $confirmed = (float)($deal['confirmed_amount'] ?? 0);
        $fallbackPayment = (float)($deal['payment_amount'] ?? 0);
        $paidAmount = $confirmed > 0 ? $confirmed : $fallbackPayment;
        $requiresFullPayment = !empty($serviceMeta['requires_full_payment_for_access']);
        $outstandingBalance = max($payable - $paidAmount, 0);

        if (!$service) {
            return [
                'lead_found' => true,
                'decision_version' => self::DECISION_VERSION,
                'vip_family' => null,
                'service_family' => null,
                'support_mode' => null,
                'paid_status' => $payable > 0 ? ($outstandingBalance > 0 ? 'has_outstanding_balance' : 'paid_in_full') : 'not_applicable',
                'requires_full_payment_for_access' => false,
                'effective_product_code' => null,
                'effective_service_title' => null,
                'vip_eligible' => false,
                'outstanding_balance' => $outstandingBalance,
                'issue_code' => $matched['issue_code'] ?? 'service_not_canonical',
                'effective_deal' => $this->formatDealReference($deal),
            ];
        }

        $family = trim((string)($serviceMeta['service_family'] ?? ''));
        $paidStatus = 'not_applicable';
        if ($requiresFullPayment || $payable > 0 || $paidAmount > 0) {
            $paidStatus = $outstandingBalance > 0 ? 'has_outstanding_balance' : 'paid_in_full';
        }

        $vipEligible = $family === 'vip_channel'
            && (!$requiresFullPayment || $paidStatus === 'paid_in_full');

        return [
            'lead_found' => true,
            'decision_version' => self::DECISION_VERSION,
            'vip_family' => $family !== '' ? $family : null,
            'service_family' => $family !== '' ? $family : null,
            'support_mode' => ($serviceMeta['support_mode'] ?? '') !== '' ? (string)$serviceMeta['support_mode'] : null,
            'paid_status' => $paidStatus,
            'requires_full_payment_for_access' => $requiresFullPayment,
            'effective_product_code' => $this->resolveExternalAccessCode($service),
            'effective_service_title' => (string)($service['title'] ?? '') !== '' ? (string)$service['title'] : null,
            'vip_eligible' => $vipEligible,
            'outstanding_balance' => $outstandingBalance,
            'issue_code' => $matched['issue_code'] ?? null,
            'effective_deal' => $this->formatDealReference($deal),
        ];
    }

    private function resolveCanonicalServiceForDeal(array $deal, array $person): array
    {
        $services = $this->getServiceCatalog();
        $products = $this->getProductsWithServices();
        $serviceMapById = [];
        $serviceMapByToken = [];
        foreach ($services as $service) {
            $serviceId = (int)($service['id'] ?? 0);
            if ($serviceId > 0) {
                $serviceMapById[$serviceId] = $service;
            }
            foreach ([(string)($service['title'] ?? ''), (string)($service['value'] ?? ''), (string)($service['code'] ?? '')] as $token) {
                $normalizedToken = $this->normalizeText($token);
                if ($normalizedToken !== '') {
                    $serviceMapByToken[$normalizedToken] = $service;
                }
            }
        }

        $mappingIndex = $this->getRawMappings();
        $tokens = $this->extractDealTokens($deal);
        $personTokens = $this->extractTokens((string)($person['requested_services'] ?? ''));
        $productTokens = $this->extractTokens((string)($person['customer_products'] ?? ''));
        $tokens = array_values(array_unique(array_merge($tokens, $personTokens, $productTokens)));

        foreach ($tokens as $token) {
            $normalized = $this->normalizeText($token);
            if ($normalized === '') {
                continue;
            }

            if (!empty($mappingIndex[$normalized]['service_lookup_item_id'])) {
                $serviceId = (int)$mappingIndex[$normalized]['service_lookup_item_id'];
                if (isset($serviceMapById[$serviceId])) {
                    return ['service' => $serviceMapById[$serviceId], 'issue_code' => null];
                }
            }

            if (isset($serviceMapByToken[$normalized])) {
                return ['service' => $serviceMapByToken[$normalized], 'issue_code' => null];
            }
        }

        foreach ($products as $product) {
            $productServiceId = (int)($product['service_lookup_item_id'] ?? 0);
            if ($productServiceId <= 0 || !isset($serviceMapById[$productServiceId])) {
                continue;
            }
            $productName = $this->normalizeText((string)($product['name'] ?? ''));
            if ($productName === '') {
                continue;
            }

            foreach ($tokens as $token) {
                $normalized = $this->normalizeText($token);
                if ($normalized !== '' && (strpos($normalized, $productName) !== false || strpos($productName, $normalized) !== false)) {
                    return ['service' => $serviceMapById[$productServiceId], 'issue_code' => null];
                }
            }
        }

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

        return ['service' => null, 'issue_code' => $hasVipKeyword ? 'vip_keyword_without_mapping' : 'service_not_canonical'];
    }

    private function resolveExternalAccessCode(array $service): ?string
    {
        $meta = is_array($service['meta'] ?? null) ? $service['meta'] : [];
        $code = trim((string)($meta['external_access_code'] ?? ''));
        if ($code !== '') {
            return $code;
        }

        $lookupCode = trim((string)($service['code'] ?? ''));
        if ($lookupCode !== '') {
            return $lookupCode;
        }

        $value = trim((string)($service['value'] ?? ''));
        if ($value !== '') {
            return $this->normalizeIdentifier($value);
        }

        $title = trim((string)($service['title'] ?? ''));
        return $title !== '' ? $this->normalizeIdentifier($title) : null;
    }

    private function normalizeIdentifier(string $value): string
    {
        $value = $this->normalizeText($value);
        $value = preg_replace('/[^a-z0-9_]+/u', '_', $value);
        $value = trim((string)$value, '_');
        return $value !== '' ? $value : 'service';
    }

    private function formatDealReference(array $deal): ?array
    {
        $dealId = (int)($deal['id'] ?? 0);
        $didarDealId = trim((string)($deal['didar_deal_id'] ?? ''));
        if ($dealId <= 0 && $didarDealId === '') {
            return null;
        }

        return [
            'id' => $dealId > 0 ? $dealId : null,
            'didar_deal_id' => $didarDealId !== '' ? $didarDealId : null,
            'title' => trim((string)($deal['title'] ?? '')) !== '' ? trim((string)($deal['title'] ?? '')) : null,
            'status' => trim((string)($deal['status'] ?? '')) !== '' ? trim((string)($deal['status'] ?? '')) : null,
            'pipeline_stage' => trim((string)($deal['pipeline_stage'] ?? '')) !== '' ? trim((string)($deal['pipeline_stage'] ?? '')) : null,
            'register_time' => trim((string)($deal['register_time'] ?? '')) !== '' ? trim((string)($deal['register_time'] ?? '')) : null,
            'won_time' => trim((string)($deal['won_time'] ?? '')) !== '' ? trim((string)($deal['won_time'] ?? '')) : null,
        ];
    }

    private function emptyVipDecision(): array
    {
        return [
            'lead_found' => false,
            'decision_version' => self::DECISION_VERSION,
            'vip_family' => null,
            'service_family' => null,
            'support_mode' => null,
            'paid_status' => 'not_applicable',
            'requires_full_payment_for_access' => false,
            'effective_product_code' => null,
            'effective_service_title' => null,
            'vip_eligible' => false,
            'outstanding_balance' => 0.0,
            'issue_code' => 'lead_not_found',
            'effective_deal' => null,
        ];
    }

    private function isBetterDecision(array $candidate, array $current, string $strategy): bool
    {
        $candidateIsVip = ($candidate['service_family'] ?? null) === 'vip_channel';
        $currentIsVip = ($current['service_family'] ?? null) === 'vip_channel';
        if ($candidateIsVip !== $currentIsVip) {
            return $candidateIsVip;
        }

        if ($strategy === 'latest_paid_vip_deal') {
            $candidatePaid = ($candidate['paid_status'] ?? null) === 'paid_in_full';
            $currentPaid = ($current['paid_status'] ?? null) === 'paid_in_full';
            if ($candidatePaid !== $currentPaid) {
                return $candidatePaid;
            }
        }

        $candidateTime = (string)(($candidate['effective_deal']['register_time'] ?? '') ?: '');
        $currentTime = (string)(($current['effective_deal']['register_time'] ?? '') ?: '');
        if ($candidateTime !== $currentTime) {
            return $candidateTime > $currentTime;
        }

        $candidateId = (int)(($candidate['effective_deal']['id'] ?? 0) ?: 0);
        $currentId = (int)(($current['effective_deal']['id'] ?? 0) ?: 0);
        return $candidateId > $currentId;
    }

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