<?php

declare(strict_types=1);

namespace PpPhp\Core\I18n;

use PpPhp\Core\DB;

/**
 * Live (database-backed) translation overlay for GENERATED apps — Phase 2 of the
 * i18n system. When a project enables "Live translation editing", the build ships
 * this class + two small tables so an administrator can change UI strings and
 * manage languages from inside the running app WITHOUT a rebuild:
 *
 *   • pp_translations — OVERRIDE layer only: (lang, tkey) => tvalue. Absent keys
 *     fall through to the baked file dictionary (app/lang.php) then the code
 *     fallback, so this table stays small and a rebuild never clobbers live edits.
 *   • pp_languages    — the runtime language list (code, name, flag, order,
 *     enabled). Seeded once from the baked languages, then owner-managed.
 *
 * EVERY method is best-effort and self-healing: a missing table, a read-only DB,
 * or no connection degrades silently to the baked layer — the app never fatals
 * because translation is a presentation concern, never a hard dependency.
 */
final class TranslationStore
{
    /** @var array<string,array<string,string>>|null per-request overlay cache [lang=>[key=>val]] */
    private static ?array $overlay = null;
    /** @var array<int,array<string,mixed>>|null per-request language cache */
    private static ?array $langs = null;
    private static bool $schemaReady = false;

    /**
     * Create the two tables if absent and seed the language list from the baked
     * languages the first time. Runs at most once per request; all failures are
     * swallowed (the app keeps working on the baked dictionary).
     *
     * @param array<int,array<string,mixed>> $bakedLanguages the app/lang.php list
     */
    public static function ensureSchema(array $bakedLanguages = []): void
    {
        if (self::$schemaReady) return;
        self::$schemaReady = true;
        try {
            DB::Exec(
                'CREATE TABLE IF NOT EXISTS pp_translations ('
                . 'lang VARCHAR(12) NOT NULL, '
                . 'tkey VARCHAR(191) NOT NULL, '
                . 'tvalue TEXT, '
                . 'updated_at VARCHAR(32) DEFAULT NULL, '
                . 'PRIMARY KEY (lang, tkey))'
            );
            DB::Exec(
                'CREATE TABLE IF NOT EXISTS pp_languages ('
                . 'code VARCHAR(12) NOT NULL, '
                . 'name VARCHAR(80) NOT NULL, '
                . 'flag VARCHAR(16) DEFAULT NULL, '
                . 'sort_order INT DEFAULT 0, '
                . 'is_enabled INT DEFAULT 1, '
                . 'PRIMARY KEY (code))'
            );
            self::seedLanguages($bakedLanguages);
        } catch (\Throwable $e) {
            // best-effort: fall back to the baked layer
        }
    }

    /** Seed pp_languages from the baked list the first time it is empty. */
    private static function seedLanguages(array $bakedLanguages): void
    {
        if ($bakedLanguages === []) return;
        try {
            $have = DB::FetchOne('SELECT COUNT(*) AS c FROM pp_languages');
            $count = is_array($have) ? (int) ($have['c'] ?? 0) : (int) $have;
            if ($count > 0) return;
        } catch (\Throwable $e) {
            return;
        }
        $i = 0;
        foreach ($bakedLanguages as $l) {
            if (!is_array($l) || empty($l['code'])) continue;
            try {
                DB::Insert('pp_languages', [
                    'code'       => (string) $l['code'],
                    'name'       => (string) ($l['name'] ?? $l['code']),
                    'flag'       => (string) ($l['flag'] ?? ''),
                    'sort_order' => $i++,
                    'is_enabled' => 1,
                ]);
            } catch (\Throwable $e) {
                // ignore individual seed failures
            }
        }
    }

    /**
     * Override map for the given language codes: [lang => [key => value]].
     * One indexed read per request, cached. Missing table → empty map.
     *
     * @param string[] $codes
     * @return array<string,array<string,string>>
     */
    public static function overlay(array $codes): array
    {
        if (self::$overlay !== null) return self::$overlay;
        $out = [];
        $codes = array_values(array_unique(array_filter(array_map('strval', $codes), static fn($c) => $c !== '')));
        if ($codes === []) { self::$overlay = $out; return $out; }
        try {
            $ph = implode(',', array_fill(0, count($codes), '?'));
            $rows = DB::FetchAll('SELECT lang, tkey, tvalue FROM pp_translations WHERE lang IN (' . $ph . ')', $codes);
            if (is_array($rows)) {
                foreach ($rows as $r) {
                    $lang = (string) ($r['lang'] ?? '');
                    $key  = (string) ($r['tkey'] ?? '');
                    if ($lang === '' || $key === '') continue;
                    $out[$lang][$key] = (string) ($r['tvalue'] ?? '');
                }
            }
        } catch (\Throwable $e) {
            // no table yet / read error → baked layer only
        }
        self::$overlay = $out;
        return $out;
    }

