-- ============================================================================
-- Migration: Add 'content' column to courses table
-- Run this in phpMyAdmin or MySQL CLI
-- ============================================================================

-- Step 1: Add 'content' column if it doesn't exist
-- Uses a stored procedure since MySQL doesn't support IF NOT EXISTS with ALTER TABLE

DELIMITER //

DROP PROCEDURE IF EXISTS add_course_content_column//

CREATE PROCEDURE add_course_content_column()
BEGIN
    DECLARE col_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO col_exists
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'courses'
      AND COLUMN_NAME = 'content';

    IF col_exists = 0 THEN
        ALTER TABLE `courses` ADD COLUMN `content` LONGTEXT DEFAULT NULL AFTER `syllabus`;
    END IF;
END//

CALL add_course_content_column()//

DROP PROCEDURE IF EXISTS add_course_content_column//

DELIMITER ;

-- Step 2: Sync description → content (if content is empty but description exists)
UPDATE `courses`
SET `content` = `description`
WHERE (`content` IS NULL OR `content` = '')
  AND `description` IS NOT NULL
  AND `description` != '';

-- Step 3: Sync content → description (if description is empty but content exists)
UPDATE `courses`
SET `description` = `content`
WHERE (`description` IS NULL OR `description` = '')
  AND `content` IS NOT NULL
  AND `content` != '';

-- Step 4: Ensure description and syllabus columns exist

DELIMITER //

DROP PROCEDURE IF EXISTS ensure_course_columns//

CREATE PROCEDURE ensure_course_columns()
BEGIN
    DECLARE desc_exists INT DEFAULT 0;
    DECLARE syll_exists INT DEFAULT 0;

    SELECT COUNT(*) INTO desc_exists
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'courses'
      AND COLUMN_NAME = 'description';

    SELECT COUNT(*) INTO syll_exists
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'courses'
      AND COLUMN_NAME = 'syllabus';

    IF desc_exists = 0 THEN
        ALTER TABLE `courses` ADD COLUMN `description` TEXT DEFAULT NULL AFTER `fee`;
    END IF;

    IF syll_exists = 0 THEN
        ALTER TABLE `courses` ADD COLUMN `syllabus` TEXT DEFAULT NULL AFTER `description`;
    END IF;
END//

CALL ensure_course_columns()//

DROP PROCEDURE IF EXISTS ensure_course_columns//

DELIMITER ;
