<?php
namespace App\Services;

use App\Database\Connection;
use App\Utils\Logger;
use Generator;
use PDO;
use RuntimeException;
use Throwable;

class ActivityTypeRepairService
{
    private Connection $db;

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

    public function preview(string $backupPath): array
    {
        return $this->run($backupPath, false);
    }

    public function apply(string $backupPath): array
    {
        return $this->run($backupPath, true);
    }

    private function run(string $backupPath, bool $apply): array
    {
        if (!is_file($backupPath)) {
            throw new RuntimeException('فایل بکاپ پیدا نشد: ' . $backupPath);
        }

        [$activityMap, $activityCompositeMap] = $this->buildBackupMaps($backupPath);
        $pdo = $this->db->getPdo();
        $lookupTitles = $this->loadLookupTitles($pdo);
        $rows = $this->loadBrokenRows($pdo);

        $matched = 0;
        $updated = 0;
        $preview = [];
        $sourceCounts = [];
        $updateStmt = $pdo->prepare("UPDATE activities SET activity_type_title = ?, last_sync = NOW() WHERE id = ?");

        foreach ($rows as $row) {
            $didarActivityId = trim((string)($row['didar_activity_id'] ?? ''));
            $newTitle = '';
            $matchSource = '';

            if ($didarActivityId !== '' && isset($activityMap[$didarActivityId])) {
                $newTitle = $activityMap[$didarActivityId];
                $matchSource = 'didar_activity_id';
            } else {
                $compositeKey = $this->buildCompositeActivityKey($row);
                if (isset($activityCompositeMap[$compositeKey])) {
                    $newTitle = $activityCompositeMap[$compositeKey];
                    $matchSource = 'composite';
                }
            }

            if ($newTitle === '') {
                $newTitle = $this->extractTitleFromRawJson((string)($row['raw_json'] ?? ''));
                if ($newTitle !== '') {
                    $matchSource = 'raw_json';
                }
            }

            if ($newTitle === '') {
                $lookupTitle = $lookupTitles[trim((string)($row['activity_type_id'] ?? ''))] ?? '';
                if ($lookupTitle !== '') {
                    $newTitle = $lookupTitle;
                    $matchSource = 'lookup';
                }
            }

            if ($newTitle === '') {
                $newTitle = $this->normalizeActivityTypeLabel((string)($row['stage'] ?? ''));
                if ($newTitle !== '') {
                    $matchSource = 'stage';
                }
            }

            if ($newTitle === '') {
                continue;
            }

            $matched++;
            $sourceCounts[$matchSource] = ($sourceCounts[$matchSource] ?? 0) + 1;

            if (count($preview) < 20) {
                $preview[] = [
                    'id' => (int)$row['id'],
                    'didar_activity_id' => $didarActivityId,
                    'old_title' => (string)($row['activity_type_title'] ?? ''),
                    'new_title' => $newTitle,
                    'match_source' => $matchSource,
                    'activity_type_id' => (string)($row['activity_type_id'] ?? ''),
                    'stage' => (string)($row['stage'] ?? ''),
                ];
            }

            if ($apply) {
                $updateStmt->execute([$newTitle, (int)$row['id']]);
                $updated++;
            }
        }

        $report = [
            'backup_path' => $backupPath,
            'backup_activity_type_rows' => count($activityMap),
            'current_broken_rows' => count($rows),
            'matched_rows' => $matched,
            'updated_rows' => $updated,
            'match_sources' => $sourceCounts,
            'mode' => $apply ? 'apply' : 'preview',
            'preview' => $preview,
        ];

        Logger::logInfo('Activity type repair executed', $report);
        return $report;
    }