    /**
     * The runtime language list, or null when live languages are unavailable
     * (no table / empty) so the caller keeps the baked list.
     *
     * @return array<int,array<string,mixed>>|null
     */
    public static function languages(): ?array
    {
        if (self::$langs !== null) return self::$langs ?: null;
        try {
            $rows = DB::FetchAll('SELECT code, name, flag FROM pp_languages WHERE is_enabled = 1 ORDER BY sort_order, code');
            if (is_array($rows) && $rows !== []) {
                $out = [];
                foreach ($rows as $r) {
                    $code = (string) ($r['code'] ?? '');
                    if ($code === '') continue;
                    $out[] = [
                        'code' => $code,
                        'name' => (string) ($r['name'] ?? $code),
                        'flag' => (string) ($r['flag'] ?? ''),
                    ];
                }
                self::$langs = $out;
                return $out ?: null;
            }
        } catch (\Throwable $e) {
            // fall through
        }
        self::$langs = [];
        return null;
    }

    /** ALL languages (incl. disabled) for the admin editor. @return array<int,array<string,mixed>> */
    public static function allLanguages(): array
    {
        try {
            $rows = DB::FetchAll('SELECT code, name, flag, sort_order, is_enabled FROM pp_languages ORDER BY sort_order, code');
            return is_array($rows) ? $rows : [];
        } catch (\Throwable $e) {
            return [];
        }
    }

    /** Every override for one language, for the admin editor. @return array<string,string> */
    public static function overridesFor(string $lang): array
    {
        try {
            $rows = DB::FetchAll('SELECT tkey, tvalue FROM pp_translations WHERE lang = ?', [$lang]);
            $out = [];
            if (is_array($rows)) {
                foreach ($rows as $r) {
                    $out[(string) ($r['tkey'] ?? '')] = (string) ($r['tvalue'] ?? '');
                }
            }
            return $out;
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Upsert a single override. An empty value DELETES the override (revert to the
     * baked/default string). Portable upsert = exists-check then update|insert.
     */
    public static function set(string $lang, string $key, string $value): bool
    {
        $lang = trim($lang);
        $key  = trim($key);
        if ($lang === '' || $key === '') return false;
        try {
            if ($value === '') {
                DB::Delete('pp_translations', ['lang' => $lang, 'tkey' => $key]);
                self::$overlay = null;
                return true;
            }
            $exists = DB::FetchOne('SELECT tkey FROM pp_translations WHERE lang = ? AND tkey = ?', [$lang, $key]);
            $now = gmdate('c');
            if ($exists) {
                DB::Update('pp_translations', ['tvalue' => $value, 'updated_at' => $now], ['lang' => $lang, 'tkey' => $key]);
            } else {
                DB::Insert('pp_translations', ['lang' => $lang, 'tkey' => $key, 'tvalue' => $value, 'updated_at' => $now]);
            }
            self::$overlay = null;
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /** @param array<string,string> $pairs key=>value */
    public static function bulkSet(string $lang, array $pairs): int
    {
        $n = 0;
        foreach ($pairs as $k => $v) {
            if (self::set($lang, (string) $k, (string) $v)) $n++;
        }
        return $n;
    }

    /** Add or update a language row. */
    public static function saveLanguage(string $code, string $name, string $flag = '', ?int $sort = null, bool $enabled = true): bool
    {
        $code = strtolower(trim(preg_replace('/[^A-Za-z0-9_-]/', '', $code) ?? ''));
        $name = trim($name);
        if ($code === '' || $name === '') return false;
        try {
            $exists = DB::FetchOne('SELECT code FROM pp_languages WHERE code = ?', [$code]);
            $data = ['name' => $name, 'flag' => $flag, 'is_enabled' => $enabled ? 1 : 0];
            if ($sort !== null) $data['sort_order'] = $sort;
            if ($exists) {
                DB::Update('pp_languages', $data, ['code' => $code]);
            } else {
                if (!isset($data['sort_order'])) {
                    $max = DB::FetchOne('SELECT MAX(sort_order) AS m FROM pp_languages');
                    $data['sort_order'] = (is_array($max) ? (int) ($max['m'] ?? 0) : (int) $max) + 1;
                }
                $data['code'] = $code;
                DB::Insert('pp_languages', $data);
            }
            self::$langs = null;
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /** Remove a language and all its overrides (never removes the last one). */
    public static function removeLanguage(string $code): bool
    {
        $code = strtolower(trim($code));
        if ($code === '') return false;
        try {
            $cnt = DB::FetchOne('SELECT COUNT(*) AS c FROM pp_languages');
            $count = is_array($cnt) ? (int) ($cnt['c'] ?? 0) : (int) $cnt;
            if ($count <= 1) return false;   // keep at least one language
            DB::Delete('pp_languages', ['code' => $code]);
            DB::Delete('pp_translations', ['lang' => $code]);
            self::$langs = null;
            self::$overlay = null;
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /** Reorder / toggle in one call from the admin grid. @param array<int,array{code:string,sort:int,enabled:bool}> $rows */
    public static function reorder(array $rows): bool
    {
        try {
            foreach ($rows as $r) {
                $code = strtolower(trim((string) ($r['code'] ?? '')));
                if ($code === '') continue;
                DB::Update('pp_languages', [
                    'sort_order' => (int) ($r['sort'] ?? 0),
                    'is_enabled' => !empty($r['enabled']) ? 1 : 0,
                ], ['code' => $code]);
            }
            self::$langs = null;
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }
}
