<?php
/**
 * ITSWEBER CMS – Migration 031: Add legacy foreign keys and indexes
 *
 * Adds missing FK constraints and indexes that were not included
 * in the original table definitions (migrations 002, 006, 007).
 *
 * - iw_pages.created_by → iw_users.id (ON DELETE SET NULL)
 * - iw_menu_items.parent_id → iw_menu_items.id (ON DELETE CASCADE)
 * - iw_settings: index on setting_group for faster filtering
 *
 * @author    Benny Weber (ITSWEBER)
 * @copyright © ITSWEBER – www.itsweber.de
 * @contact   kontakt@itsweber.de
 */

use App\Database\Migration;

class AddLegacyFkAndIndexes extends Migration
{
    public function up(): void
    {
        $db = $this->db;

        // ── 1. FK on iw_pages.created_by → iw_users.id ─────────────
        $fkExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
             WHERE TABLE_SCHEMA    = DATABASE()
               AND TABLE_NAME      = 'iw_pages'
               AND CONSTRAINT_NAME = 'fk_pages_created_by'
               AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
        );
        if ((int) $fkExists === 0) {
            // Clean orphan rows first
            $db->query(
                "UPDATE `iw_pages` SET created_by = NULL
                 WHERE created_by IS NOT NULL
                   AND created_by NOT IN (SELECT id FROM `iw_users`)",
                []
            );
            $db->query(
                "ALTER TABLE `iw_pages`
                 ADD CONSTRAINT `fk_pages_created_by`
                 FOREIGN KEY (`created_by`) REFERENCES `iw_users` (`id`)
                 ON DELETE SET NULL",
                []
            );
        }

        // ── 2. FK on iw_menu_items.parent_id (self-referential) ─────
        $fkExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
             WHERE TABLE_SCHEMA    = DATABASE()
               AND TABLE_NAME      = 'iw_menu_items'
               AND CONSTRAINT_NAME = 'fk_menu_items_parent'
               AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
        );
        if ((int) $fkExists === 0) {
            // Clean orphan parent_ids first
            $db->query(
                "UPDATE `iw_menu_items` SET parent_id = NULL
                 WHERE parent_id IS NOT NULL
                   AND parent_id NOT IN (SELECT id FROM (SELECT id FROM `iw_menu_items`) AS tmp)",
                []
            );
            $db->query(
                "ALTER TABLE `iw_menu_items`
                 ADD CONSTRAINT `fk_menu_items_parent`
                 FOREIGN KEY (`parent_id`) REFERENCES `iw_menu_items` (`id`)
                 ON DELETE CASCADE",
                []
            );
        }

        // ── 3. Index on iw_settings.setting_group ───────────────────
        $indexExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME   = 'iw_settings'
               AND INDEX_NAME   = 'idx_setting_group'"
        );
        if ((int) $indexExists === 0) {
            $db->query(
                "ALTER TABLE `iw_settings`
                 ADD INDEX `idx_setting_group` (`setting_group`)",
                []
            );
        }
    }

    public function down(): void
    {
        $db = $this->db;

        // Drop FK on pages.created_by
        $fkExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
             WHERE TABLE_SCHEMA    = DATABASE()
               AND TABLE_NAME      = 'iw_pages'
               AND CONSTRAINT_NAME = 'fk_pages_created_by'
               AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
        );
        if ((int) $fkExists > 0) {
            $db->query("ALTER TABLE `iw_pages` DROP FOREIGN KEY `fk_pages_created_by`", []);
        }

        // Drop FK on menu_items.parent_id
        $fkExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
             WHERE TABLE_SCHEMA    = DATABASE()
               AND TABLE_NAME      = 'iw_menu_items'
               AND CONSTRAINT_NAME = 'fk_menu_items_parent'
               AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
        );
        if ((int) $fkExists > 0) {
            $db->query("ALTER TABLE `iw_menu_items` DROP FOREIGN KEY `fk_menu_items_parent`", []);
        }

        // Drop index on settings.setting_group
        $indexExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME   = 'iw_settings'
               AND INDEX_NAME   = 'idx_setting_group'"
        );
        if ((int) $indexExists > 0) {
            $db->query("ALTER TABLE `iw_settings` DROP INDEX `idx_setting_group`", []);
        }
    }
}
