<?php
/**
 * ITSWEBER CMS – Migration 029: Add Foreign Key and Indexes
 *
 * - Adds foreign key on iw_page_sections.section_type → iw_section_types.type_key
 * - Adds index on iw_plugin_settings.plugin_slug for faster lookups
 *
 * @author    Benny Weber (ITSWEBER)
 * @copyright © ITSWEBER – www.itsweber.de
 * @contact   kontakt@itsweber.de
 */

use App\Database\Migration;

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

        // ── 1. Index on iw_plugin_settings.plugin_slug ──────────────
        // Check if index already exists
        $indexExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE()
               AND TABLE_NAME   = 'iw_plugin_settings'
               AND INDEX_NAME   = 'idx_plugin_slug'"
        );
        if ((int) $indexExists === 0) {
            $db->query(
                "ALTER TABLE `iw_plugin_settings`
                 ADD INDEX `idx_plugin_slug` (`plugin_slug`)",
                []
            );
        }

        // ── 2. Foreign key on iw_page_sections.section_type ─────────
        // Only add if both tables exist and FK does not yet exist
        $fkExists = $db->fetchColumn(
            "SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
             WHERE TABLE_SCHEMA    = DATABASE()
               AND TABLE_NAME      = 'iw_page_sections'
               AND CONSTRAINT_NAME = 'fk_page_sections_section_type'
               AND CONSTRAINT_TYPE = 'FOREIGN KEY'"
        );
        if ((int) $fkExists === 0) {
            // Ensure no orphan rows exist before adding FK
            $db->query(
                "DELETE ps FROM `iw_page_sections` ps
                 LEFT JOIN `iw_section_types` st ON ps.section_type = st.type_key
                 WHERE st.type_key IS NULL",
                []
            );

            $db->query(
                "ALTER TABLE `iw_page_sections`
                 ADD CONSTRAINT `fk_page_sections_section_type`
                 FOREIGN KEY (`section_type`) REFERENCES `iw_section_types` (`type_key`)
                 ON UPDATE CASCADE ON DELETE RESTRICT",
                []
            );
        }
    }

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

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

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