<?php
namespace Aumframe;

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Template storage.
 *
 * A custom table rather than post meta: both the scene JSON and the baked output can be large, and
 * putting them in postmeta would slow every WP_Query's meta preloading.
 */
class Storage
{
    const TABLE = 'aumframe_templates';
    const TABLE_REV = 'aumframe_revisions';
    const TABLE_I18N = 'aumframe_template_i18n';
    const DB_VERSION = 4;

    /** How many history entries to keep per template. Too many bloats the table; too few is not enough
     *  to be useful. */
    const KEEP_REVISIONS = 30;

    public static function table(): string
    {
        global $wpdb;
        return $wpdb->prefix . self::TABLE;
    }

    public static function rev_table(): string
    {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_REV;
    }

    public static function i18n_table(): string
    {
        global $wpdb;
        return $wpdb->prefix . self::TABLE_I18N;
    }

    /**
     * Every table this plugin owns, deepest dependency first.
     *
     * The order is the safe delete order: a template's translations and revisions reference the
     * template row, so they go before it.
     *
     *  This exists so that nothing outside has to keep its own copy of the list. The dev seeder used
     * to hard-code the names, and when `aumframe_template_i18n` was added on 2026-09-05 that copy was
     * not updated — wiping a site then left the previous site's layout translations behind, pointing at
     * templates that no longer existed. Nothing errored. Anything that needs to know what this plugin
     * stores asks here, and a table added to this class is a table everyone already knows about.
     *
     * @return string[] Prefixed table names.
 */
    public static function tables(): array
    {
        global $wpdb;

        return [
            self::i18n_table(),
            self::rev_table(),
            self::table(),
            $wpdb->prefix . \Aumframe\Library::TABLE,
        ];
    }

    public static function install(): void
    {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $table   = self::table();
        $collate = $wpdb->get_charset_collate();

            // kind:  shell (site shell) | page | singular | archive | search | notfound
            // rules: routing rules as JSON, interpreted by Router
        dbDelta("CREATE TABLE {$table} (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            slug          VARCHAR(191)    NOT NULL DEFAULT '',
            title         VARCHAR(191)    NOT NULL DEFAULT '',
            kind          VARCHAR(32)     NOT NULL DEFAULT 'page',
            rules         LONGTEXT        NULL,
            priority      INT             NOT NULL DEFAULT 10,
            status        VARCHAR(20)     NOT NULL DEFAULT 'publish',
            scene         LONGTEXT        NULL,
            baked_html    LONGTEXT        NULL,
            baked_css     LONGTEXT        NULL,
            schema_version INT            NOT NULL DEFAULT 1,
            updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY slug (slug),
            KEY kind_status (kind, status)
        ) {$collate};");

        // Version history.
        // A custom table gets none of WordPress's revisions for free (Elementor stores in post meta and
        // inherits them), so this is built by hand. **Only the scene is stored, never the baked
        // output** — html and css can both be regenerated from the scene, and storing them would double
        // each entry's size for no benefit at all.
        $rev = self::rev_table();
        dbDelta("CREATE TABLE {$rev} (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            template_id   BIGINT UNSIGNED NOT NULL,
            scene         LONGTEXT        NULL,
            note          VARCHAR(191)    NOT NULL DEFAULT '',
            author_id     BIGINT UNSIGNED NOT NULL DEFAULT 0,
            created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY template_created (template_id, created_at)
        ) {$collate};");

        /*
         * Per-language variants of a template.
         *
         *  The whole translated scene and its baked output, not just the strings.
         *
         * Storing only the strings would mean rebuilding the page at render time — and the baked HTML
         * carries no marker around authored text, so the only way back would be substituting one
         * English string for another. The same English string legitimately appears in several places
         * meaning different things ("Open", "More"), so that substitution is wrong in a way nothing
         * reports. Whereas a stored variant renders through exactly the same code path as the source
         * language: no extra cost per request, and no second rendering behaviour to keep in step.
         *
         *  `source_hash` is not optional. It records what the source scene looked like when this was
         * translated, so editing the original can mark the translations stale. Without it a buyer edits
         * one sentence in English and the other languages keep the old one for ever, silently.
         *
         *  `source_strings` (path => sha1 of the English it was translated from) is what makes
         * *re-running* correct, and it is a separate question from `source_hash`. Reuse keyed on the path
         * alone looks right and is not: node ids survive an edit, so the old translation would be kept
         * for a sentence that has since changed — the report would say "stale", the re-run would
         * translate nothing, and the page would stay wrong. Measured while building this, 2026-09-05.
         *
         *  Rows are written by the browser, never by PHP: baking only exists in
         * `packages/core` (JS), and `Rest.php` refuses a save with no `baked_html`. See
         * `workflow/MULTILINGUAL-PLAN.md` §4.1.
 */
        $i18n = self::i18n_table();
        dbDelta("CREATE TABLE {$i18n} (
            id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            template_id   BIGINT UNSIGNED NOT NULL,
            lang          VARCHAR(20)     NOT NULL DEFAULT '',
            scene         LONGTEXT        NULL,
            baked_html    LONGTEXT        NULL,
            baked_css     LONGTEXT        NULL,
            source_hash   CHAR(40)        NOT NULL DEFAULT '',
            source_strings LONGTEXT       NULL,
            updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY template_lang (template_id, lang)
        ) {$collate};");

        update_option('aumframe_db_version', self::DB_VERSION);
    }

