<?php

declare(strict_types=1);

namespace PpPhp\Core\Update;

use PpPhp\Core\DB;
use PpPhp\Core\Version;

/**
 * The update authority's catalogue of published releases and the delta patches
 * between them. Lives only on the authority (the licence-server install) — a
 * client never has this class (see PlatformPackager::$serverOnlyFiles).
 *
 * `pp_update_releases` — one row per published version: the full bundle's
 * location + integrity + the complete signed file manifest (so a delta can be
 * validated against it).
 * `pp_update_deltas`   — one row per from→to patch: the changed-files-only
 * artifact that turns a ~26 MB update into tens of KB.
 *
 * Tables use the `pp_` prefix so they auto-classify as system, not project data
 * (see the system-tables registry). Schema self-heals — no migration step.
 */
final class UpdateReleases
{
    private static bool $ensured = false;

    public static function ensureSchema(): void
    {
        if (self::$ensured) return;
        DB::Init();
        DB::SetConnection();

        DB::Exec("CREATE TABLE IF NOT EXISTS `pp_update_releases` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `version` VARCHAR(32) NOT NULL,
            `channel` VARCHAR(16) NOT NULL DEFAULT 'stable',
            `codename` VARCHAR(64) NOT NULL DEFAULT '',
            `min_php` VARCHAR(16) NOT NULL DEFAULT '8.1.0',
            `full_name` VARCHAR(191) NOT NULL DEFAULT '',
            `full_url` TEXT NOT NULL,
            `full_sha256` CHAR(64) NOT NULL DEFAULT '',
            `full_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `manifest_json` LONGTEXT NULL,
            `notes` TEXT NULL,
            `published_at` DATETIME NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uniq_ver_chan` (`version`, `channel`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        DB::Exec("CREATE TABLE IF NOT EXISTS `pp_update_deltas` (
            `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
            `channel` VARCHAR(16) NOT NULL DEFAULT 'stable',
            `from_version` VARCHAR(32) NOT NULL,
            `to_version` VARCHAR(32) NOT NULL,
            `name` VARCHAR(191) NOT NULL DEFAULT '',
            `url` TEXT NOT NULL,
            `sha256` CHAR(64) NOT NULL DEFAULT '',
            `size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
            `created_at` DATETIME NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `uniq_from_to_chan` (`from_version`, `to_version`, `channel`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Self-healing columns for installs whose tables predate these fields.
        // edition = client|server; status = live (served OTA) | staged (uploaded,
        // recorded, not yet offered to clients). Existing rows default to live.
        self::ensureColumn('pp_update_releases', 'edition', "VARCHAR(16) NOT NULL DEFAULT 'client'");
        self::ensureColumn('pp_update_releases', 'status',  "VARCHAR(16) NOT NULL DEFAULT 'live'");
        self::ensureColumn('pp_update_deltas',   'edition', "VARCHAR(16) NOT NULL DEFAULT 'client'");

        // A release's identity is (version, channel, EDITION): a client and a
        // server build of the same version are different artifacts and must be
        // able to coexist. Migrate the unique keys to include edition (runs once).
        self::migrateUniqueKey('pp_update_releases', 'uniq_ver_chan', 'uniq_ver_chan_ed', '(`version`, `channel`, `edition`)');
        self::migrateUniqueKey('pp_update_deltas', 'uniq_from_to_chan', 'uniq_ftc_ed', '(`from_version`, `to_version`, `channel`, `edition`)');

        self::$ensured = true;
    }

    /** Add a column only if it is missing — safe to run on every request. */
    private static function ensureColumn(string $table, string $col, string $definition): void
    {
        $rows = DB::FetchAll("SHOW COLUMNS FROM `{$table}` LIKE ?", [$col]);
        if (!$rows) {
            DB::Exec("ALTER TABLE `{$table}` ADD COLUMN `{$col}` {$definition}");
        }
    }

    /** Replace an old unique key with a new one — idempotent (runs once). */
    private static function migrateUniqueKey(string $table, string $oldKey, string $newKey, string $cols): void
    {
        if (DB::FetchAll("SHOW INDEX FROM `{$table}` WHERE `Key_name` = ?", [$newKey])) return;
        if (DB::FetchAll("SHOW INDEX FROM `{$table}` WHERE `Key_name` = ?", [$oldKey])) {
            DB::Exec("ALTER TABLE `{$table}` DROP INDEX `{$oldKey}`");
        }
        DB::Exec("ALTER TABLE `{$table}` ADD UNIQUE KEY `{$newKey}` {$cols}");
    }

    /**
     * Record (or replace) a published full release.
     *
     * @param array{version:string,channel?:string,codename?:string,minPhp?:string,
     *   fullName?:string,fullUrl:string,fullSha256:string,fullSize:int,
     *   manifest?:array<string,string>,notes?:string} $r
     */
    public static function publish(array $r): bool
    {
        self::ensureSchema();
        $version = Version::normalise((string) ($r['version'] ?? ''));
        $channel = self::channel((string) ($r['channel'] ?? 'stable'));

        $row = [
            'version'      => $version,
            'channel'      => $channel,
            'codename'     => (string) ($r['codename'] ?? ''),
            'min_php'      => (string) ($r['minPhp'] ?? Version::MIN_PHP),
            'full_name'    => (string) ($r['fullName'] ?? ''),
            'full_url'     => (string) ($r['fullUrl'] ?? ''),
            'full_sha256'  => (string) ($r['fullSha256'] ?? ''),
            'full_size'    => (int) ($r['fullSize'] ?? 0),
            'manifest_json' => isset($r['manifest']) ? (string) json_encode($r['manifest'], JSON_UNESCAPED_SLASHES) : null,
            'notes'        => (string) ($r['notes'] ?? ''),
            'edition'      => self::edition((string) ($r['edition'] ?? 'client')),
            'status'       => self::status((string) ($r['status'] ?? 'live')),
            'published_at' => date('Y-m-d H:i:s'),
        ];

        $existing = self::release($version, $channel, $row['edition']);
        if ($existing !== null) {
            return DB::Update('pp_update_releases', $row, ['id' => (int) $existing['id']]) !== false;
        }
        return (bool) DB::Insert('pp_update_releases', $row);
    }

    /**
     * Record (or replace) a delta patch from one version to another.
     *
     * @param array{from:string,to:string,channel?:string,name?:string,url:string,sha256:string,size:int} $d
     */
    public static function addDelta(array $d): bool
    {
        self::ensureSchema();
        $channel = self::channel((string) ($d['channel'] ?? 'stable'));
        $from = Version::normalise((string) ($d['from'] ?? ''));
        $to   = Version::normalise((string) ($d['to'] ?? ''));

        $row = [
            'channel'      => $channel,
            'from_version' => $from,
            'to_version'   => $to,
            'name'         => (string) ($d['name'] ?? ''),
            'url'          => (string) ($d['url'] ?? ''),
            'sha256'       => (string) ($d['sha256'] ?? ''),
            'size'         => (int) ($d['size'] ?? 0),
            'edition'      => self::edition((string) ($d['edition'] ?? 'client')),
            'created_at'   => date('Y-m-d H:i:s'),
        ];

        $rows = DB::FetchAll(
            'SELECT id FROM `pp_update_deltas` WHERE `from_version` = ? AND `to_version` = ? AND `channel` = ? AND `edition` = ?',
            [$from, $to, $channel, $row['edition']]
        );
        if (!empty($rows[0]['id'])) {
            return DB::Update('pp_update_deltas', $row, ['id' => (int) $rows[0]['id']]) !== false;
        }
        return (bool) DB::Insert('pp_update_deltas', $row);
    }

    /**
     * The newest published release on a channel, or null. The feed passes
     * $onlyLive=true (staged releases are never served) and $edition='client'
     * (customers run client edition).
     */
    public static function latest(string $channel = 'stable', bool $onlyLive = false, ?string $edition = null): ?array
    {
        self::ensureSchema();
        $where = '`channel` = ?';
        $args  = [self::channel($channel)];
        if ($onlyLive)        { $where .= " AND `status` = 'live'"; }
        if ($edition !== null) { $where .= ' AND `edition` = ?'; $args[] = self::edition($edition); }
        $rows = DB::FetchAll("SELECT * FROM `pp_update_releases` WHERE {$where}", $args);
        if (!$rows) return null;
        usort($rows, static fn ($a, $b) => version_compare(
            Version::normalise((string) $b['version']),
            Version::normalise((string) $a['version'])
        ));
        return $rows[0];
    }

    /** Flip a release between 'live' (served OTA) and 'staged' (held back). */
    public static function setStatus(string $version, string $channel, string $status, string $edition = 'client'): bool
    {
        self::ensureSchema();
        $rel = self::release($version, $channel, $edition);
        if ($rel === null) return false;
        return DB::Update('pp_update_releases', ['status' => self::status($status)], ['id' => (int) $rel['id']]) !== false;
    }

    /** Delta rows that TARGET a version (of an edition), newest "from" first. */
    public static function deltasFor(string $version, string $channel = 'stable', string $edition = 'client'): array
    {
        self::ensureSchema();
        $rows = DB::FetchAll(
            'SELECT * FROM `pp_update_deltas` WHERE `to_version` = ? AND `channel` = ? AND `edition` = ?',
            [Version::normalise($version), self::channel($channel), self::edition($edition)]
        );
        usort($rows, static fn ($a, $b) => version_compare(
            Version::normalise((string) $b['from_version']),
            Version::normalise((string) $a['from_version'])
        ));
        return $rows;
    }

    /** Remove a release (of an edition) and every delta that targets it. */
    public static function deleteRelease(string $version, string $channel = 'stable', string $edition = 'client'): bool
    {
        self::ensureSchema();
        $v = Version::normalise($version);
        $c = self::channel($channel);
        $e = self::edition($edition);
        DB::Query('DELETE FROM `pp_update_deltas` WHERE `to_version` = ? AND `channel` = ? AND `edition` = ?', [$v, $c, $e]);
        DB::Query('DELETE FROM `pp_update_releases` WHERE `version` = ? AND `channel` = ? AND `edition` = ?', [$v, $c, $e]);
        return true;
    }

    /** A specific published release (of a given edition), or null. */
    public static function release(string $version, string $channel = 'stable', string $edition = 'client'): ?array
    {
        self::ensureSchema();
        $rows = DB::FetchAll(
            'SELECT * FROM `pp_update_releases` WHERE `version` = ? AND `channel` = ? AND `edition` = ?',
            [Version::normalise($version), self::channel($channel), self::edition($edition)]
        );
        return $rows[0] ?? null;
    }

    /** The delta from one version to another (of a given edition), or null. */
    public static function delta(string $from, string $to, string $channel = 'stable', string $edition = 'client'): ?array
    {
        self::ensureSchema();
        $rows = DB::FetchAll(
            'SELECT * FROM `pp_update_deltas` WHERE `from_version` = ? AND `to_version` = ? AND `channel` = ? AND `edition` = ?',
            [Version::normalise($from), Version::normalise($to), self::channel($channel), self::edition($edition)]
        );
        return $rows[0] ?? null;
    }

    /** The decoded file manifest ({relpath: sha256}) stored with a release. */
    public static function manifest(string $version, string $channel = 'stable', string $edition = 'client'): array
    {
        $rel = self::release($version, $channel, $edition);
        if (!$rel || empty($rel['manifest_json'])) return [];
        $m = json_decode((string) $rel['manifest_json'], true);
        return is_array($m) ? $m : [];
    }

    /** Every published release on a channel, newest first (for the console). */
    public static function all(string $channel = 'stable'): array
    {
        self::ensureSchema();
        $rows = DB::FetchAll('SELECT * FROM `pp_update_releases` WHERE `channel` = ?', [self::channel($channel)]);
        usort($rows, static fn ($a, $b) => version_compare(
            Version::normalise((string) $b['version']),
            Version::normalise((string) $a['version'])
        ));
        return $rows;
    }

    private static function channel(string $c): string
    {
        $c = strtolower(trim($c));
        return in_array($c, ['stable', 'beta'], true) ? $c : 'stable';
    }

    private static function status(string $s): string
    {
        return strtolower(trim($s)) === 'staged' ? 'staged' : 'live';
    }

    private static function edition(string $e): string
    {
        return strtolower(trim($e)) === 'server' ? 'server' : 'client';
    }
}
