<?php

/**
 * Migration 0040 — rename pages.designer_id -> owner_id; drop is_designer_home
 *
 * The `designer_id` column outlived the designer marketplace (torn down
 * 2.1.413+, remainder swept 2.1.2213). It is now purely the page-owner id:
 * written by platform provisioning + Instagent, read by page resolution. Rename
 * it to `owner_id`. The `is_designer_home` flag drove the retired /d/<slug>
 * designer-home routing and is no longer read anywhere — drop it (and its
 * composite index).
 *
 * Guarded via the 'php' callback so it is safe on any prior column state (fresh
 * install where 0010/0011 just ran, an old site still carrying both columns, or
 * a re-run): every step checks information_schema before acting.
 *
 * @since 2.1.2213
 */

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- schema migration on the plugin's own table

return [
    'description' => 'Rename pages.designer_id -> owner_id; drop is_designer_home',

    'up' => [],

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

        $hasCol = function (string $name) use ($t): bool {
            return (int) \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 = %s",
                $t, $name
            )) > 0;
        };
        $hasIdx = function (string $name) use ($t): bool {
            return (int) \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 = %s",
                $t, $name
            )) > 0;
        };

        // Dead is_designer_home flag + its composite index.
        if ($hasIdx('designer_home')) {
            \Instinctor\Core\DB::query("ALTER TABLE {$t} DROP KEY designer_home");
        }
        if ($hasCol('is_designer_home')) {
            \Instinctor\Core\DB::query("ALTER TABLE {$t} DROP COLUMN is_designer_home");
        }

        // Rename designer_id -> owner_id, carrying its index across.
        if ($hasCol('designer_id') && !$hasCol('owner_id')) {
            if ($hasIdx('designer_id')) {
                \Instinctor\Core\DB::query("ALTER TABLE {$t} DROP KEY designer_id");
            }
            \Instinctor\Core\DB::query("ALTER TABLE {$t} CHANGE COLUMN designer_id owner_id INT UNSIGNED NULL DEFAULT NULL");
            if (!$hasIdx('owner_id')) {
                \Instinctor\Core\DB::query("ALTER TABLE {$t} ADD KEY owner_id (owner_id)");
            }
        }
    },
];
