<?php

/**
 * Migration 0019 — `is_sandbox_3d` flag on images
 *
 * Adds a single boolean column to `inst_instinctor_images` so admins can
 * tag up to 10 (or any number of) images as "sandbox 3D backgrounds".
 *
 * The hero sandbox (Try It Yourself controls) queries images WHERE
 * `is_sandbox_3d=1` AND a depth-map derivative exists, then renders them as
 * the bottom-row thumbs. Visitors click a thumb to swap the scene's 3D
 * backdrop live without reloading.
 *
 * Idempotent: existence is checked in PHP via INFORMATION_SCHEMA before
 * any ALTER runs. Earlier draft used `ADD COLUMN IF NOT EXISTS` — that's
 * MariaDB-only syntax and threw a 1064 syntax error on MySQL 8.x, which
 * blocked the migration runner from advancing past 0019.
 *
 * @since 2.1.329
 */

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

return [
    'description' => 'Add `is_sandbox_3d` flag column to inst_instinctor_images',

    // Schema work happens in the PHP callback so we can guard each
    // ALTER on its own existence check. Pure SQL `IF NOT EXISTS` is
    // not portable between MySQL and MariaDB for ALTER TABLE.
    'up' => [],

    'php' => function () {
        $table = \Instinctor\Core\DB::prefix() . 'instinctor_images';

        // Add column if missing.
        $colExists = \Instinctor\Core\DB::get_var(
            \Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME   = %s
                   AND COLUMN_NAME  = 'is_sandbox_3d'",
                $table
            )
        );
        if (!$colExists) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table}
                 ADD COLUMN is_sandbox_3d TINYINT(1) NOT NULL DEFAULT 0
                 AFTER optimized"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('is_sandbox_3d column add failed: '
                    . \Instinctor\Core\DB::last_error())
                );
            }
        }

        // Add index if missing.
        $idxExists = \Instinctor\Core\DB::get_var(
            \Instinctor\Core\DB::prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
                 WHERE TABLE_SCHEMA = DATABASE()
                   AND TABLE_NAME   = %s
                   AND INDEX_NAME   = 'idx_sandbox_3d'",
                $table
            )
        );
        if (!$idxExists) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table} ADD INDEX idx_sandbox_3d (is_sandbox_3d)"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('idx_sandbox_3d index add failed: '
                    . \Instinctor\Core\DB::last_error())
                );
            }
        }
    },

    'down' => [
        // DROP INDEX/COLUMN IF EXISTS — these forms ARE portable; both
        // MySQL 8.0+ and MariaDB accept them. Down rarely runs in prod
        // so a one-platform path is acceptable here anyway.
        "ALTER TABLE {prefix}instinctor_images DROP INDEX IF EXISTS idx_sandbox_3d",
        "ALTER TABLE {prefix}instinctor_images DROP COLUMN IF EXISTS is_sandbox_3d",
    ],
];