    private function loadLookupTitles(PDO $pdo): array
    {
        $lookupTitles = [];
        try {
            $lookupStmt = $pdo->query("
                SELECT COALESCE(li.value, '') AS lookup_value, COALESCE(li.title, '') AS lookup_title
                FROM lookup_items li
                INNER JOIN lookup_groups lg ON lg.id = li.group_id
                WHERE lg.code = 'activity_types_fallback'
            ");
            foreach (($lookupStmt ? $lookupStmt->fetchAll(PDO::FETCH_ASSOC) : []) as $lookupRow) {
                $lookupValue = trim((string)($lookupRow['lookup_value'] ?? ''));
                if ($lookupValue !== '' && !isset($lookupTitles[$lookupValue])) {
                    $lookupTitles[$lookupValue] = $this->normalizeActivityTypeLabel((string)($lookupRow['lookup_title'] ?? ''));
                }
            }
        } catch (Throwable $e) {
            Logger::logWarning('Activity repair lookup preload failed', ['error' => $e->getMessage()]);
        }
        return $lookupTitles;
    }

    private function loadBrokenRows(PDO $pdo): array
    {
        $stmt = $pdo->query("
            SELECT
                id,
                didar_activity_id,
                activity_type_id,
                owner_didar_id,
                contact_didar_id,
                deal_didar_id,
                register_date,
                note,
                result_note,
                stage,
                raw_json,
                COALESCE(activity_type_title, '') AS activity_type_title
            FROM activities
            WHERE TRIM(COALESCE(activity_type_title, '')) = ''
               OR TRIM(COALESCE(activity_type_title, '')) = 'فعالیت'
               OR TRIM(COALESCE(activity_type_title, '')) = 'نامشخص'
        ");
        return $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
    }

    private function buildBackupMaps(string $backupPath): array
    {
        $activityMap = [];
        $activityCompositeMap = [];

        foreach ($this->extractInsertStatements($backupPath) as $statement) {
            $columns = $this->parseInsertColumns($statement);
            if (empty($columns)) {
                continue;
            }

            $valuesPos = stripos($statement, 'VALUES');
            if ($valuesPos === false) {
                continue;
            }
            $valuesSql = substr($statement, $valuesPos + 6);
            $valuesSql = rtrim(trim($valuesSql), ';');

            foreach ($this->iterateTuples($valuesSql) as $tupleSql) {
                $rawFields = $this->parseTupleFields($tupleSql);
                if (count($rawFields) !== count($columns)) {
                    continue;
                }
                $row = [];
                foreach ($columns as $index => $column) {
                    $row[$column] = $this->decodeSqlValue($rawFields[$index]);
                }
                $didarActivityId = trim((string)($row['didar_activity_id'] ?? ''));
                $activityTypeTitle = $this->normalizeActivityTitleFromBackup((string)($row['activity_type_title'] ?? ''));
                if ($activityTypeTitle === '') {
                    continue;
                }
                if ($didarActivityId !== '') {
                    $activityMap[$didarActivityId] = $activityTypeTitle;
                }
                $compositeKey = $this->buildCompositeActivityKey($row);
                if ($compositeKey !== '||||||' && !isset($activityCompositeMap[$compositeKey])) {
                    $activityCompositeMap[$compositeKey] = $activityTypeTitle;
                }
            }
        }

        return [$activityMap, $activityCompositeMap];
    }

    private function normalizeActivityTitleFromBackup(string $title): string
    {
        $title = trim($title);
        if ($title === '' || $title === 'فعالیت' || $title === 'نامشخص') {
            return '';
        }
        return $title;
    }

    private function normalizeTextForMatch($value): string
    {
        $value = html_entity_decode(strip_tags((string)$value), ENT_QUOTES | ENT_HTML5, 'UTF-8');
        $value = preg_replace('/\s+/u', ' ', trim($value));
        return function_exists('mb_substr') ? mb_substr($value, 0, 180) : substr($value, 0, 180);
    }

    private function textContains(string $haystack, string $needle): bool
    {
        if (function_exists('mb_stripos')) {
            return mb_stripos($haystack, $needle) !== false;
        }
        return stripos($haystack, $needle) !== false;
    }

    private function normalizeActivityTypeLabel(string $value): string
    {
        $value = trim($value);
        $code = strtolower(str_replace([' ', '-', '/'], '_', $value));
        if ($value === '' || $value === 'فعالیت' || $value === 'نامشخص') {
            return '';
        }
        if (in_array($code, ['contact_no_answer', 'no_answer', 'contact_not_answered'], true)) {
            return 'تماس عدم پاسخ';
        }
        if (in_array($code, ['contact_followup', 'followup', 'contact_new', 'new_contact'], true)) {
            return 'تماس پیگیری';
        }
        if (in_array($code, ['payment_link', 'send_payment_link'], true)) {
            return 'ارسال لینک پرداخت';
        }
        if (in_array($code, ['outstanding_followup', 'outstanding_balance_followup', 'payment_followup'], true)) {
            return 'پیگیری مانده حساب';
        }
        if (in_array($code, ['payment', 'paid', 'successful_payment'], true)) {
            return 'پرداخت';
        }
        if (in_array($code, ['course_delivered'], true)) {
            return 'برگزاری دوره';
        }
        if (in_array($code, ['without_purchase'], true)) {
            return 'ناموفق';
        }
        if ($this->textContains($value, 'عدم پاسخ') || $this->textContains($value, 'بی پاسخ')) {
            return 'تماس عدم پاسخ';
        }
        if ($this->textContains($value, 'پیگیری مانده')) {
            return 'پیگیری مانده حساب';
        }
        if ($this->textContains($value, 'ارسال لینک') || $this->textContains($value, 'شبا')) {
            return 'ارسال لینک پرداخت';
        }
        if ($this->textContains($value, 'برگزاری') || $this->textContains($value, 'دوره')) {
            return 'برگزاری دوره';
        }
        if ($this->textContains($value, 'پشتیبانی')) {
            return 'پشتیبانی';
        }
        if ($this->textContains($value, 'ارجاع') || $this->textContains($value, 'crm')) {
            return 'ارجاع به CRM';
        }
        if ($this->textContains($value, 'ناموفق')) {
            return 'ناموفق';
        }
        if ($this->textContains($value, 'پرداخت') || $this->textContains($value, 'واریز') || $this->textContains($value, 'موفق')) {
            return 'پرداخت';
        }
        if ($this->textContains($value, 'پیگیری') || $this->textContains($value, 'تماس') || $this->textContains($value, 'خروجی') || $this->textContains($value, 'جدید')) {
            return 'تماس پیگیری';
        }
        return $value;
    }

    private function extractTitleFromRawJson($rawJson): string
    {
        $rawJson = trim((string)$rawJson);
        if ($rawJson === '') {
            return '';
        }
        $decoded = json_decode($rawJson, true);
        if (!is_array($decoded)) {
            return '';
        }

        $candidates = [
            (string)($decoded['ActivityTypeTitle'] ?? ''),
            (string)($decoded['title'] ?? ''),
            (string)($decoded['title_alt'] ?? ''),
            (string)($decoded['ActivityStageTitle'] ?? ''),
            (string)($decoded['stage'] ?? ''),
        ];
        if (isset($decoded['Activity']) && is_array($decoded['Activity'])) {
            $candidates[] = (string)($decoded['Activity']['ActivityTypeTitle'] ?? '');
            $candidates[] = (string)($decoded['Activity']['ActivityStageTitle'] ?? '');
            $candidates[] = (string)($decoded['Activity']['Stage'] ?? '');
        }

        foreach ($candidates as $candidate) {
            $normalized = $this->normalizeActivityTypeLabel($candidate);
            if ($normalized !== '') {
                return $normalized;
            }
        }
        return '';
    }

    private function normalizeDateMinute($value): string
    {
        $value = trim((string)$value);
        if ($value === '') {
            return '';
        }
        $timestamp = strtotime($value);
        if ($timestamp === false) {
            return '';
        }
        return date('Y-m-d H:i', $timestamp);
    }

    private function buildCompositeActivityKey(array $row): string
    {
        return implode('|', [
            trim((string)($row['owner_didar_id'] ?? '')),
            trim((string)($row['contact_didar_id'] ?? '')),
            trim((string)($row['deal_didar_id'] ?? '')),
            $this->normalizeDateMinute($row['register_date'] ?? ''),
            $this->normalizeTextForMatch($row['note'] ?? ''),
            $this->normalizeTextForMatch($row['result_note'] ?? ''),
            trim((string)($row['stage'] ?? '')),
        ]);
    }

    private function extractInsertStatements(string $path): Generator
    {
        $handle = fopen($path, 'rb');
        if (!$handle) {
            throw new RuntimeException('Unable to open backup file');
        }

        $buffer = '';
        $collecting = false;
        while (($line = fgets($handle)) !== false) {
            if (!$collecting && strpos($line, 'INSERT INTO `activities`') === false) {
                continue;
            }
            $collecting = true;
            $buffer .= $line;
            if (substr(rtrim($line), -1) === ';') {
                yield $buffer;
                $buffer = '';
                $collecting = false;
            }
        }
        fclose($handle);
    }

    private function parseInsertColumns(string $statement): array
    {
        if (!preg_match('/INSERT INTO `activities`\s*\((.*?)\)\s*VALUES\s*/su', $statement, $matches)) {
            return [];
        }
        return array_map(static function ($column) {
            return trim($column, " \t\n\r\0\x0B`");
        }, explode(',', $matches[1]));
    }

    private function iterateTuples(string $valuesSql): Generator
    {
        $length = strlen($valuesSql);
        $inString = false;
        $escape = false;
        $depth = 0;
        $current = '';
        for ($i = 0; $i < $length; $i++) {
            $ch = $valuesSql[$i];
            if ($inString) {
                $current .= $ch;
                if ($escape) {
                    $escape = false;
                } elseif ($ch === '\\') {
                    $escape = true;
                } elseif ($ch === "'") {
                    $inString = false;
                }
                continue;
            }

            if ($ch === "'") {
                $inString = true;
                $current .= $ch;
                continue;
            }

            if ($ch === '(') {
                if ($depth > 0) {
                    $current .= $ch;
                }
                $depth++;
                continue;
            }

            if ($ch === ')') {
                $depth--;
                if ($depth === 0) {
                    yield $current;
                    $current = '';
                    continue;
                }
                $current .= $ch;
                continue;
            }

            if ($depth > 0) {
                $current .= $ch;
            }
        }
    }

    private function parseTupleFields(string $tupleSql): array
    {
        $fields = [];
        $current = '';
        $length = strlen($tupleSql);
        $inString = false;
        $escape = false;

        for ($i = 0; $i < $length; $i++) {
            $ch = $tupleSql[$i];
            if ($inString) {
                $current .= $ch;
                if ($escape) {
                    $escape = false;
                } elseif ($ch === '\\') {
                    $escape = true;
                } elseif ($ch === "'") {
                    $inString = false;
                }
                continue;
            }

            if ($ch === "'") {
                $inString = true;
                $current .= $ch;
                continue;
            }

            if ($ch === ',') {
                $fields[] = trim($current);
                $current = '';
                continue;
            }

            $current .= $ch;
        }
        $fields[] = trim($current);
        return $fields;
    }

    private function decodeSqlValue(string $value)
    {
        if (strcasecmp($value, 'NULL') === 0) {
            return null;
        }
        if ($value === "''") {
            return '';
        }
        if (strlen($value) >= 2 && $value[0] === "'" && substr($value, -1) === "'") {
            $value = substr($value, 1, -1);
            $value = str_replace(['\\\\', "\\'", "''"], ['\\', "'", "'"], $value);
        }
        return $value;
    }
}
