<?php

declare(strict_types=1);

namespace Sogr\Service;

use PDO;
use Sogr\Core\ApiException;
use Sogr\Core\Database;
use Throwable;

/**
 * Pouzdana DB_TO_SHEETS sinhronizacija.
 * MySQL je primarni izvor. Google Sheets/Drive su sekundarni i pozivaju se
 * isključivo preko zasebnog Apps Script bridge servisa sa privatnim ključem.
 */
final class Phase7CSyncService
{
    public const VERSION = '7C-A6-R2-R3-R7.1';
    public const MODE = 'MYSQL_PRIMARY_DB_TO_SHEETS_SECONDARY';

    private PDO $pdo;
    private string $privateRoot;

    public function __construct(?PDO $pdo = null, ?string $privateRoot = null)
    {
        $this->pdo = $pdo ?? Database::connection();
        if ($privateRoot !== null) {
            $this->privateRoot = rtrim($privateRoot, '/\\');
        } else {
            $documentRoot = isset($_SERVER['DOCUMENT_ROOT'])
                ? rtrim((string) $_SERVER['DOCUMENT_ROOT'], '/\\')
                : dirname(__DIR__, 3);
            $this->privateRoot = dirname($documentRoot) . '/sogr_private';
        }
    }

    /** @return array<string,mixed> */
    public function status(bool $callBridge = false): array
    {
        $counts = [];
        $stmt = $this->pdo->query(
            "SELECT queue_status,COUNT(*) AS c FROM sync_queue GROUP BY queue_status"
        );
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $counts[(string) $row['queue_status']] = (int) $row['c'];
        }
        foreach (['PENDING','PROCESSING','DONE','FAILED','CONFLICT'] as $status) {
            $counts[$status] ??= 0;
        }

        $config = $this->readConfig(false);
        $bridge = null;
        if ($callBridge && ($config['valid'] ?? false) === true) {
            try {
                $bridge = $this->bridgeRequest('bridgeHealth', []);
            } catch (Throwable $e) {
                $bridge = ['ok' => false, 'error' => $e->getMessage()];
            }
        }

