<?php

declare(strict_types=1);

namespace Instinctor\Services;

use Instinctor\Core\Config;
use Instinctor\Core\DB;


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

/**
 * PageService — canonical data layer for Impulse pages.
 *
 * All reads and writes for Impulse page data go through this class.
 * Storage is the instinctor_pages table regardless of environment.
 *
 * WP MODE
 * -------
 * create() also calls wp_insert_post() to register a slug-routing shell in
 * wp_posts so WordPress's permalink system continues to work.  No actual
 * content is stored in wp_posts — all data lives in instinctor_pages.
 * trash() / restore() / permanentDelete() also update the wp_post status.
 *
 * STANDALONE MODE
 * ---------------
 * No wp_post shells.  The standalone Router resolves slugs directly via
 * getBySlug().  URLs are built from home_url() + slug.
 *
 * ELEMENT STORAGE
 * ---------------
 * Elements are stored as a JSON array in the `elements` column.
 * Settings are stored as a JSON object in the `settings` column.
 * All methods that read elements/settings return decoded PHP arrays.
 * All methods that write accept PHP arrays and encode internally.
 *
 * WP DATA MIGRATION
 * -----------------
 * migrateFromWordPress() copies existing wp_postmeta rows into the table.
 * Called once from Plugin::init() on WP, guarded by a Config flag.
 *
 * @since 3.18.56
 */
class PageService
{
    private static string $table;

    /**
     * Preview override — when set, every page-level read (getElements,
     * getSettings, getById, getBySlug) returns the override instead of
     * hitting the DB. Used by the library-preview renderer to reuse
     * impulse-page.php for saved scenes that aren't real pages.
     *
     * Managed via withPreviewOverride() which restores after a callable
     * runs, so the override never leaks across requests.
     *
     * @var array{elements:array,settings:array,name?:string}|null
     */
    private static ?array $previewOverride = null;

    /**
     * Temporarily swap in elements+settings so a template include renders
     * the library scene as if it were a real page.
     */
    public static function withPreviewOverride(array $elements, array $settings, callable $callback, string $name = 'Library Preview'): void
    {
        $prev = self::$previewOverride;
        self::$previewOverride = [
            'elements' => $elements,
            'settings' => $settings,
            'name'     => $name,
        ];
        try {
            $callback();
        } finally {
            self::$previewOverride = $prev;
        }
    }

    // ── Element-tree invariants (orphan prevention) ───────────────────────────

    /**
     * Canonical list of element types Impulse knows how to render.
     *
     * Single source of truth for the normaliser. If a stored element has
     * a `type` not in this list, normalizeTree() drops it (and its
     * descendants). This is the physical guarantee that prevents
     * "Unknown element type: X" placeholders ever surviving past a
     * single read/write cycle.
     *
     * Derived from the `case 'X':` labels in impulse_render_frontend_element()
     * in templates/impulse-page.php. When adding a new block type, add
     * it here too — the renderer switch and this constant must stay in
     * sync.
     *
     * TOP-LEVEL is restricted to a small set of container/chrome types
     * — see TOP_LEVEL_ALLOWED_TYPES. Anything else at top level is an
     * orphan (e.g. an image floating outside any section).
     *
     * @var string[]
     */
    public const ALLOWED_ELEMENT_TYPES = [
        // Layout
        'section', 'grid', 'rows', 'columns',
        // Text & media
        // 'title' + 'icon_card' joined in the 2026-06 type rename; the
        // legacy keys stay allowed because an allowlist miss here DELETES
        // user elements on the next save (normalizeTree Invariant 1) - the
        // rename sweep missed this constant and every title block saved
        // after v2.1.1416 was silently purged. Permissive is safe; strict
        // destroys data.
        'heading', 'title', 'divider', 'text', 'image', 'button', 'video', 'icon', 'icon_card', 'table',
        // Navigation, header, footer
        'site_logo', 'site_title', 'nav_menu', 'social', 'copyright',
        'header', 'footer',
        // Ecommerce
        'products', 'product_carousel', 'bundle', 'plans',
        'cart', 'mini_cart', 'checkout', 'price_list', 'shipping',
        // Blog — auto-listing of published type='post' pages
        'posts',
        // Configurator (build-a-pizza) — its own standalone block: a full-screen
        // visual product builder. NOT a products display mode any more; it carries
        // its whole catalogue (ingredients/sizes/art) in its own settings and adds
        // a synthetic cart line (no linked product). Must be allowlisted or
        // normalizeTree purges every configurator block on the next save.
        'configurator',
        // Account / auth
        'login', 'register', 'account', 'receipt',
        // Advanced
        'map', 'animator', 'flip_box',
        'carousel', 'slider', 'unfold', 'code', 'kinetic', 'chart',
        'contact', 'popup', 'modal', 'booking', 'network', 'reviews', 'marquee',
        // Instructor (Fitness & Lessons) vertical — member-facing class timetable
        // + booking. Renders the weekly schedule from ClassService.
        'schedule',
        // Marketing-page demo block (renders the editor mockup in a hero
        // screen — see includes/element-renderers/demo.php). It
        // is intentionally a top-level element type, not a child of any
        // existing container, because it owns its own panel/canvas layout.
        'demo',
        // Instagent (AI section generator) placeholder. It IS persisted —
        // it survives in the DB until the generator swaps it for the
        // produced element tree, and an unsaved/aborted one can reach the
        // frontend (where it renders nothing). Must be allowed here or
        // normalizeTree strips it on the creating save → panel GET 404s.
        'instagent',
    ];

    /**
     * Element types that may appear at the TOP LEVEL of a page
     * (parent_id === null).
     *
     *   - section: standard content container, the default
     *   - header / footer: chrome blocks used by global_header /
     *     global_footer pages (which exist as type="global_header" /
     *     "global_footer" rows), and occasionally embedded on regular pages
     *     to override the inherited chrome
     *
     * Anything else found at top level is an orphan and gets stripped by
     * normalizeTree() (Invariant 3).
     *
     * @var string[]
     */
    public const TOP_LEVEL_ALLOWED_TYPES = [
        'section', 'header', 'footer',
    ];

    /**
     * Largest integer JavaScript can represent exactly — Number.MAX_SAFE_INTEGER,
     * 2^53 - 1. Element ids above this are silently rounded the moment the
     * editor's JS reads them: two distinct elements can collapse onto one id,
     * which corrupts the whole tree (self-parented orphans, phantom duplicate
     * renders, delete-cascades that take out the wrong element). Every id we
     * persist MUST stay at or below this ceiling — enforced by sanitizeElementIds().
     */
    private const MAX_SAFE_ID = 9007199254740991;

    /**
     * Normalize an element tree, enforcing invariants that prevent
     * orphans and zombie data from surviving in the DB.
     *
     * Invariants enforced (cascading drops — when an element is removed,
     * all descendants are removed too):
     *
     *   1. Every element's `type` must be in ALLOWED_ELEMENT_TYPES.
     *      Unknown types → element + descendants stripped.
     *
     *   2. Every element with `parent_id !== null` must reference an
     *      existing surviving element. Dangling references → stripped.
     *
     * The normaliser is mode-agnostic. It deliberately does NOT police
     * which types may sit at the page root: in page mode loose blocks
     * are legal at the root, and only Scene mode requires every root
     * element to be a section. That top-level policy is a Scene-mode
     * constraint enforced at element creation (createImpulseElement),
     * never here — stripping root blocks here would silently delete the
     * page-mode designer's loose blocks.
     *
     * Idempotent: normalize(normalize($x)) === normalize($x). Safe to
     * call on every read and every write.
     *
     * Logs purged elements to error_log so we can audit what got
     * cleaned up retroactively.
     *
     * @param array $elements Raw element tree from JSON
     * @return array Cleaned element tree (may be empty)
     */
    public static function normalizeTree(array $elements): array
    {
        if (empty($elements)) {
            return [];
        }

        $allowed = array_flip(self::ALLOWED_ELEMENT_TYPES);
        $purged  = [];

        // Pass 1 — index by id (drop entries that lack an id field).
        // Build a working copy keyed by id for cheap lookup.
        $byId = [];
        foreach ($elements as $el) {
            if (!is_array($el) || !isset($el['id'])) {
                $purged[] = '[no id]';
                continue;
            }
            // Normalize id + parent_id to int when they are numeric strings.
            // The stored JSON has historically mixed string and int ids
            // (different creation paths). Server handlers match with strict
            // === against an (int)-cast id, so a string id silently fails to
            // match — move-into / reorder / settings-save 'succeed' but never
            // mutate (dragged/moved block reverts on reload). Casting here, the
            // single read funnel, makes every id-match consistent; saveElements
            // writes them back as ints, self-healing the data. Ids are < 2^53
            // (JS-safe) so the cast is lossless. Sergei 2026-06-05.
            if (is_string($el['id']) && ctype_digit($el['id'])) {
                $el['id'] = (int) $el['id'];
            }
            if (isset($el['parent_id']) && is_string($el['parent_id']) && ctype_digit($el['parent_id'])) {
                $el['parent_id'] = (int) $el['parent_id'];
            }
            // parent_id 0 means ROOT, exactly like null - JS callers
            // routinely send `parent || 0`. Before this coercion,
            // Invariant 2 saw 0 as "child of element id 0", purged the
            // element as dangling, then cascaded away its whole subtree
            // (the 2026-06-10 undo-eats-a-screen incident).
            if (isset($el['parent_id']) && $el['parent_id'] === 0) {
                $el['parent_id'] = null;
            }
            $byId[$el['id']] = $el;
        }

        // Pass 2 — mark drops by invariant violations. We iterate
        // until convergence: dropping an element may orphan its
        // children (Invariant 2), which then also get dropped.
        $changed = true;
        while ($changed) {
            $changed = false;
            foreach ($byId as $id => $el) {
                $type     = (string) ($el['type'] ?? '');
                $parentId = $el['parent_id'] ?? null;

                // Invariant 1 — unknown type.
                if (!isset($allowed[$type])) {
                    $purged[] = sprintf('id=%s type=%s reason=unknown-type', $id, $type);
                    unset($byId[$id]);
                    $changed = true;
                    continue;
                }

                // No top-level type policing here — page mode allows
                // loose blocks at the root; Scene mode's "root = sections
                // only" rule is enforced at element creation, not here.

                // Invariant 2 — parent must exist.
                if ($parentId !== null && !isset($byId[$parentId])) {
                    $purged[] = sprintf('id=%s type=%s reason=dangling-parent (parent_id=%s)', $id, $type, $parentId);
                    unset($byId[$id]);
                    $changed = true;
                    continue;
                }
            }
        }

        if (!empty($purged)) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log('[Instinctor PageService::normalizeTree] purged '
                . count($purged) . ' element(s): ' . implode('; ', $purged));
        }

