<?php

/**
 * Migration 0024 — `depth_rel_path` column on images
 *
 * Records the storage-relative path of a depth map paired with each source
 * image. Set by ajaxSaveDepthMap right after the depth file lands on disk,
 * so the image picker can surface "My 3D" — every image the user has ever
 * had a depth generated for — without rescanning the filesystem on every
 * picker open.
 *
 * Schema:
 *   - depth_rel_path VARCHAR(255) NULL, indexed for the WHERE != NULL scan
 *     the 3D-filtered picker query runs.
 *
 * Backfill is opportunistic, not part of this migration. The picker's
 * filter=3d mode falls back to a naming-convention scan when a row has
 * an empty depth_rel_path but a sibling depth file exists on disk, and
 * persists the discovery back to the DB on first sight. Avoids a long
 * blocking migration on installs with thousands of historical images.
 *
 * Idempotent via INFORMATION_SCHEMA guard, same pattern as 0019.
 *
 * @since 2.1.873
 */

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

return [
    'description' => 'Add `depth_rel_path` column + index to inst_instinctor_images',

    'up' => [],

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

        // Column.
        $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  = 'depth_rel_path'",
                $table
            )
        );
        if (!$colExists) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table}
                 ADD COLUMN depth_rel_path VARCHAR(255) NULL DEFAULT NULL
                 AFTER is_sandbox_3d"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('depth_rel_path column add failed: '
                    . \Instinctor\Core\DB::last_error())
                );
            }
        }

        // Index — drives the `WHERE depth_rel_path IS NOT NULL` filter the
        // picker hits in 3D mode. A plain b-tree on the column does the
        // job; we never search by depth path value.
        $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_depth_rel_path'",
                $table
            )
        );
        if (!$idxExists) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table} ADD INDEX idx_depth_rel_path (depth_rel_path)"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('idx_depth_rel_path index add failed: '
                    . \Instinctor\Core\DB::last_error())
                );
            }
        }
    },

    'down' => [
        "ALTER TABLE {prefix}instinctor_images DROP INDEX IF EXISTS idx_depth_rel_path",
        "ALTER TABLE {prefix}instinctor_images DROP COLUMN IF EXISTS depth_rel_path",
    ],
];