    /**
     * Create missing tables after a schema upgrade.
     *
     * Not by version number alone — it also confirms the table is really there.
     * Trusting the version alone means one failed install wedges permanently: the version has
     * already been written as current, install() never runs again, the missing table stays missing, and
     * the symptom is an inexplicable database error at runtime that nobody would trace back to install
     * logic.
     * (Reproducible during development by editing code into an opcache: old code runs, new version
     * number is written.)
 */
    public static function maybe_upgrade(): void
    {
        if ((int) get_option('aumframe_db_version') !== self::DB_VERSION || !self::tables_ok()) {
            self::install();
        }
    }

    public static function table_exists(string $table): bool
    {
        global $wpdb;
        return $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)) === $table;
    }

    private static function tables_ok(): bool
    {
        foreach ([self::table(), self::rev_table(), self::i18n_table()] as $t) {
            if (!self::table_exists($t)) {
                return false;
            }
        }
        return true;
    }

    public static function get(int $id): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM %i WHERE id = %d', self::table(), $id),
            ARRAY_A
        );
        return $row ?: null;
    }

    public static function get_by_slug(string $slug): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM %i WHERE slug = %s', self::table(), $slug),
            ARRAY_A
        );
        return $row ?: null;
    }

    /** @return array<int,array> published templates, ascending by priority (lower numbers match first) */
    /**
     * Swap a template row for the current language's variant, when there is one.
     *
     * The single point where a layout becomes multilingual. Every consumer reaches templates through
     * `Router::match()` / `Router::shell()`, so overlaying here covers the content template, the site
     * shell and the CSS at once — rather than three places that have to be kept in step.
     *
     *  Always falls back to the source language. Untranslated templates, half-translated sites and
     * newly added templates are all normal states, and a page that renders in the wrong language is
     * enormously better than one that does not render.
     *
     *  The engine does not know what "the current language" is — it asks. With nothing listening the
     * filter returns '' and this whole path costs one function call.
     *
     * @param array $row A template row.
     * @return array The row, possibly with scene/baked output replaced.
 */
    public static function localize(array $row): array
    {
        /**
         * Filters the language code layouts should be rendered in.
         *
         * Empty means the source language, which is also the answer on a site with one language.
         *
         * @param string $lang Language code.
 */
        $lang = (string) apply_filters('aumframe/current_language', '');

        if ($lang === '' || empty($row['id'])) {
            return $row;
        }

        global $wpdb;
        $variant = $wpdb->get_row(
            $wpdb->prepare(
                'SELECT scene, baked_html, baked_css FROM %i WHERE template_id = %d AND lang = %s LIMIT 1',
                self::i18n_table(),
                (int) $row['id'],
                $lang
            ),
            ARRAY_A
        );

        /*
         *  An empty `baked_html` is not a usable variant. A row can exist with the scene
         * translated and the bake not yet submitted (the browser does that in a second step), and
         * rendering it would blank the page — the one failure mode this whole design has to avoid.
 */
        if (!$variant || trim((string) ($variant['baked_html'] ?? '')) === '') {
            return $row;
        }

        $row['scene']      = $variant['scene'] ?? $row['scene'];
        $row['baked_html'] = $variant['baked_html'];
        $row['baked_css']  = $variant['baked_css'] ?? $row['baked_css'];

        return $row;
    }

    public static function published(?string $kind = null): array
    {
        global $wpdb;
        /*
         * The table name goes through `%i` (identifier placeholder) rather than string concatenation.
         * It was never injectable — it is `$wpdb->prefix` plus a class constant — but a concatenated
         * table name makes the whole statement unverifiable to a reader and to static analysis, so the
         * one real risk in here (the `kind` filter) could not be told apart from the safe part.
         * `%i` also means prepare() now always runs, where before it was skipped when $kind was null.
 */
        $sql  = "SELECT * FROM %i WHERE status = 'publish'";
        $args = [self::table()];
        if ($kind !== null) {
            $sql   .= ' AND kind = %s';
            $args[] = $kind;
        }
        $sql .= ' ORDER BY priority ASC, id ASC';

        // phpcs:ignore WordPress.DB.PreparedSQL -- $sql is assembled from the string literals directly above this line and nothing else; every value, including the table name, is passed as a prepare() argument.
        return $wpdb->get_results($wpdb->prepare($sql, $args), ARRAY_A) ?: [];
    }

    /**
     * Write a template. The baked output (html/css) is produced by core in the browser and submitted
     * alongside — PHP never compiles the node tree itself.
 */
    public static function save(array $data): int
    {
        global $wpdb;

        // How each column is sanitised. Only keys the caller actually supplied are processed —
        // this used to fill in defaults for missing keys, so a call meaning "update the scene only"
        // wiped title, slug and rules to empty, and with the routing rules gone the page stopped being
        // taken over. A partial update has to be partial.
        $clean = [
            'slug'           => fn($v) => sanitize_title((string) $v),
            'title'          => fn($v) => sanitize_text_field((string) $v),
            'kind'           => fn($v) => sanitize_key((string) $v),
            'rules'          => fn($v) => is_string($v) ? $v : wp_json_encode($v ?: []),
            'priority'       => fn($v) => (int) $v,
            'status'         => fn($v) => sanitize_key((string) $v),
            'scene'          => fn($v) => is_string($v) ? $v : wp_json_encode($v),
            'baked_html'     => fn($v) => (string) $v,
            'baked_css'      => fn($v) => (string) $v,
            'schema_version' => fn($v) => (int) $v,
        ];

        $row = [];
        foreach ($clean as $key => $fn) {
            if (array_key_exists($key, $data)) {
                $row[$key] = $fn($data[$key]);
            }
        }
        $row['updated_at'] = current_time('mysql');

        $existing = !empty($data['id'])
            ? self::get((int) $data['id'])
            : (!empty($row['slug']) ? self::get_by_slug($row['slug']) : null);

        // Defaults are only needed when creating; on update, whatever is absent keeps its stored value
        if (!$existing) {
            $row += [
                'slug' => '', 'title' => '', 'kind' => 'page', 'rules' => '[]',
                'priority' => 10, 'status' => 'publish', 'scene' => null,
                'baked_html' => '', 'baked_css' => '', 'schema_version' => 1,
            ];
        }

        if ($existing) {
            // Push the old state into history before overwriting — history is "a past you can
            // return to", while the current state always lives in the main table and needs no copy.
            //
            // Unchanged content is not recorded: saving as-is or renaming would otherwise push useful
            // history out.
            if (array_key_exists('scene', $row)
                && ($existing['scene'] ?? '') !== ''
                && $existing['scene'] !== $row['scene']
            ) {
                self::push_revision((int) $existing['id'], (string) $existing['scene']);
            }
            /*
             *  The return value must be checked.
             *
             * `$wpdb->update()` returns `false` on failure (a MySQL error, a scene too large for
             * `max_allowed_packet`, a dropped connection, a field overflow…).
             * This used to `return $existing['id']` directly, so a failure counted as success: REST
             * answered 200 → the editor showed "Saved" → not one byte changed in the database. The
             * user refreshed to find every change gone, with nothing reported anywhere along the way.
             *
             * This class of failure is inherently environmental: a permissive local MySQL and a
             * small scene will never hit it; on a buyer's host, one large page is enough.
             * (Traced here on 2026-08-30 from "reordered, saved, refreshed, and it reverted".)
 */
            $id = (int) $existing['id'];
            $ok = $wpdb->update(self::table(), $row, ['id' => $id]);
            if ($ok === false) {
                self::$lastError = $wpdb->last_error ?: 'UPDATE failed';
                return 0;
            }

            /*
             *  Read-back verification — do not trust the return value, verify the effect.
             *
             * `$wpdb->update()`'s return value cannot establish "it was stored":
             *   false = an error (caught above)
             *   0     = no row changed — which could mean the value genuinely did not change, or
             *           that the write was swallowed by something (a trigger, a proxy layer, a storage
             *           engine failing silently)
             *   n     = n rows changed
             * And `0` is indistinguishable from "nothing needed changing", so a return code alone can
             * never close the case.
             *
             * So the scene is read back and compared after writing. It is the only authoritative test on
             * the save path: the front end reads the baked output while the editor reads the scene, and
             * if either fails to land, what the user sees is "it saved and a refresh put it back" —
             * exactly the symptom that cost a full day online on 2026-08-30/31.
             *
             * The cost is one redundant SELECT. Saving is not a hot path, and that price for "a design
             * can never be silently lost" is worth paying.
 */
            if (array_key_exists('scene', $row)) {
                $back = $wpdb->get_var($wpdb->prepare(
                    'SELECT scene FROM %i WHERE id = %d',
                    self::table(),
                    $id
                ));
                if ((string) $back !== (string) $row['scene']) {
                    self::$lastError = sprintf(
                        'wrote %d bytes but read back %d (rows affected: %s)%s',
                        strlen((string) $row['scene']),
                        strlen((string) $back),
                        var_export($ok, true),
                        $wpdb->last_error ? ' — ' . $wpdb->last_error : ''
                    );
                    return 0;
                }
            }

            self::changed($id);
            return $id;
        }

        $ok = $wpdb->insert(self::table(), $row);
        if ($ok === false) {
            self::$lastError = $wpdb->last_error ?: 'INSERT failed';
            return 0;
        }
        $id = (int) $wpdb->insert_id;
        self::changed($id);
        return $id;
    }

    /**
     * A template was written, so what the front end serves has changed.
     *
     *  Without this, "I saved and the front end did not move" is the buyer's normal experience.
     * The theme purges page caches when a *setting* changes, but a design is saved through
     * `Rest.php` → here, which touches no option at all — so LiteSpeed or the host's edge keeps serving
     * the page it built before the save. Every author hits it, every time, on the most frequent action
     * there is. (Observed 2026-09-01 with a `[aumframe_search]` Shortcode node: correct in the editor,
     * absent from the front end, and the stored HTML had been right the whole time.)
     *
     * Fired rather than purged directly: cache policy belongs to the theme (it is the half that knows
     * which cache plugins exist and says so in an admin notice), and the plugin has no business
     * reaching into `AumCreate_Cache_Purge`. The theme listens.
     *
     * Only on success — a failed write leaves the cache correct, and purging it would throw away a
     * warm cache to no purpose.
 */
    private static function changed(int $id): void
    {
        if ($id > 0) {
            do_action('aumframe_template_saved', $id);
        }
    }

    /** Why the last database write failed, for the REST layer to report to the user. */
    private static $lastError = '';

    public static function last_error(): string
    {
        return self::$lastError;
    }

    /* Version history */

    public static function push_revision(int $template_id, string $scene, string $note = ''): int
    {
        global $wpdb;
        $wpdb->insert(self::rev_table(), [
            'template_id' => $template_id,
            'scene'       => $scene,
            'note'        => sanitize_text_field($note),
            'author_id'   => get_current_user_id(),
            'created_at'  => current_time('mysql'),
        ]);
        $id = (int) $wpdb->insert_id;
        self::prune_revisions($template_id);
        return $id;
    }

    /**
     * List the history. Without the scene — one entry is tens of KB and thirty of them are several
     * MB, while the list only needs times and authors. Fetch a single entry when the content is wanted.
     *
     * @return array<int,array>
 */
    public static function revisions(int $template_id, int $limit = self::KEEP_REVISIONS): array
    {
        global $wpdb;
        return $wpdb->get_results(
            $wpdb->prepare(
                'SELECT id, template_id, note, author_id, created_at, LENGTH(scene) AS size
                 FROM %i
                 WHERE template_id = %d ORDER BY created_at DESC, id DESC LIMIT %d',
                self::rev_table(),
                $template_id,
                $limit
            ),
            ARRAY_A
        ) ?: [];
    }

    public static function revision(int $id): ?array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare('SELECT * FROM %i WHERE id = %d', self::rev_table(), $id),
            ARRAY_A
        );
        return $row ?: null;
    }

    /** Delete entries beyond the retention count, or this table only ever grows. */
    public static function prune_revisions(int $template_id): void
    {
        global $wpdb;
        $rev = self::rev_table();
        $keep = (int) apply_filters('aumframe/keep_revisions', self::KEEP_REVISIONS, $template_id);

        $ids = $wpdb->get_col($wpdb->prepare(
            "SELECT id FROM {$rev} WHERE template_id = %d ORDER BY created_at DESC, id DESC LIMIT %d, 999999",
            $template_id,
            $keep
        ));
        if ($ids) {
            $in = implode(',', array_map('intval', $ids));
            $wpdb->query("DELETE FROM {$rev} WHERE id IN ({$in})");
        }
    }

    public static function delete(int $id): void
    {
        global $wpdb;
        // History travels with the template, or orphan rows nothing can reach are left behind
        $wpdb->delete(self::rev_table(), ['template_id' => $id]);
        $wpdb->delete(self::table(), ['id' => $id]);
    }
}