        return array_values($byId);
    }

    /**
     * Guarantee every element id — and every id-valued cross-reference — stays
     * within JavaScript's safe-integer range. See MAX_SAFE_ID.
     *
     * Element ids historically came from scaffold tooling as
     * `millisecond-timestamp * 10000 + index`: 17-digit numbers ~1.8e16,
     * roughly double 2^53. The PHP renderer handles them fine (64-bit ints),
     * but the editor's JavaScript silently rounds them on read — so a grid
     * `…167` and its text child `…168` both become `…168` and the tree
     * corrupts on the next save. This runs on every write (saveElements) so an
     * oversized id can never reach the DB through any PHP path.
     *
     * Remaps the structural `id` + `parent_id`, plus any id-valued reference
     * buried in `settings` (e.g. unfold `items[].section_id`) — caught by an
     * exact-number swap over the settings JSON, which also covers
     * double-encoded string fields.
     *
     * @param array $elements Raw element tree.
     * @return array Element tree with every id in the safe range.
     */
    private static function sanitizeElementIds(array $elements): array
    {
        $unsafe = [];
        foreach ($elements as $el) {
            if (!is_array($el) || !isset($el['id'])) continue;
            $id = (int) $el['id'];
            if ($id > self::MAX_SAFE_ID) $unsafe[$id] = true;
        }
        if (empty($unsafe)) return $elements;

        // New ids: time()*1000 — 13 digits, ~1.8e12, safe for centuries —
        // bumped past anything already present so a remap never collides.
        $existing = [];
        foreach ($elements as $el) {
            if (is_array($el) && isset($el['id'])) $existing[(int) $el['id']] = true;
        }
        $remap = [];
        $next  = time() * 1000;
        foreach (array_keys($unsafe) as $oldId) {
            while (isset($existing[$next]) || in_array($next, $remap, true)) $next++;
            $remap[$oldId] = $next;
            $next++;
        }

        $pairs = [];
        foreach ($remap as $o => $n) $pairs[] = $o . '->' . $n;
        // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
        error_log('[Instinctor PageService::sanitizeElementIds] remapped '
            . count($remap) . ' element id(s) past JS safe-integer range: '
            . implode(', ', $pairs));

        foreach ($elements as &$el) {
            if (!is_array($el)) continue;
            if (isset($el['id']) && isset($remap[(int) $el['id']])) {
                $el['id'] = $remap[(int) $el['id']];
            }
            if (isset($el['parent_id']) && isset($remap[(int) $el['parent_id']])) {
                $el['parent_id'] = $remap[(int) $el['parent_id']];
            }
            if (isset($el['settings']) && is_array($el['settings'])) {
                $json = wp_json_encode($el['settings']);
                if ($json !== false && $json !== null) {
                    $swapped = $json;
                    foreach ($remap as $o => $n) {
                        // Digit boundaries so a 17-digit id is never matched
                        // as a substring of a longer number.
                        $swapped = preg_replace('/(?<![0-9])' . $o . '(?![0-9])/', (string) $n, $swapped);
                    }
                    if ($swapped !== $json) {
                        $decoded = json_decode($swapped, true);
                        if (is_array($decoded)) $el['settings'] = $decoded;
                    }
                }
            }
        }
        unset($el);

        return $elements;
    }

    // ── Bootstrap ─────────────────────────────────────────────────────────────

    private static function table(): string
    {
        if (!isset(self::$table)) {
            self::$table = DB::prefix() . 'instinctor_pages';
        }
        return self::$table;
    }

    // ── Page reads ────────────────────────────────────────────────────────────

    /**
     * Return all non-trash pages ordered by sort_order ASC, id ASC.
     *
     * @return array[] Each row is an associative array with keys:
     *                 id, name, slug, status, type, thumbnail,
     *                 sort_order, created_at, updated_at.
     *                 elements/settings are NOT included (fetch separately).
     */
    public static function getAll(): array
    {
        $t = self::table();
        $rows = DB::get_results(
            "SELECT id, name, slug, status, type, thumbnail, sort_order, settings, created_at, updated_at
             FROM {$t}
             WHERE status != 'trash'
             ORDER BY sort_order ASC, id ASC",
            'ARRAY_A'
        );
        return $rows ?: [];
    }
    /**
     * Return a single page row including elements and settings (decoded).
     * Returns null if not found or is trashed.
     */
    /** The owner_id a page belongs to, or null (vendor / unowned). */
    public static function ownerOf(int $pageId): ?int
    {
        $v = DB::get_var(DB::prepare("SELECT owner_id FROM " . self::table() . " WHERE id = %d", $pageId));
        return ($v === null || $v === '') ? null : (int) $v;
    }

    /**
     * Whether the logged-in user may edit this page. A site_owner is restricted
     * to pages they own (owner_id = their user id); vendor owner/admin/staff are
     * unchanged. Enforced at the builder + every page-mutation endpoint so a
     * tenant cannot reach another tenant's page by guessing an id.
     */
    public static function currentUserCanEdit(int $pageId): bool
    {
        if (!class_exists('\\Instinctor\\Core\\Auth')) return true;
        $u = \Instinctor\Core\Auth::user();
        if (!$u) return false;
        if (($u->role ?? '') === 'site_owner') {
            return self::ownerOf($pageId) === (int) \Instinctor\Core\Auth::userId();
        }
        return true;
    }

    public static function getById(int $id): ?array
    {
        if (self::$previewOverride !== null) {
            return [
                'id'        => $id,
                'name'      => self::$previewOverride['name'] ?? 'Library Preview',
                'slug'      => 'library-preview-' . $id,
                'status'    => 'draft',
                'type'      => 'page',
                'thumbnail' => '',
                'elements'  => self::$previewOverride['elements'],
                'settings'  => self::$previewOverride['settings'],
            ];
        }
        return self::getByIdReal($id);
    }

    private static function getByIdReal(int $id): ?array
    {
        if ($id <= 0) {
            return null;
        }
        $t   = self::table();
        $row = DB::get_row(DB::prepare(
            "SELECT * FROM {$t} WHERE id = %d LIMIT 1",
            $id
        ), 'ARRAY_A');
        if (!$row) {
            return null;
        }
        return self::decodeRow($row);
    }

    /**
     * Return a page by its slug. Null if not found or trashed.
     *
     * NOTE: matches both top-level pages AND designer-owned pages — used
     * internally by services that resolve any kind of page reference. For
     * PUBLIC top-level routing (e.g. `/profile` from a browser), use
     * getByPublicSlug() instead, otherwise designer-owned pages with slug
     * 'profile' would shadow the global namespace.
     */
    public static function getBySlug(string $slug): ?array
    {
        $t   = self::table();
        $row = DB::get_row(DB::prepare(
            "SELECT * FROM {$t} WHERE slug = %s AND status != 'trash' LIMIT 1",
            $slug
        ), 'ARRAY_A');
        if (!$row) {
            return null;
        }
        return self::decodeRow($row);
    }

    /**
     * Public-slug lookup: only matches pages NOT owned by a designer. Used
     * by the top-level `/:slug` router so that designer-owned pages (every
     * designer has slug='profile' for their home page, slug='starter' etc.
     * for gigs) never leak into the global URL namespace. Designer pages
     * are only reachable under `/d/<designer_slug>/<page_slug>`.
     */
    public static function getByPublicSlug(string $slug): ?array
    {
        $t   = self::table();
        $row = DB::get_row(DB::prepare(
            "SELECT * FROM {$t} WHERE slug = %s AND owner_id IS NULL AND status != 'trash' LIMIT 1",
            $slug
        ), 'ARRAY_A');
        if (!$row) {
            return null;
        }
        return self::decodeRow($row);
    }

    /**
     * Resolve a top-level slug to its routable page: a vendor page (owner_id
     * NULL) OR a PUBLISHED tenant page (owner_id set), so instinctor.com/<slug>
     * serves a claimed tenant site while the vendor's own pages keep working.
     * Slugs are globally unique across the site (soloProvision + slug-available
     * enforce it), so at most one page matches; vendor wins on any tie. Tenant
     * drafts/demos never resolve here (only owner_id-NULL rows may be
     * non-published, and the caller still checks status).
     */
    public static function getRoutableBySlug(string $slug): ?array
    {
        $t   = self::table();
        $row = DB::get_row(DB::prepare(
            "SELECT * FROM {$t}
              WHERE slug = %s AND status != 'trash'
                AND (owner_id IS NULL OR status = 'published')
              ORDER BY (owner_id IS NULL) DESC LIMIT 1",
            $slug
        ), 'ARRAY_A');
        return $row ? self::decodeRow($row) : null;
    }

    /**
     * Return global_header or global_footer page. Null if not yet created.
     */
    public static function getByType(string $type): ?array
    {
        if (!in_array($type, ['global_header', 'global_footer'], true)) {
            return null;
        }
        $t   = self::table();
        $row = DB::get_row(DB::prepare(
            "SELECT * FROM {$t} WHERE type = %s LIMIT 1",
            $type
        ), 'ARRAY_A');
        if (!$row) {
            return null;
        }
        return self::decodeRow($row);
    }

    /**
     * Published blog posts, newest first. A post is a page with type='post'.
     * Returns lightweight rows for index listing: id, name (title), slug,
     * thumbnail (featured image), excerpt + date (from settings), created_at.
     * The post body itself renders via the normal page renderer at /:slug.
     */
    public static function getPublishedPosts(int $limit = 50): array
    {
        $t    = self::table();
        $rows = DB::get_results(DB::prepare(
            "SELECT id, name, slug, thumbnail, settings, created_at
             FROM {$t}
             WHERE type = 'post' AND status = 'published' AND owner_id IS NULL
             ORDER BY created_at DESC
             LIMIT %d",
            max(1, $limit)
        ), 'ARRAY_A');
        $posts = [];
        foreach ($rows as $r) {
            $s = json_decode((string) ($r['settings'] ?? ''), true) ?: [];
            $posts[] = [
                'id'        => (int) $r['id'],
                'title'     => (string) ($r['name'] ?? ''),
                'slug'      => (string) ($r['slug'] ?? ''),
                'image'     => (string) ($r['thumbnail'] ?? ''),
                'excerpt'   => (string) ($s['post_excerpt'] ?? $s['excerpt'] ?? ''),
                // Author-set publish date wins; else the row's creation date.
                'date'      => (string) ($s['post_date'] ?? $r['created_at'] ?? ''),
            ];
        }
        return $posts;
    }

    /**
     * Return true if a page with this id exists (any status).
     */
    /**
     * Guarantee a row exists in instinctor_pages for the given page ID.
     *
     * If the row is already present — no-op.
     * If the page exists only in wp_postmeta (_impulse_enabled) — inserts the row,
     * copying elements and settings from postmeta so nothing is lost.
     *
     * Called before any element read/write to prevent silent save failures caused
     * by UPDATE matching 0 rows when the row is missing.
     */
    public static function ensurePageRow(int $id): void
    {
        if ($id <= 0) {
            return;
        }

        $t     = self::table();
        $count = (int) DB::get_var(DB::prepare(
            "SELECT COUNT(*) FROM {$t} WHERE id = %d",
            $id
        ));

        if ($count > 0) {
            return; // Already in instinctor_pages.
        }

        // Not in instinctor_pages — attempt to seed from wp_postmeta.
        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            if (!function_exists('get_post') || !function_exists('get_post_meta')) {
                return;
            }

            $post = get_post($id);
            if (!$post || $post->post_type !== 'page') {
                return;
            }

            $elemRaw = get_post_meta($id, '_impulse_elements', true);
            $settRaw = get_post_meta($id, '_impulse_settings', true);

            // Normalize elements
            if (is_array($elemRaw)) {
                $elements = $elemRaw;
            } elseif (is_string($elemRaw) && $elemRaw !== '') {
                $elements = json_decode($elemRaw, true) ?: (@unserialize($elemRaw) ?: []);
            } else {
                $elements = [];
            }

            // Normalize settings
            if (is_array($settRaw)) {
                $settings = $settRaw;
            } elseif (is_string($settRaw) && $settRaw !== '') {
                $settings = json_decode($settRaw, true) ?: (@unserialize($settRaw) ?: self::defaultSettings());
            } else {
                $settings = self::defaultSettings();
            }

            $status = $post->post_status === 'publish' ? 'published' : 'draft';
            $type   = get_post_meta($id, '_impulse_type', true) ?: 'page';
            $slug   = $post->post_name;
            $name   = $post->post_title;

            DB::query(DB::prepare(
                "INSERT IGNORE INTO {$t}
                 (id, name, slug, status, type, elements, settings, sort_order, created_at, updated_at)
                 VALUES (%d, %s, %s, %s, %s, %s, %s, %d, %s, %s)",
                $id,
                $name,
                $slug,
                $status,
                $type,
                wp_json_encode($elements),
                wp_json_encode($settings),
                (int) $post->menu_order,
                $post->post_date,
                $post->post_modified
            ));
        }
    }

        public static function exists(int $id): bool
    {
        if ($id <= 0) {
            return false;
        }
        $t = self::table();
        $inTable = (int) DB::get_var(DB::prepare(
            "SELECT COUNT(*) FROM {$t} WHERE id = %d",
            $id
        )) > 0;
        if ($inTable) {
            return true;
        }
        // Fallback: WP mode — accept pages that exist in wp_posts with _impulse_enabled.
        // This covers the period between plugin upgrade and migration completion.
        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            if (function_exists('get_post_meta') && get_post_meta($id, '_impulse_enabled', true) === '1') {
                // Opportunistically migrate this page into instinctor_pages now.
                self::migrateFromWordPress();
                return true;
            }
        }
        return false;
    }

    // ── Element / settings helpers ────────────────────────────────────────────

    /**
     * Return the elements array for a page, decoded.  Empty array if missing.
     */
    public static function getElements(int $pageId): array
    {
        if (self::$previewOverride !== null) {
            $override = is_array(self::$previewOverride['elements']) ? self::$previewOverride['elements'] : [];
            return self::normalizeTree($override);
        }
        $t   = self::table();
        // Use row existence check separate from elements value so an empty-elements
        // page in instinctor_pages is NOT mistakenly read from wp_postmeta.
        $row = DB::get_row(DB::prepare(
            "SELECT elements FROM {$t} WHERE id = %d LIMIT 1",
            $pageId
        ), ARRAY_A);

        if ($row !== null) {
            // Row exists in instinctor_pages — use it (may be empty array).
            $json    = $row['elements'] ?? '';
            $decoded = $json ? json_decode($json, true) : [];
            // Normalize on read — physical guarantee that orphan elements
            // (unknown types, dangling parent refs, non-section top-levels)
            // can never be returned from this service. See normalizeTree().
            return is_array($decoded) ? self::normalizeTree($decoded) : [];
        }

        // Row not in instinctor_pages — WP fallback (pre-migration state).
        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            if (function_exists('get_post_meta')) {
                $meta = get_post_meta($pageId, '_impulse_elements', true);
                if (is_array($meta)) {
                    return self::normalizeTree($meta);
                }
                // Could be JSON string stored by older code
                if (is_string($meta) && $meta !== '') {
                    $decoded = json_decode($meta, true);
                    if (is_array($decoded)) return self::normalizeTree($decoded);
                    $decoded = @unserialize($meta);
                    if (is_array($decoded)) return self::normalizeTree($decoded);
                }
            }
        }
        return [];
    }

    /**
     * Overwrite the elements array for a page.
     */
    public static function saveElements(int $pageId, array $elements): void
    {
        // Guarantee no id exceeds JavaScript's safe-integer ceiling — an
        // oversized id corrupts the tree the moment the editor reads it.
        $elements = self::sanitizeElementIds($elements);

        // Normalize before writing — physical guarantee that no orphan,
        // unknown-type, or invariant-breaking element ever lands in the
        // DB. The client cannot bypass this; every save runs through
        // normalizeTree(). See normalizeTree() for the invariants.
        $elements = self::normalizeTree($elements);

        // Snapshot the current state before overwrite.
        self::snapshotRevision($pageId, 'elements');

        $t    = self::table();
        $json = wp_json_encode($elements);
        
        // Guard against json_encode failure (invalid UTF-8, circular refs, etc.)
        if ($json === false || $json === null) {
            // Attempt to sanitize: force UTF-8 on all string values
            array_walk_recursive($elements, function(&$val) {
                if (is_string($val)) {
                    $val = mb_convert_encoding($val, 'UTF-8', 'UTF-8');
                }
            });
            $json = wp_json_encode($elements);
            if ($json === false || $json === null) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log('[Instinctor] saveElements FATAL: json_encode failed for page ' . $pageId . ' with ' . count($elements) . ' elements. json_last_error=' . json_last_error_msg());
                return;
            }
        }
        
        // Check if JSON exceeds MySQL max_allowed_packet (common cause of silent failures)
        $jsonLen = strlen($json);
        if ($jsonLen > 16 * 1024 * 1024) { // 16MB safety limit
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log('[Instinctor] saveElements WARNING: JSON is ' . round($jsonLen/1024/1024, 2) . 'MB for page ' . $pageId);
        }
        
        $now  = current_time('mysql');

        // Use direct SQL so we check for real errors rather than "0 rows changed".
        // DB::update returns 0 both for "no rows matched" AND "row matched but data identical"
        // — the latter would falsely trigger the wp_postmeta fallback and corrupt the read path.
        $result = DB::query(DB::prepare(
            "UPDATE {$t} SET elements = %s, updated_at = %s WHERE id = %d",
            $json, $now, $pageId
        ));

        if ($result === false) {
            $dbError = DB::last_error();
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log('[Instinctor] saveElements DB error for page ' . $pageId . ': ' . $dbError);
            // Row does not exist yet — fall back to wp_postmeta only if not in instinctor_pages.
            if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
                if (function_exists('update_post_meta')) {
                    update_post_meta($pageId, '_impulse_elements', $elements);
                }
            }
        }

        // Keep the page-level env/depth settings in sync with the hero section.
        // The Impulse builder writes depth onto the SECTION element, but the
        // frontend renderer reads it from page settings — without this mirror
        // they diverge (editor shows depth, live site shows nothing).
        self::mirrorHeroEnvToSettings($pageId, $elements);

        // Do NOT clear the cached thumbnail on save. Blanking it here wiped every
        // preview on ANY save — including bulk/script edits done outside the editor —
        // which emptied the whole Page Grid and forced reopening each page by hand.
        // The last capture stays put (a slightly stale preview beats a blank one); the
        // editor's close-capture refreshes it after a normal edit, so hero changes made
        // in the builder are reflected as soon as the page is closed. Sergei 2026-07-03.
    }

    /**
     * Mirror the hero section's environment/depth settings into the page-level
     * `settings` JSON.
     *
     * Architectural background: the Impulse builder stores the hero
     * environment (screen_cloud_effect / screen_depth_image / screen_depth_map /
     * screen_depth_enabled / screen_depth_strength) on the first SECTION element
     * inside the element tree. But templates/impulse-page.php renders the hero
     * environment from PAGE settings (see ~line 1959-1971). Those two stores
     * drifted: the editor (reads element settings) showed the depth image while
     * the live frontend (reads page settings) showed nothing — the "depth only
     * works in the editor" bug.
     *
     * Fix: on every elements write, take the FIRST top-level section whose
     * screen_cloud_effect is a real (non-'none') value and copy its env keys
     * into page settings. If no section has an effect set, the keys are removed
     * so a cleared effect propagates to the frontend too. Runs through the one
     * choke point (saveElements) so every write path — element PUT, full save,
     * import — stays consistent. Writes the settings column directly (no
     * revision snapshot: this is a derived mirror, not a user edit) and only
     * when a value actually changed, so steady-state autosaves do no extra work.
     *
     * @since 2.1.911
     */
    private static function mirrorHeroEnvToSettings(int $pageId, array $elements): void
    {
        $keys = [
            'screen_cloud_effect',
            'screen_depth_image',
            'screen_depth_map',
            'screen_depth_enabled',
            'screen_depth_strength',
        ];

        // First top-level section with a real environment effect = the hero.
        $heroEnv = null;
        foreach ($elements as $el) {
            if (!is_array($el) || ($el['type'] ?? '') !== 'section') {
                continue;
            }
            if (($el['parent_id'] ?? null) !== null) {
                continue; // nested section — not a screen
            }
            $s = $el['settings'] ?? null;
            if (!is_array($s)) {
                continue;
            }
            $eff = (string) ($s['screen_cloud_effect'] ?? '');
            if ($eff !== '' && $eff !== 'none') {
                $heroEnv = $s;
                break;
            }
        }

        // Read the current settings JSON raw — do NOT use getSettings() here, it
        // force-injects screen_mode=1 and we only want to touch the env keys.
        $t    = self::table();
        $json = DB::get_var(DB::prepare(
            "SELECT settings FROM {$t} WHERE id = %d LIMIT 1",
            $pageId
        ));
        $settings = $json ? json_decode($json, true) : [];
        if (!is_array($settings)) {
            $settings = [];
        }

        $changed = false;
        foreach ($keys as $k) {
            $cur = isset($settings[$k]) ? (string) $settings[$k] : '';
            $new = ($heroEnv !== null && isset($heroEnv[$k])) ? (string) $heroEnv[$k] : '';
            if ($new === $cur) {
                continue;
            }
            if ($new === '') {
                unset($settings[$k]);
            } else {
                $settings[$k] = $heroEnv[$k]; // preserve original scalar type
            }
            $changed = true;
        }

        if (!$changed) {
            return;
        }

        DB::query(DB::prepare(
            "UPDATE {$t} SET settings = %s WHERE id = %d",
            wp_json_encode($settings), $pageId
        ));
    }

    /**
     * Remove the cached thumbnail URL from the DB and delete the file on disk.
     * Safe to call even if no thumbnail exists (no-op in that case).
     */
    public static function clearThumbnail(int $pageId): void
    {
        $current = self::getThumbnail($pageId);
        if (!$current) {
            return; // Nothing to clear.
        }

        // Remove the file from disk.
        $file = \Instinctor\Core\Storage::basedir('impulse-previews') . '/page-' . $pageId . '.jpg';
        if (file_exists($file)) {
            wp_delete_file($file);
        }

        // Clear the URL in DB.
        $t = self::table();
        DB::query(DB::prepare(
            "UPDATE {$t} SET thumbnail = '' WHERE id = %d",
            $pageId
        ));
    }

    /**
     * Return the settings array for a page, decoded.  Empty array if missing.
     */
    public static function getSettings(int $pageId): array
    {
        if (self::$previewOverride !== null) {
            return is_array(self::$previewOverride['settings']) ? self::$previewOverride['settings'] : [];
        }
        $t    = self::table();
        $json = DB::get_var(DB::prepare(
            "SELECT settings FROM {$t} WHERE id = %d LIMIT 1",
            $pageId
        ));
        if (!$json) {
            return [];
        }
        $decoded = json_decode($json, true);
        if (!is_array($decoded)) {
            return [];
        }
        // Page mode is fully retired — every page is screen mode regardless
        // of stored data. Force the flag on so legacy/unset rows behave
        // identically to freshly created pages.
        return $decoded;
    }

    /**
     * Overwrite the settings object for a page.
     */
    public static function saveSettings(int $pageId, array $settings): void
    {
        self::snapshotRevision($pageId, 'settings');

        // Bottom-of-stack env backstop. Endpoint-level merges in
        // RestController guard against empty-string clobbers, but
        // callers that bypass the endpoints (page-convert default
        // overwrite, Instagent, ad-hoc scripts) can wipe keys by
        // simple omission — saveSettings does a full-column write.
        // If the incoming settings omits any of these page-level
        // env keys AND the DB currently has a non-empty value,
        // copy the DB value through so the palette + cloud effect
        // survive. Users can still clear an env explicitly by
        // sending 'none' or any other non-empty value. Locked
        // 2026-06-04 — page-env wipe should be intentional, not
        // accidental.
        static $envBackstopKeys = [
            'screen_cloud_effect',
            'screen_lava_color1','screen_lava_color2','screen_lava_color3','screen_lava_color4','screen_lava_bg_color',
            'screen_cosmos_color1','screen_cosmos_color2','screen_cosmos_color3','screen_cosmos_color4','screen_cosmos_bg_color',
            'screen_3d_color1','screen_3d_color2','screen_3d_color3','screen_3d_color4','screen_3d_bg_color',
            'screen_wave_color1','screen_wave_color2','screen_wave_color3','screen_wave_color4',
            'screen_depth_image','screen_depth_map',
            'hero_intensity','hero_speed','hero_screen_count','hero_transition',
        ];
        $existing = self::getSettings($pageId);
        if (is_array($existing) && !empty($existing)) {
            foreach ($envBackstopKeys as $k) {
                /* Treat both MISSING and EMPTY-string / null as "absent"
                   for these keys. An empty save still falls back to the
                   DB value. The user can clear an env explicitly only
                   via a non-empty value like 'none'. */
                $incomingMissingOrEmpty = !array_key_exists($k, $settings)
                    || $settings[$k] === ''
                    || $settings[$k] === null;
                if ($incomingMissingOrEmpty
                    && isset($existing[$k])
                    && $existing[$k] !== ''
                    && $existing[$k] !== null) {
                    $settings[$k] = $existing[$k];
                }
            }
        }

        $t = self::table();
        DB::update(
            $t,
            [
                'settings'   => wp_json_encode($settings),
                'updated_at' => current_time('mysql'),
            ],
            ['id' => $pageId]
        );
    }

    // ─── Page revisions ──────────────────────────────────────────────────────
    // Snapshots are captured at the top of saveElements/saveSettings (pre-overwrite).
    // Auto-pruned to last 20 per page. Restore is itself snapshotted (undoable).

    // 2026-05-24: bumped 20 → 100. The old window covered ~1 minute of
    // active editing — long enough that a routine "save → reconsider →
    // undo" worked, but easy to blow past during normal design work,
    // leaving a deletion unrecoverable once the user had touched
    // anything else. 100 revisions buys roughly 5 minutes of active
    // edits at typical autosave cadence; storage cost is negligible
    // (each revision is a small JSON blob).
    public const REVISIONS_PER_PAGE = 100;

    private static function revisionsTable(): string
    {
        return DB::prefix() . 'instinctor_page_revisions';
    }

    /**
     * Capture current page state into the revisions table, then prune.
     * $kind = 'elements' | 'settings' | 'both' (records what triggered the snapshot).
     */
    public static function snapshotRevision(int $pageId, string $kind = 'both'): void
    {
        if ($pageId <= 0) return;
        $t   = self::table();
        $rt  = self::revisionsTable();
        $row = DB::get_row(DB::prepare("SELECT elements, settings FROM {$t} WHERE id = %d", $pageId));
        if (!$row) return;

        DB::insert($rt, [
            'page_id'    => $pageId,
            'kind'       => $kind,
            'elements'   => $row->elements,
            'settings'   => $row->settings,
            'created_at' => current_time('mysql'),
        ]);

        // Prune oldest beyond REVISIONS_PER_PAGE.
        DB::query(DB::prepare(
            "DELETE FROM {$rt} WHERE page_id = %d AND id NOT IN (
                SELECT id FROM (
                    SELECT id FROM {$rt} WHERE page_id = %d ORDER BY id DESC LIMIT %d
                ) keep_set
            )",
            $pageId, $pageId, self::REVISIONS_PER_PAGE
        ));
    }

    /** List recent revisions for a page (most recent first). */
    public static function listRevisions(int $pageId): array
    {
        $rt = self::revisionsTable();
        $rows = DB::get_results(DB::prepare(
            "SELECT id, kind, created_at FROM {$rt}
             WHERE page_id = %d
             ORDER BY id DESC
             LIMIT %d",
            $pageId, self::REVISIONS_PER_PAGE
        ));
        return $rows ?: [];
    }

    /**
     * Restore a revision. Snapshots current state first (so the restore is undoable),
     * then writes the revision's elements/settings back into the page.
     * Returns true on success, false if revision not found / mismatched page.
     */
    public static function restoreRevision(int $pageId, int $revisionId): bool
    {
        $rt  = self::revisionsTable();
        $rev = DB::get_row(DB::prepare(
            "SELECT page_id, elements, settings FROM {$rt} WHERE id = %d",
            $revisionId
        ));
        if (!$rev || (int)$rev->page_id !== $pageId) return false;

        // Snapshot current state first so the restore itself is undoable.
        self::snapshotRevision($pageId, 'both');

        $t = self::table();
        DB::update(
            $t,
            [
                'elements'   => $rev->elements,
                'settings'   => $rev->settings,
                'updated_at' => current_time('mysql'),
            ],
            ['id' => $pageId]
        );
        self::clearThumbnail($pageId);
        return true;
    }

    /**
     * Return the thumbnail URL for a page (empty string if none).
     */
    public static function getThumbnail(int $pageId): string
    {
        $t = self::table();
        return (string) DB::get_var(DB::prepare(
            "SELECT thumbnail FROM {$t} WHERE id = %d LIMIT 1",
            $pageId
        ));
    }

    /**
     * Save a thumbnail URL for a page.
     *
     * `$device` selects which column to write — `desktop` (default, the
     * canonical `thumbnail` column) / `tablet` / `mobile`. Per-device
     * thumbnails power gig_grid's <picture> element so visitors on a phone
     * see the page captured at mobile aspect (9:16) instead of a desktop
     * screenshot squished into a portrait card. Unknown device strings
     * fall back to desktop so legacy callers keep working unchanged.
     */
    public static function saveThumbnail(int $pageId, string $url, string $device = 'desktop'): void
    {
        $t = self::table();
        $col = 'thumbnail';
        if ($device === 'tablet') $col = 'thumbnail_tablet';
        elseif ($device === 'mobile') $col = 'thumbnail_mobile';
        DB::update($t, [$col => $url], ['id' => $pageId]);
    }

    /**
     * Update updated_at to now (no-op unless callers explicitly need it).
     * Most saves already update updated_at; this exists for compatibility.
     */
    public static function touch(int $pageId): void
    {
        $t = self::table();
        DB::update($t, ['updated_at' => current_time('mysql')], ['id' => $pageId]);
    }

    // ── URL helper ────────────────────────────────────────────────────────────

    /**
     * Return the public URL for a page.
     *
     * On WP: delegates to get_permalink() since the wp_post shell still exists.
     * On standalone: builds home_url() + slug.
     */
    public static function getPageUrl(int $pageId): string
    {
        $page = self::getById($pageId);
        if (!$page) {
            return home_url('/');
        }
        $slug = trim($page['slug'] ?? '', '/');
        if (!$slug) {
            return home_url('/');
        }
        $base = rtrim(home_url('/'), '/');
        if ($pageId === (int) \Instinctor\Core\Config::get('instinctor_front_page_id', 0)) {
            return home_url('/');
        }
        return $base . '/' . $slug . '/';
    }

    /**
     * Resolve the URL for a page type (cart, checkout, shop, account, orders).
     *
     * Resolution chain:
     * 1. Config option instinctor_{$type}_page_id → Impulse page URL
     * 2. Impulse page with matching slug
     * 3. Falls back to home_url('/')
     *
     * Never returns hardcoded /shop/, /cart/, /checkout/ etc.
     */
    public static function resolveUrl(string $type): string
    {
        $pageId = (int) \Instinctor\Core\Config::get("instinctor_{$type}_page_id", 0);
        if ($pageId) {
            // Verify it's a real Impulse page before using get_permalink
            $page = self::getById($pageId);
            if ($page) {
                return self::getPageUrl($pageId);
            }
        }
        // Try slug-based lookup
        $slugPage = self::getBySlug($type);
        if ($slugPage) {
            return self::getPageUrl((int) $slugPage['id']);
        }
        return home_url('/');
    }

    // ── Page writes ───────────────────────────────────────────────────────────

    /**
     * Count non-trashed pages of a given type. Used for the free-tier page cap.
     */
    public static function countType(string $type): int
    {
        $t = self::table();
        return (int) DB::get_var(DB::prepare(
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- count over the plugin's own pages table via the Instinctor DB layer
            "SELECT COUNT(*) FROM {$t} WHERE type = %s AND status != 'trash'",
            $type
        ));
    }

    /**
     * Create a new page.  Returns the new page ID.
     *
     * On WP: registers a wp_post shell first to get the WP-assigned ID,
     * then inserts a matching row in instinctor_pages.
     * On standalone: inserts directly; ID comes from auto-increment.
     *
     * @param array $data {
     *   name       string  Page title
     *   slug       string  URL slug (auto-derived from name if omitted)
     *   status     string  'draft' | 'published'
     *   type       string  'page' | 'global_header' | 'global_footer'
     *   settings   array   Initial page settings
     *   sort_order int
     * }
     * @return int  New page ID, or 0 on failure.
     */
    public static function create(array $data): int
    {
        $name      = sanitize_text_field($data['name'] ?? 'Untitled Page');
        $status    = ($data['status'] ?? 'draft') === 'published' ? 'published' : 'draft';
        $type      = sanitize_text_field($data['type'] ?? 'page');
        $settings  = $data['settings'] ?? self::defaultSettings();
        $elements  = $data['elements'] ?? [];
        $sortOrder = (int) ($data['sort_order'] ?? 0);

        // Free build: hard cap at 3 standard pages (build-time, not license-gated;
        // header/footer/system types don't count). The REST + editor layer shows
        // the upgrade prompt — this is the backstop so no path can exceed it.
        if ($type === 'page' && function_exists('instinctor_locked')
            && instinctor_locked('multipage') && self::countType('page') >= 3) {
            return 0;
        }

        $t = self::table();

        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            // ── WP mode: register a slug-routing shell in wp_posts ──────────
            $slug = !empty($data['slug'])
                ? sanitize_title($data['slug'])
                : sanitize_title($name) . '-' . time();

            $wpStatus = $status === 'published' ? 'publish' : 'draft';
            $postId   = wp_insert_post([
                'post_type'   => 'page',
                'post_title'  => $name,
                'post_status' => $wpStatus,
                'post_name'   => $slug,
            ]);

            if (is_wp_error($postId) || !$postId) {
                return 0;
            }

            // Insert matching row using the WP-assigned ID
            DB::query(DB::prepare(
                "INSERT IGNORE INTO {$t}
                 (id, name, slug, status, type, elements, settings, sort_order)
                 VALUES (%d, %s, %s, %s, %s, %s, %s, %d)",
                $postId,
                $name,
                get_post_field('post_name', $postId), // resolved slug (WP may append -2 etc.)
                $status,
                $type,
                wp_json_encode($elements),
                wp_json_encode($settings),
                $sortOrder
            ));

            if ($status === 'published') {
                \Instinctor\Services\SEOService::pingSitemap();
                \Instinctor\Services\SEOService::indexNowPing([self::getPageUrl($postId)]);
            }
            return $postId;
        }

        // ── Standalone mode: insert directly ────────────────────────────────
        $slug = !empty($data['slug'])
            ? sanitize_title($data['slug'])
            : sanitize_title($name) . '-' . time();

        // Ensure slug uniqueness
        $slug = self::uniqueSlug($slug);

        DB::insert($t, [
            'name'       => $name,
            'slug'       => $slug,
            'status'     => $status,
            'type'       => $type,
            'elements'   => wp_json_encode($elements),
            'settings'   => wp_json_encode($settings),
            'sort_order' => $sortOrder,
        ]);

        $newId = (int) DB::insert_id();
        if ($status === 'published') {
            \Instinctor\Services\SEOService::pingSitemap();
            \Instinctor\Services\SEOService::indexNowPing([self::getPageUrl($newId)]);
        }
        return $newId;
    }

    /**
     * Update one or more fields on a page.
     *
     * Accepted $fields keys: name, slug, status, type, sort_order.
     * Elements/settings have dedicated save methods (saveElements/saveSettings).
     *
     * On WP: also updates the wp_post shell for name/status changes so that
     * WP's permalink routing stays consistent.
     *
     * @param int   $id
     * @param array $fields  Associative array of column→value pairs to update.
     * @return bool  True if the row was found and updated.
     */
    public static function update(int $id, array $fields): bool
    {
        if (!self::exists($id)) {
            return false;
        }

        $allowed = ['name', 'slug', 'status', 'type', 'sort_order'];
        $data    = array_intersect_key($fields, array_flip($allowed));

        if (empty($data)) {
            return false;
        }

        // Sanitize
        if (isset($data['name'])) {
            $data['name'] = sanitize_text_field($data['name']);
        }
        if (isset($data['slug'])) {
            $data['slug'] = self::uniqueSlug(sanitize_title($data['slug']), $id);
        }
        if (isset($data['status'])) {
            $data['status'] = in_array($data['status'], ['published', 'draft', 'trash'], true)
                ? $data['status'] : 'draft';
        }

        $data['updated_at'] = current_time('mysql');

        DB::update(self::table(), $data, ['id' => $id]);

        // ── WP: sync shell ───────────────────────────────────────────────────
        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            $wpData = ['ID' => $id];
            if (isset($fields['name'])) {
                $wpData['post_title'] = $data['name'];
            }
            if (isset($fields['slug'])) {
                $wpData['post_name'] = $data['slug'];
            }
            if (isset($fields['status'])) {
                $wpData['post_status'] = $data['status'] === 'published' ? 'publish' : 'draft';
            }
            if (count($wpData) > 1) {
                wp_update_post($wpData);
            }
        }

        if (isset($data['status']) && $data['status'] === 'published') {
            \Instinctor\Services\SEOService::pingSitemap();
            \Instinctor\Services\SEOService::indexNowPing([self::getPageUrl($id)]);
        }

        return true;
    }

    /**
     * Move a page to trash.
     */
    public static function trash(int $id): bool
    {
        if (!self::exists($id)) {
            return false;
        }
        DB::update(self::table(), ['status' => 'trash', 'updated_at' => current_time('mysql')], ['id' => $id]);
        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            wp_trash_post($id);
        }
        return true;
    }

    /**
     * Restore a trashed page to draft.
     */
    public static function restore(int $id): bool
    {
        if (!self::exists($id)) {
            return false;
        }
        DB::update(self::table(), ['status' => 'draft', 'updated_at' => current_time('mysql')], ['id' => $id]);
        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            wp_untrash_post($id);
            wp_update_post(['ID' => $id, 'post_status' => 'draft']);
        }
        return true;
    }

    /**
     * Permanently delete a page and its preview thumbnail.
     */
    public static function permanentDelete(int $id): bool
    {
        if (!self::exists($id)) {
            return false;
        }
        \Instinctor\Core\Storage::delete('impulse-previews/page-' . $id . '.jpg');
        DB::delete(self::table(), ['id' => $id]);
        if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
            wp_delete_post($id, true);
        }
        return true;
    }

    /**
     * Set sort_order for multiple pages.
     *
     * @param array $order  [ pageId => position, ... ]
     */
    public static function reorder(array $order): void
    {
        $t = self::table();
        foreach ($order as $pageId => $position) {
            $pageId   = (int) $pageId;
            $position = (int) $position;
            if ($pageId > 0) {
                DB::update($t, ['sort_order' => $position], ['id' => $pageId]);
                if (!defined('INSTINCTOR_ENV') || INSTINCTOR_ENV === 'wordpress') {
                    wp_update_post(['ID' => $pageId, 'menu_order' => $position]);
                }
            }
        }
    }

    /**
     * Duplicate a page (and all its elements).  Returns new page ID or 0.
     */
    public static function duplicate(int $sourceId): int
    {
        $source = self::getById($sourceId);
        if (!$source) {
            return 0;
        }

        $newName = $source['name'] . ' (Copy)';
        $newSlug = sanitize_title($source['name'] . '-copy-' . time());

        $newId = self::create([
            'name'       => $newName,
            'slug'       => $newSlug,
            'status'     => 'draft',
            'type'       => $source['type'],
            'settings'   => $source['settings'],
            'sort_order' => $source['sort_order'],
        ]);

        if (!$newId) {
            return 0;
        }

        // Remap element IDs and parent references
        $elements = $source['elements'];
        if (!empty($elements)) {
            $idMap   = [];
            $newEls  = [];
            $baseTime = time();

            foreach ($elements as $i => $el) {
                $oldId         = $el['id'];
                $newElId       = $baseTime * 10000 + $i;
                $idMap[$oldId] = $newElId;
                $el['id']      = $newElId;
                $el['page_id'] = $newId;
                $newEls[]      = $el;
            }
            foreach ($newEls as &$el) {
                if (!empty($el['parent_id']) && isset($idMap[$el['parent_id']])) {
                    $el['parent_id'] = $idMap[$el['parent_id']];
                }
            }
            unset($el);

            self::saveElements($newId, $newEls);
        }

        return $newId;
    }

    // ── WP data migration (one-time) ─────────────────────────────────────────

    /**
     * Copy existing Impulse pages from wp_postmeta into instinctor_pages.
     *
     * Called once from Plugin::init() on WordPress installs, guarded by a
     * Config flag so it never runs twice.  Idempotent — already-migrated
     * pages (matched by id) are skipped via INSERT IGNORE.
     *
     * Safe to call multiple times; the Config flag prevents repeat execution.
     */
    public static function migrateFromWordPress(): void
    {
        // Guard: only on WordPress
        if (defined('INSTINCTOR_ENV') && INSTINCTOR_ENV !== 'wordpress') {
            Config::set('instinctor_pages_migrated', '1');
            return;
        }

        global $wpdb;
        if (!isset($wpdb)) {
            return;
        }

        $target = self::table();

        // Check whether the table exists at all before querying it.
        // If Migration::run() hasn't created it yet, suppress the error and bail —
        // the migration will create the table and then this method will be called again.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.SlowDBQuery -- direct query against the plugin's own custom table; the WP options/post API does not model this data and the read is request-scoped (object caching would add complexity without benefit)
        $tableExists = (bool) $wpdb->get_var(
            $wpdb->prepare("SHOW TABLES LIKE %s", $target)
        );
        if (!$tableExists) {
            return;
        }

        // If the flag is set, verify migration is actually complete by comparing
        // the number of instinctor_pages rows against _impulse_enabled WP pages.
        // A partial migration (e.g. from ensurePageRow seeding one page) should
        // not stop the full migration from running.
        if (Config::get('instinctor_pages_migrated')) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.SlowDBQuery -- direct query against the plugin's own custom table; the WP options/post API does not model this data and the read is request-scoped (object caching would add complexity without benefit)
            $wpCount = (int) $wpdb->get_var(
                "SELECT COUNT(*) FROM {$wpdb->postmeta} WHERE meta_key = '_impulse_enabled' AND meta_value = '1'"
            );
            if ($wpCount === 0) {
                // No WP-sourced pages at all — nothing to migrate.
                return;
            }
            // phpcs:disable WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL,PluginCheck.Security.DirectDB.UnescapedDBParameter -- COUNT(*) over the plugin's own table ($target = self::table(), 1496); table identifiers cannot be bound via prepare and there is no user value.
            $dbCount = (int) $wpdb->get_var("SELECT COUNT(*) FROM {$target}");
            // phpcs:enable WordPress.DB.DirectDatabaseQuery,WordPress.DB.PreparedSQL,PluginCheck.Security.DirectDB.UnescapedDBParameter
            if ($dbCount >= $wpCount) {
                return; // All WP pages are in instinctor_pages — migration done.
            }
            // Count mismatch — some pages were not migrated. Re-run.
            Config::delete('instinctor_pages_migrated');
        }

        // Fetch all pages with _impulse_enabled meta from WP
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.SlowDBQuery -- direct query against the plugin's own custom table; the WP options/post API does not model this data and the read is request-scoped (object caching would add complexity without benefit)
        $posts = $wpdb->get_results(
            "SELECT
                p.ID,
                p.post_title,
                p.post_name,
                p.post_status,
                p.menu_order,
                p.post_date,
                p.post_modified
             FROM {$wpdb->posts} p
             INNER JOIN {$wpdb->postmeta} mi
                 ON mi.post_id = p.ID AND mi.meta_key = '_impulse_enabled' AND mi.meta_value = '1'
             WHERE p.post_type = 'page'",
            ARRAY_A
        );

        if (empty($posts)) {
            Config::set('instinctor_pages_migrated', '1');
            return;
        }

        foreach ($posts as $post) {
            $id = (int) $post['ID'];

            // Skip if already in table
            $exists = (int) DB::get_var(DB::prepare(
                "SELECT COUNT(*) FROM {$target} WHERE id = %d",
                $id
            ));
            if ($exists) {
                continue;
            }

            $getMeta = function (string $key) use ($wpdb, $id) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery,WordPress.DB.SlowDBQuery -- direct query against the plugin's own custom table; the WP options/post API does not model this data and the read is request-scoped (object caching would add complexity without benefit)
                return $wpdb->get_var($wpdb->prepare(
                    "SELECT meta_value FROM {$wpdb->postmeta}
                     WHERE post_id = %d AND meta_key = %s LIMIT 1",
                    $id,
                    $key
                ));
            };

            $type      = $getMeta('_impulse_type') ?: 'page';
            $elemRaw   = $getMeta('_impulse_elements') ?: '[]';
            $settRaw   = $getMeta('_impulse_settings') ?: '{}';
            $thumbnail = $getMeta('_impulse_thumbnail') ?: '';

            // Normalize elements: might be serialized PHP array from old meta
            $elements = is_string($elemRaw) ? json_decode($elemRaw, true) : $elemRaw;
            if (!is_array($elements)) {
                // Try unserializing (legacy format)
                $unserialized = @unserialize($elemRaw);
                $elements     = is_array($unserialized) ? $unserialized : [];
            }

            $settings = is_string($settRaw) ? json_decode($settRaw, true) : $settRaw;
            if (!is_array($settings)) {
                $unserialized = @unserialize($settRaw);
                $settings     = is_array($unserialized) ? $unserialized : self::defaultSettings();
            }

            $status = match ($post['post_status']) {
                'publish' => 'published',
                'trash'   => 'trash',
                default   => 'draft',
            };

            DB::query(DB::prepare(
                "INSERT IGNORE INTO {$target}
                 (id, name, slug, status, type, elements, settings, thumbnail, sort_order, created_at, updated_at)
                 VALUES (%d, %s, %s, %s, %s, %s, %s, %s, %d, %s, %s)",
                $id,
                $post['post_title'],
                $post['post_name'],
                $status,
                $type,
                wp_json_encode($elements),
                wp_json_encode($settings),
                $thumbnail,
                (int) $post['menu_order'],
                $post['post_date'],
                $post['post_modified']
            ));
        }

        Config::set('instinctor_pages_migrated', '1');
    }

    // ── Format helpers ────────────────────────────────────────────────────────

    /**
     * Build the standard page array returned to API callers.
     * Decodes elements and settings from JSON.
     */
    private static function decodeRow(array $row): array
    {
        $elements = json_decode($row['elements'] ?? '[]', true);
        $settings = json_decode($row['settings'] ?? '{}', true);

        // Fallback: might be PHP-serialized from legacy WP migration
        if (!is_array($elements)) {
            $elements = @unserialize($row['elements'] ?? '') ?: [];
        }
        if (!is_array($settings)) {
            $settings = @unserialize($row['settings'] ?? '') ?: self::defaultSettings();
        }

        // Normalize legacy http:// thumbnail URLs on SSL sites so the admin iframe
        // doesn't spam Mixed Content warnings for every page preview.
        $thumb = $row['thumbnail'] ?? '';
        if ($thumb && function_exists('is_ssl') && is_ssl() && strpos($thumb, 'http://') === 0) {
            $thumb = 'https://' . substr($thumb, 7);
        }
        return [
            'id'          => (int) $row['id'],
            'name'        => $row['name'],
            'slug'        => $row['slug'],
            'status'      => $row['status'],
            'type'        => $row['type'],
            'elements'    => $elements,
            'settings'    => $settings,
            'thumbnail'   => $thumb,
            'sort_order'  => (int) ($row['sort_order'] ?? 0),
            // Designer ownership is required for URL construction (the
            // public path is /:designer_slug/:page_slug for designer-owned
            // pages). Previously omitted, which caused getPageUrl() to
            // always emit the bare-slug form and the editor URL bar to
            // show a 404-bound link for every designer page.
            'owner_id' => (int) ($row['owner_id'] ?? 0),
            'created_at'  => $row['created_at'],
            'updated_at'  => $row['updated_at'],
        ];
    }

    /**
     * Default page settings.
     */
    public static function defaultSettings(): array
    {
        return [
            'padding_top'        => 20,
            'padding_right'      => 20,
            'padding_bottom'     => 20,
            'padding_left'       => 20,
            'element_spacing'    => 20,
            'max_content_width'  => 1440,
            'max_width'          => 1200,
            'bg_color'           => '#ffffff',
            // Zone model: the first N screens form the Hero zone (3D
            // fly-from-depth); the rest are the Body zone (snap scroll).
            // 0 = no hero — the whole page is one Body zone.
            'hero_screen_count'  => 0,
            // Reveal effects. Hero defaults to z_depth (dramatic). Body
            // defaults to fade — pure opacity, content-respecting. The
            // dramatic catalog (iris/dissolve/swirl/etc) is hero-only;
            // body's 4-effect curated set is Fade/Lift/Depth/Slide.
            'hero_transition'    => 'z_depth',
            'screen_transition'  => 'fade',
        ];
    }

    /**
     * Ensure a slug is unique in the table by appending a counter if needed.
     */
    private static function uniqueSlug(string $slug, ?int $excludeId = null): string
    {
        $t        = self::table();
        $base     = $slug;
        $counter  = 2;

        while (true) {
            if ($excludeId) {
                $existing = DB::get_var(DB::prepare(
                    "SELECT COUNT(*) FROM {$t} WHERE slug = %s AND id != %d",
                    $slug, $excludeId
                ));
            } else {
                $existing = DB::get_var(DB::prepare(
                    "SELECT COUNT(*) FROM {$t} WHERE slug = %s",
                    $slug
                ));
            }
            if (!(int) $existing) {
                break;
            }
            $slug = $base . '-' . $counter;
            $counter++;
        }

        return $slug;
    }

    // ── Page Export / Import ─────────────────────────────────────────────────

    /**
     * Export a page to a portable JSON format.
     * Contains everything needed to recreate the page on another site:
     * elements (with tree structure), page settings, and metadata.
     *
     * Media URLs are preserved as-is — the importer is responsible for
     * updating them after import (designer swaps images in the builder).
     *
     * @return array|null  Export data array, or null if page not found.
     */
    public static function exportPage(int $pageId): ?array
    {
        $page = self::getById($pageId);
        if (!$page) return null;

        $elements = $page['elements'] ?: [];
        $settings = $page['settings'] ?: [];

        // Collect all media URLs found in element settings (informational — helps designer know what to replace)
        $mediaUrls = [];
        $mediaKeys = ['image', 'image_url', 'bg_image', 'background_image', 'video_url', 'poster_image', 'logo_image', 'src', 'url', 'featured_image'];
        foreach ($elements as $el) {
            $s = $el['settings'] ?? [];
            foreach ($mediaKeys as $key) {
                if (!empty($s[$key]) && is_string($s[$key]) && (str_starts_with($s[$key], 'http') || str_starts_with($s[$key], '/'))) {
                    $mediaUrls[] = $s[$key];
                }
            }
            // Check images field (JSON array of URLs used in product/gallery blocks)
            if (!empty($s['images']) && is_string($s['images'])) {
                $imgs = json_decode($s['images'], true);
                if (is_array($imgs)) {
                    foreach ($imgs as $img) {
                        if (is_string($img) && $img) $mediaUrls[] = $img;
                        elseif (is_array($img) && !empty($img['url'])) $mediaUrls[] = $img['url'];
                    }
                }
            }
        }
        // Page settings media
        foreach ($mediaKeys as $key) {
            if (!empty($settings[$key]) && is_string($settings[$key]) && str_starts_with($settings[$key], 'http')) {
                $mediaUrls[] = $settings[$key];
            }
        }

        return [
            'instinctor_export' => true,
            'version'           => defined('INSTINCTOR_VERSION') ? INSTINCTOR_VERSION : '0',
            'exported_at'       => gmdate('Y-m-d H:i:s'),
            'page' => [
                'id'       => $pageId,
                'name'     => $page['name'],
                'slug'     => $page['slug'],
                'type'     => $page['type'],
                'settings' => $settings,
            ],
            'elements'   => $elements,
            'media_urls' => array_values(array_unique($mediaUrls)),
        ];
    }

    /**
     * Export page to JSON string.
     */
    public static function exportPageJson(int $pageId): ?string
    {
        $data = self::exportPage($pageId);
        if (!$data) return null;
        return json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
    }

    /**
     * Import a page from exported JSON data.
     * Creates a new page with all elements, remapping IDs to avoid collisions.
     *
     * @param  array|string $data  Export data (array or JSON string)
     * @param  string|null  $name  Override page name (null = use exported name)
     * @return int  New page ID, or 0 on failure.
     */
    public static function importPage($data, ?string $name = null): int
    {
        if (is_string($data)) {
            $data = json_decode($data, true);
        }
        if (!is_array($data) || empty($data['elements'])) {
            return 0;
        }

        $pageMeta = $data['page'] ?? [];
        $elements = $data['elements'];

        $pageName = $name ?: ($pageMeta['name'] ?? 'Imported Page');
        $pageSlug = self::uniqueSlug(
            sanitize_title($pageName . '-' . substr(md5((string) time()), 0, 6))
        );

        $newPageId = self::create([
            'name'     => $pageName,
            'slug'     => $pageSlug,
            'status'   => 'draft',
            'type'     => $pageMeta['type'] ?? 'page',
            'settings' => $pageMeta['settings'] ?? [],
        ]);

        if (!$newPageId) return 0;

        // Remap all element IDs + parent_id references
        if (!empty($elements)) {
            $idMap   = [];
            $newEls  = [];
            $baseId  = time() * 10000;

            foreach ($elements as $i => $el) {
                $oldId         = $el['id'] ?? $i;
                $newElId       = $baseId + $i;
                $idMap[$oldId] = $newElId;

                $el['id']      = $newElId;
                $el['page_id'] = $newPageId;
                $newEls[]      = $el;
            }

            // Second pass: remap parent_id references
            foreach ($newEls as &$el) {
                $pid = $el['parent_id'] ?? null;
                if ($pid !== null && $pid !== 0 && isset($idMap[$pid])) {
                    $el['parent_id'] = $idMap[$pid];
                }
            }
            unset($el);

            self::saveElements($newPageId, $newEls);
        }

        return $newPageId;
    }

    /**
     * Import into an existing page — replaces elements and settings, keeps page name/slug/id.
     *
     * @param  int          $pageId  Target page ID.
     * @param  array|string $data    Export data (array or JSON string).
     * @return bool
     */
    public static function importInto(int $pageId, $data): bool
    {
        if (is_string($data)) {
            $data = json_decode($data, true);
        }
        if (!is_array($data) || empty($data['elements'])) {
            return false;
        }
        if (!self::exists($pageId)) {
            return false;
        }

        $elements = $data['elements'];
        $pageMeta = $data['page'] ?? [];

        // Update page settings if present in export
        if (!empty($pageMeta['settings'])) {
            self::saveSettings($pageId, $pageMeta['settings']);
        }

        // Remap element IDs to avoid collisions
        if (!empty($elements)) {
            $idMap   = [];
            $newEls  = [];
            $baseId  = time() * 10000;

            foreach ($elements as $i => $el) {
                $oldId         = $el['id'] ?? $i;
                $newElId       = $baseId + $i;
                $idMap[$oldId] = $newElId;

                $el['id']      = $newElId;
                $el['page_id'] = $pageId;
                $newEls[]      = $el;
            }

            foreach ($newEls as &$el) {
                $pid = $el['parent_id'] ?? null;
                if ($pid !== null && $pid !== 0 && isset($idMap[$pid])) {
                    $el['parent_id'] = $idMap[$pid];
                }
            }
            unset($el);

            self::saveElements($pageId, $newEls);
        }

        return true;
    }

    // ── Full-site export ──────────────────────────────────────────────────────

    /**
     * Export the entire site as a single array.
     * Includes: all non-trash pages (with elements + settings), global config keys,
     * and a flat list of all media URLs referenced in element settings.
     */
    public static function exportSite(): array
    {
        $pages    = self::getAll();
        $exported = [];
        $allMedia = [];

        foreach ($pages as $row) {
            if (($row['status'] ?? '') === 'trash') continue;
            $pageExport = self::exportPage((int) $row['id']);
            if ($pageExport) {
                $exported[]  = $pageExport;
                $allMedia     = array_merge($allMedia, $pageExport['media_urls'] ?? []);
            }
        }

        // Global config keys relevant to site identity and settings
        $configKeys = [
            'instinctor_store_name', 'instinctor_store_description',
            'instinctor_currency', 'instinctor_currency_symbol', 'instinctor_currency_position',
            'instinctor_front_page_id', 'page_on_front', 'show_on_front',
            'instinctor_global_snippets', 'instinctor_global_css',
            'instinctor_chat_enabled', 'instinctor_chat_settings',
        ];
        $config = [];
        foreach ($configKeys as $key) {
            $val = \Instinctor\Core\Config::get($key);
            if ($val !== null && $val !== '' && $val !== false) {
                $config[$key] = $val;
            }
        }

        return [
            'instinctor_site_export' => true,
            'version'                => defined('INSTINCTOR_VERSION') ? INSTINCTOR_VERSION : '0',
            'exported_at'            => gmdate('Y-m-d H:i:s'),
            'page_count'             => count($exported),
            'config'                 => $config,
            'pages'                  => $exported,
            'media_urls'             => array_values(array_unique($allMedia)),
        ];
    }

    /**
     * Import a full site export.
     * Creates all pages, maps front_page_id to new IDs, writes config keys.
     * Returns [ 'pages' => count, 'errors' => [] ]
     */
    public static function importSite(array $data): array
    {
        if (empty($data['instinctor_site_export']) || empty($data['pages'])) {
            return ['pages' => 0, 'errors' => ['Invalid site export format']];
        }

        $errors  = [];
        $count   = 0;
        $idMap   = []; // old page id => new page id

        // First pass: import all pages, build old ID => new ID map
        foreach ($data['pages'] as $pageExport) {
            $oldId = (int) ($pageExport['page']['id'] ?? 0);
            $slug  = $pageExport['page']['slug'] ?? '';

            // If slug already exists, overwrite it
            $existing = $slug ? self::getBySlug($slug) : null;
            if ($existing) {
                self::importInto((int) $existing['id'], $pageExport);
                $newId = (int) $existing['id'];
            } else {
                $newId = self::importPage($pageExport);
            }

            if ($newId) {
                $count++;
                if ($oldId) $idMap[$oldId] = $newId;
            } else {
                $errors[] = 'Failed to import page: ' . ($pageExport['page']['name'] ?? '?');
            }
        }

        // Write config — remap front_page_id if we have a slug match
        if (!empty($data['config'])) {
            foreach ($data['config'] as $key => $val) {
                // Skip front page ID — we remap it below
                if ($key === 'instinctor_front_page_id' || $key === 'page_on_front') continue;
                \Instinctor\Core\Config::set($key, $val);
            }

            // Remap front page using old ID → new ID map
            if (!empty($data['config']['instinctor_front_page_id'])) {
                $oldFrontId = (int) $data['config']['instinctor_front_page_id'];
                $newFrontId = $idMap[$oldFrontId] ?? (empty($idMap) ? 0 : reset($idMap));
                if ($newFrontId) {
                    \Instinctor\Core\Config::set('instinctor_front_page_id', $newFrontId);
                    \Instinctor\Core\Config::set('page_on_front', $newFrontId);
                    \Instinctor\Core\Config::set('show_on_front', 'page');
                }
            }
        }

        return ['pages' => $count, 'errors' => $errors];
    }
}
