-- ============================================================
-- Admin News Management: Migration for bilingual + nullable branch_id
-- Gmind Institute Database Migration
-- ============================================================
--
-- This script makes branch_id nullable in the branch_news table
-- so that general institute news (not tied to a branch) can be
-- created. It also adds bilingual (Hindi) columns if they don't
-- already exist from the migrate_bilingual_columns.sql migration.
--
-- Run this ONCE on your database.
-- ============================================================

-- ─── Make branch_id nullable for general news ─────────────

-- First, drop the foreign key constraint if it exists
ALTER TABLE `branch_news` DROP FOREIGN KEY `fk_branch_news_branch_id`;

-- Make branch_id nullable
ALTER TABLE `branch_news` MODIFY `branch_id` BIGINT UNSIGNED NULL;

-- Re-add the foreign key constraint (now allows NULL)
ALTER TABLE `branch_news`
    ADD CONSTRAINT `fk_branch_news_branch_id`
    FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE;

-- ─── Bilingual columns (Hindi) ────────────────────────────
-- These may already exist if migrate_bilingual_columns.sql was run.
-- The IF NOT EXISTS check is handled by checking column existence.

-- Add title_hi if not exists
SET @dbname = DATABASE();
SET @tablename = 'branch_news';

-- title_hi
SET @columnname = 'title_hi';
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
    'SELECT 1',
    'ALTER TABLE `branch_news` ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- content_hi
SET @columnname = 'content_hi';
SET @preparedStatement = (SELECT IF(
    (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = @dbname AND TABLE_NAME = @tablename AND COLUMN_NAME = @columnname) > 0,
    'SELECT 1',
    'ALTER TABLE `branch_news` ADD COLUMN `content_hi` LONGTEXT NULL AFTER `content`'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- ============================================================
-- Verification (run to check migration success)
-- ============================================================
-- SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE
--   FROM INFORMATION_SCHEMA.COLUMNS
--   WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'branch_news'
--   ORDER BY ORDINAL_POSITION;

-- ============================================================
-- Rollback Script (to undo this migration)
-- ============================================================
-- ALTER TABLE `branch_news` DROP FOREIGN KEY `fk_branch_news_branch_id`;
-- ALTER TABLE `branch_news` MODIFY `branch_id` BIGINT UNSIGNED NOT NULL;
-- ALTER TABLE `branch_news`
--     ADD CONSTRAINT `fk_branch_news_branch_id`
--     FOREIGN KEY (`branch_id`) REFERENCES `branches` (`id`)
--     ON DELETE CASCADE ON UPDATE CASCADE;
-- ALTER TABLE `branch_news` DROP COLUMN `title_hi`;
-- ALTER TABLE `branch_news` DROP COLUMN `content_hi`;
