<?php

/**
 * Migration 0028 — `cutout_rel_path` column on images
 *
 * Records the storage-relative path of a transparent-background subject
 * cutout (WebP with alpha) paired with each source image. Generated by
 * the rembg-based cutout service on the platform VPS; the bytes are
 * returned to the user's site, saved locally to the same images tree,
 * and the rel_path persisted here.
 *
 * Invariant: a row never has BOTH `depth_rel_path` AND `cutout_rel_path`
 * non-null. The depth pipeline skips RGBA sources (alpha images are
 * already cutouts and depth-map on a cutout produces garbage parallax).
 *
 * Schema:
 *   - cutout_rel_path VARCHAR(255) NULL, indexed for the "My Cutouts"
 *     filter the picker uses (mirror of My 3D for depth maps).
 *
 * Backfill: none. Cutouts are generated on demand.
 *
 * Idempotent via INFORMATION_SCHEMA guard, same pattern as 0024.
 *
 * @since 2.1.1194
 */

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

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

        // Index — drives the picker's `WHERE cutout_rel_path IS NOT NULL`
        // filter for the "My Cutouts" tab.
        $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_cutout_rel_path'",
                $table
            )
        );
        if (!$idxExists) {
            \Instinctor\Core\DB::query(
                "ALTER TABLE {$table} ADD INDEX idx_cutout_rel_path (cutout_rel_path)"
            );
            if (\Instinctor\Core\DB::last_error() !== '') {
                throw new \RuntimeException(
                    esc_html('idx_cutout_rel_path index add failed: '
                    . \Instinctor\Core\DB::last_error())
                );
            }
        }
    },

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