        return [
            'version' => self::VERSION,
            'mode' => self::MODE,
            'config' => [
                'valid' => (bool) ($config['valid'] ?? false),
                'bridgeUrl' => (string) ($config['bridgeUrlMasked'] ?? ''),
                'configuredAtUtc' => $config['configuredAtUtc'] ?? null,
            ],
            'queue' => $counts,
            'bridge' => $bridge,
            'ready' => ($config['valid'] ?? false) === true
                && $counts['PROCESSING'] === 0
                && $counts['CONFLICT'] === 0,
        ];
    }

    /** @return array<string,mixed> */
    public function bridgeHealth(): array
    {
        return $this->bridgeRequest('bridgeHealth', []);
    }

    /** @return array<string,mixed> */
    public function preflightJob(array $job): array
    {
        return $this->bridgeRequest('preflightSync', ['job' => self::normalizeJobForBridge($job)]);
    }

    /**
     * Google Sheets legacy pay/payMultiple ugovor očekuje dd.MM.yyyy., dok je
     * autoritativni MySQL datum ISO YYYY-MM-DD. Normalizacija se radi samo na
     * transportnoj kopiji job-a; podatak u MySQL-u i payload_json ostaju ISO.
     *
     * @param array<string,mixed> $job
     * @return array<string,mixed>
     */
    public static function normalizeJobForBridge(array $job): array
    {
        $action = (string) (($job['businessAction'] ?? '') ?: ($job['action'] ?? ''));
        if (!in_array($action, ['pay', 'payMultiple'], true)) {
            return $job;
        }
        $payload = is_array($job['payload'] ?? null) ? $job['payload'] : [];
        $date = trim((string) ($payload['datum'] ?? ''));
        if (preg_match('/^(\d{4})-(\d{2})-(\d{2})$/', $date, $m) === 1) {
            $year = (int) $m[1];
            $month = (int) $m[2];
            $day = (int) $m[3];
            if (!checkdate($month, $day, $year)) {
                throw new ApiException('SYNC_DATE_INVALID', 'Datum sync posla nije ispravan.', 422);
            }
            $payload['datum'] = sprintf('%02d.%02d.%04d.', $day, $month, $year);
            $job['payload'] = $payload;
        }
        return $job;
    }

    /**
     * @return array<string,mixed>
     */
    public function processQueueId(int $queueId): array
    {
        if ($queueId <= 0) {
            throw new ApiException('SYNC_QUEUE_ID_INVALID', 'Sync queue ID nije ispravan.', 400);
        }

        $row = $this->claim($queueId);
        if (($row['alreadyDone'] ?? false) === true) {
            return [
                'ok' => true,
                'alreadyDone' => true,
                'queueId' => $queueId,
                'bridgeResult' => $row['bridgeResult'] ?? null,
            ];
        }

        $payload = $this->decodeObject($row['payload_json'] ?? null);
        $job = $payload['job'] ?? null;
        if (!is_array($job)) {
            $this->fail($queueId, 'Sync queue payload nema job objekat.');
            throw new ApiException('SYNC_JOB_MISSING', 'Sync queue zapis nema ispravan job.', 500);
        }

        try {
            $result = $this->bridgeRequest('syncJob', ['job' => self::normalizeJobForBridge($job)]);
            if (($result['ok'] ?? false) !== true) {
                throw new ApiException(
                    'SHEETS_SYNC_REJECTED',
                    (string) ($result['error'] ?? 'Google Sheets bridge nije prihvatio sinhronizaciju.'),
                    502,
                    ['bridgeResult' => $result]
                );
            }
            $this->complete($queueId, $row, $payload, $result);
            return [
                'ok' => true,
                'queueId' => $queueId,
                'status' => 'DONE',
                'bridgeResult' => $result,
            ];
        } catch (Throwable $e) {
            $this->fail($queueId, $e->getMessage());
            return [
                'ok' => false,
                'queueId' => $queueId,
                'status' => 'FAILED',
                'error' => $e->getMessage(),
            ];
        }
    }

    /** @return array<string,mixed> */
    public function processPending(int $limit = 10): array
    {
        $limit = max(1, min(50, $limit));
        $sql = "SELECT id FROM sync_queue
                WHERE direction='DB_TO_SHEETS'
                  AND queue_status IN ('PENDING','FAILED')
                ORDER BY priority,id LIMIT " . $limit;
        $ids = array_map('intval', $this->pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN));
        $results = [];
        foreach ($ids as $id) {
            $results[] = $this->processQueueId($id);
        }
        return [
            'ok' => !in_array(false, array_map(static fn(array $r): bool => ($r['ok'] ?? false) === true, $results), true),
            'processed' => count($results),
            'results' => $results,
        ];
    }

    /** @return array<string,mixed> */
    private function claim(int $queueId): array
    {
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare('SELECT * FROM sync_queue WHERE id=:id FOR UPDATE');
            $stmt->execute([':id' => $queueId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!is_array($row)) {
                throw new ApiException('SYNC_QUEUE_NOT_FOUND', 'Sync queue zapis nije pronađen.', 404);
            }
            $status = (string) $row['queue_status'];
            if ($status === 'DONE') {
                $payload = $this->decodeObject($row['payload_json'] ?? null);
                $this->pdo->commit();
                return [
                    'alreadyDone' => true,
                    'bridgeResult' => $payload['bridgeResult'] ?? null,
                ];
            }
            if ($status === 'CONFLICT') {
                throw new ApiException('SYNC_CONFLICT', 'Sync queue zapis je u konfliktu i zahteva ručnu proveru.', 409);
            }
            if ($status === 'PROCESSING' && !empty($row['locked_at'])) {
                $lockedAt = strtotime((string) $row['locked_at'] . ' UTC');
                if ($lockedAt !== false && $lockedAt > time() - 300) {
                    throw new ApiException('SYNC_ALREADY_PROCESSING', 'Sinhronizacija ovog zapisa je već u toku.', 409);
                }
            }
            $worker = 'web-' . substr(hash('sha256', gethostname() . '|' . getmypid()), 0, 20);
            $update = $this->pdo->prepare(
                "UPDATE sync_queue SET queue_status='PROCESSING',locked_at=UTC_TIMESTAMP(),locked_by=:worker,
                 attempts=attempts+1,last_error=NULL WHERE id=:id"
            );
            $update->execute([':worker' => $worker, ':id' => $queueId]);
            $this->pdo->commit();
            return $row;
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    /** @param array<string,mixed> $queueRow @param array<string,mixed> $payload @param array<string,mixed> $result */
    private function complete(int $queueId, array $queueRow, array $payload, array $result): void
    {
        $this->pdo->beginTransaction();
        try {
            $action = (string) (($payload['job']['businessAction'] ?? '') ?: ($payload['job']['action'] ?? ''));
            $this->applyBridgeMetadata($action, $queueRow, $payload, $result);
            $payload['bridgeResult'] = $result;
            $payload['syncedAtUtc'] = gmdate('c');
            $update = $this->pdo->prepare(
                "UPDATE sync_queue SET payload_json=:payload,queue_status='DONE',locked_at=NULL,locked_by=NULL,
                 last_error=NULL,completed_at=UTC_TIMESTAMP(),next_attempt_at=NULL WHERE id=:id"
            );
            $update->execute([
                ':payload' => self::json($payload),
                ':id' => $queueId,
            ]);
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    /** @param array<string,mixed> $queueRow @param array<string,mixed> $payload @param array<string,mixed> $result */
    private function applyBridgeMetadata(string $action, array $queueRow, array $payload, array $result): void
    {
        $entityId = isset($queueRow['entity_id']) ? (int) $queueRow['entity_id'] : 0;
        $jobPayload = is_array($payload['job']['payload'] ?? null) ? $payload['job']['payload'] : [];

        if (($action === 'pay' || $action === 'payMultiple') && $entityId > 0) {
            $receipt = is_array($result['priznanica'] ?? null) ? $result['priznanica'] : [];
            $receiptRow = (int) ($receipt['receiptRow'] ?? 0);
            $link = trim((string) ($receipt['link'] ?? ''));
            $stmt = $this->pdo->prepare(
                'UPDATE receipts SET receipt_sheet_row=:row,receipt_link=:link,row_version=row_version+1
                 WHERE payment_id=:payment_id'
            );
            $stmt->execute([
                ':row' => $receiptRow > 0 ? $receiptRow : null,
                ':link' => $link !== '' ? $link : null,
                ':payment_id' => $entityId,
            ]);
            if ($receiptRow > 0) {
                $receiptIdStmt = $this->pdo->prepare('SELECT id,receipt_sheet_year,row_version FROM receipts WHERE payment_id=:id');
                $receiptIdStmt->execute([':id' => $entityId]);
                $receiptDb = $receiptIdStmt->fetch(PDO::FETCH_ASSOC);
                if (is_array($receiptDb)) {
                    $this->upsertSheetLink(
                        (int) $receiptDb['receipt_sheet_year'],
                        'RECEIPTS',
                        'RECEIPT',
                        (int) $receiptDb['id'],
                        null,
                        $receiptRow,
                        (int) $receiptDb['row_version'],
                        $receipt
                    );
                }
            }
        }

        if ($action === 'saveReceiptPdf' && $entityId > 0) {
            $fileId = trim((string) ($result['fileId'] ?? ''));
            $url = trim((string) (($result['link'] ?? '') ?: ($result['url'] ?? '')));
            $sha = trim((string) ($jobPayload['pdfSha256'] ?? ''));
            $row = (int) ($result['receiptRow'] ?? 0);
            $stmt = $this->pdo->prepare(
                'UPDATE receipts SET pdf_drive_file_id=:file_id,pdf_url=:url,pdf_sha256=:sha,
                 receipt_sheet_row=COALESCE(NULLIF(:row,0),receipt_sheet_row),row_version=row_version+1 WHERE id=:id'
            );
            $stmt->execute([
                ':file_id' => $fileId !== '' ? $fileId : null,
                ':url' => $url !== '' ? $url : null,
                ':sha' => preg_match('/^[a-f0-9]{64}$/', $sha) === 1 ? $sha : null,
                ':row' => $row,
                ':id' => $entityId,
            ]);
        }

        if ($action === 'saveExpense' && $entityId > 0) {
            $expense = is_array($result['trosak'] ?? null) ? $result['trosak'] : [];
            $source = trim((string) ($expense['sourceCell'] ?? ''));
            if ($source !== '') {
                $stmt = $this->pdo->prepare(
                    'UPDATE expenses SET source_reference=:source,row_version=row_version+1 WHERE id=:id'
                );
                $stmt->execute([':source' => $source, ':id' => $entityId]);
            }
        }

        $entityIds = $payload['job']['dbEntityIds'] ?? [];
        if (is_array($entityIds)) {
            foreach ($entityIds as $type => $ids) {
                foreach ((array) $ids as $id) {
                    $this->touchExistingLinks((string) $type, (int) $id, $result);
                }
            }
        }
    }

    /** @param array<string,mixed> $payload */
    private function upsertSheetLink(
        int $year,
        string $role,
        string $entityType,
        int $entityId,
        ?string $entityUuid,
        int $sheetRow,
        int $dbVersion,
        array $payload
    ): void {
        $tab = $this->pdo->prepare(
            'SELECT gst.id FROM google_sheet_tabs gst
             JOIN google_sheet_documents gsd ON gsd.id=gst.document_id
             WHERE gsd.year=:year AND gsd.is_primary=1 AND gsd.is_active=1
               AND gst.tab_role=:role AND gst.is_active=1 LIMIT 1'
        );
        $tab->execute([':year' => $year, ':role' => $role]);
        $tabId = (int) $tab->fetchColumn();
        if ($tabId <= 0) {
            return;
        }
        $hash = hash('sha256', self::json($payload));
        $stmt = $this->pdo->prepare(
            "INSERT INTO sheet_row_links(sheet_tab_id,entity_type,entity_id,entity_uuid,sheet_row,db_version,
             sheet_version,last_synced_hash,last_source,last_synced_at)
             VALUES(:tab,:type,:entity,:uuid,:row,:db_version,1,:hash,'DB',UTC_TIMESTAMP())
             ON DUPLICATE KEY UPDATE sheet_row=VALUES(sheet_row),db_version=VALUES(db_version),
             sheet_version=sheet_version+1,last_synced_hash=VALUES(last_synced_hash),last_source='DB',
             last_synced_at=UTC_TIMESTAMP(),entity_uuid=COALESCE(VALUES(entity_uuid),entity_uuid)"
        );
        $stmt->execute([
            ':tab' => $tabId,
            ':type' => $entityType,
            ':entity' => $entityId,
            ':uuid' => $entityUuid,
            ':row' => $sheetRow,
            ':db_version' => max(1, $dbVersion),
            ':hash' => $hash,
        ]);
    }

    /** @param array<string,mixed> $payload */
    private function touchExistingLinks(string $entityType, int $entityId, array $payload): void
    {
        if ($entityId <= 0) {
            return;
        }
        $stmt = $this->pdo->prepare(
            "UPDATE sheet_row_links SET db_version=db_version+1,sheet_version=sheet_version+1,
             last_synced_hash=:hash,last_source='DB',last_synced_at=UTC_TIMESTAMP()
             WHERE entity_type=:type AND entity_id=:id"
        );
        $stmt->execute([
            ':hash' => hash('sha256', self::json($payload)),
            ':type' => $entityType,
            ':id' => $entityId,
        ]);
    }

    private function fail(int $queueId, string $message): void
    {
        $message = $this->safeSubstr($message, 0, 8000);
        $stmt = $this->pdo->prepare(
            "UPDATE sync_queue SET queue_status='FAILED',locked_at=NULL,locked_by=NULL,last_error=:error,
             next_attempt_at=UTC_TIMESTAMP()+INTERVAL 5 MINUTE WHERE id=:id"
        );
        $stmt->execute([':error' => $message, ':id' => $queueId]);
    }

    /** @return array<string,mixed> */
    private function bridgeRequest(string $action, array $extra): array
    {
        $config = $this->readConfig(true);
        $body = array_merge(['action' => $action, 'syncKey' => $config['bridgeKey']], $extra);
        $json = self::json($body);
        $response = $this->httpPost((string) $config['bridgeUrl'], $json);
        try {
            $decoded = json_decode($response['body'], true, 512, JSON_THROW_ON_ERROR);
        } catch (Throwable $e) {
            throw new ApiException(
                'SYNC_BRIDGE_INVALID_JSON',
                'Google Apps Script bridge nije vratio ispravan JSON.',
                502,
                ['httpStatus' => $response['status'], 'bodyPrefix' => $this->safeSubstr($response['body'], 0, 500)],
                $e
            );
        }
        if (!is_array($decoded)) {
            throw new ApiException('SYNC_BRIDGE_INVALID_RESPONSE', 'Bridge odgovor nije objekat.', 502);
        }
        if ($response['status'] < 200 || $response['status'] >= 300 || ($decoded['ok'] ?? false) !== true) {
            throw new ApiException(
                'SYNC_BRIDGE_ERROR',
                (string) ($decoded['error'] ?? ('Bridge HTTP greška ' . $response['status'] . '.')),
                502,
                ['httpStatus' => $response['status'], 'bridgeResponse' => $decoded]
            );
        }
        return $decoded;
    }

    /** @return array{status:int,body:string} */
    private function httpPost(string $url, string $body): array
    {
        if (function_exists('curl_init')) {
            $ch = curl_init($url);
            if ($ch === false) {
                throw new ApiException('CURL_INIT_FAILED', 'Nije moguće pokrenuti cURL.', 500);
            }
            curl_setopt_array($ch, [
                CURLOPT_POST => true,
                CURLOPT_POSTFIELDS => $body,
                CURLOPT_HTTPHEADER => ['Content-Type: text/plain;charset=utf-8', 'Accept: application/json'],
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_FOLLOWLOCATION => true,
                CURLOPT_MAXREDIRS => 5,
                CURLOPT_CONNECTTIMEOUT => 10,
                CURLOPT_TIMEOUT => 60,
                CURLOPT_SSL_VERIFYPEER => true,
                CURLOPT_SSL_VERIFYHOST => 2,
                CURLOPT_USERAGENT => 'SOGR-MySQL-Sync/' . self::VERSION,
            ]);
            $raw = curl_exec($ch);
            $status = (int) curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
            $error = curl_error($ch);
            curl_close($ch);
            if ($raw === false) {
                throw new ApiException('SYNC_BRIDGE_UNREACHABLE', 'Bridge nije dostupan: ' . $error, 502);
            }
            return ['status' => $status, 'body' => (string) $raw];
        }

        $context = stream_context_create([
            'http' => [
                'method' => 'POST',
                'header' => "Content-Type: text/plain;charset=utf-8\r\nAccept: application/json\r\n",
                'content' => $body,
                'timeout' => 60,
                'ignore_errors' => true,
            ],
        ]);
        $raw = @file_get_contents($url, false, $context);
        if ($raw === false) {
            throw new ApiException('SYNC_BRIDGE_UNREACHABLE', 'Bridge nije dostupan.', 502);
        }
        $status = 200;
        foreach (($http_response_header ?? []) as $header) {
            if (preg_match('/^HTTP\/\S+\s+(\d{3})/', $header, $match) === 1) {
                $status = (int) $match[1];
            }
        }
        return ['status' => $status, 'body' => $raw];
    }

    /** @return array<string,mixed> */
    private function readConfig(bool $required): array
    {
        $path = $this->privateRoot . '/phase7c-write-sync.json';
        if (!is_file($path)) {
            if ($required) {
                throw new ApiException('SYNC_CONFIG_MISSING', 'DB→Sheets bridge još nije konfigurisan.', 503);
            }
            return ['valid' => false];
        }
        try {
            $config = json_decode((string) file_get_contents($path), true, 32, JSON_THROW_ON_ERROR);
        } catch (Throwable $e) {
            throw new ApiException('SYNC_CONFIG_INVALID', 'Privatna sync konfiguracija nije ispravna.', 503, [], $e);
        }
        if (!is_array($config)) {
            throw new ApiException('SYNC_CONFIG_INVALID', 'Privatna sync konfiguracija nije objekat.', 503);
        }
        $url = trim((string) ($config['bridgeUrl'] ?? ''));
        $key = trim((string) ($config['bridgeKey'] ?? ''));
        $parts = parse_url($url);
        $host = strtolower((string) ($parts['host'] ?? ''));
        $validUrl = ($parts['scheme'] ?? '') === 'https'
            && in_array($host, ['script.google.com', 'script.googleusercontent.com'], true)
            && str_contains((string) ($parts['path'] ?? ''), '/macros/');
        $valid = $validUrl && strlen($key) >= 48;
        if (!$valid && $required) {
            throw new ApiException('SYNC_CONFIG_INVALID', 'Bridge URL ili privatni ključ nisu ispravni.', 503);
        }
        return [
            'valid' => $valid,
            'bridgeUrl' => $url,
            'bridgeKey' => $key,
            'bridgeUrlMasked' => $this->maskUrl($url),
            'configuredAtUtc' => $config['configuredAtUtc'] ?? null,
        ];
    }

    private function maskUrl(string $url): string
    {
        if (strlen($url) < 40) {
            return $url;
        }
        return substr($url, 0, 38) . '…' . substr($url, -12);
    }

    /** @return array<string,mixed> */
    private function decodeObject(mixed $value): array
    {
        if (is_array($value)) {
            return $value;
        }
        if (!is_string($value) || trim($value) === '') {
            return [];
        }
        try {
            $decoded = json_decode($value, true, 512, JSON_THROW_ON_ERROR);
            return is_array($decoded) ? $decoded : [];
        } catch (Throwable) {
            return [];
        }
    }

    /**
     * UTF-8 bezbedno skraćivanje bez obavezne mbstring ekstenzije.
     */
    private function safeSubstr(string $text, int $start, int $length): string
    {
        if (function_exists('mb_substr')) {
            return (string) mb_substr($text, $start, $length, 'UTF-8');
        }
        if (function_exists('iconv_substr')) {
            $value = iconv_substr($text, $start, $length, 'UTF-8');
            if ($value !== false) {
                return (string) $value;
            }
        }
        $characterCount = preg_match_all('/./us', $text, $characters);
        if ($start === 0 && $characterCount !== false) {
            return implode('', array_slice($characters[0], 0, $length));
        }
        return substr($text, $start, $length);
    }

    private static function json(mixed $value): string
    {
        return json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_THROW_ON_ERROR);
    }
}