-- ============================================================================
-- GMIND INSTITUTE — Fix courses table: ensure all required columns exist
-- ============================================================================
-- Run this SQL in phpMyAdmin against the `advaysin_gmind` database.
-- Idempotent — safe to run multiple times.
--
-- This is the companion to the "course is not adding" fix. It ensures:
--   - `courses.content` column exists (LONGTEXT) — used by CourseController::show()
--   - `courses.description` column exists (TEXT) — fallback if content is empty
--   - `courses.syllabus` column exists (TEXT)
--   - `courses.is_featured` column exists (TINYINT(1))
--   - `courses.active` column exists (TINYINT(1))
--   - `courses.image` column exists (VARCHAR(500))
--   - `courses.category` column exists (ENUM or VARCHAR — accepts either)
--
-- Also backfills content ↔ description so neither field stays empty when the
-- other has data.
-- ============================================================================

-- content column (the one most commonly missing on old backups)
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'content');
SET @sql := IF(@col = 0,
    'ALTER TABLE `courses` ADD COLUMN `content` LONGTEXT DEFAULT NULL AFTER `syllabus`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- description column
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'description');
SET @sql := IF(@col = 0,
    'ALTER TABLE `courses` ADD COLUMN `description` TEXT DEFAULT NULL AFTER `fee`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- syllabus column
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'syllabus');
SET @sql := IF(@col = 0,
    'ALTER TABLE `courses` ADD COLUMN `syllabus` TEXT DEFAULT NULL AFTER `description`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- image column
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'image');
SET @sql := IF(@col = 0,
    'ALTER TABLE `courses` ADD COLUMN `image` VARCHAR(500) DEFAULT NULL AFTER `syllabus`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- is_featured column
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'is_featured');
SET @sql := IF(@col = 0,
    'ALTER TABLE `courses` ADD COLUMN `is_featured` TINYINT(1) NOT NULL DEFAULT 0 AFTER `image`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- active column
SET @col := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses' AND COLUMN_NAME = 'active');
SET @sql := IF(@col = 0,
    'ALTER TABLE `courses` ADD COLUMN `active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `is_featured`',
    'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Backfill: copy description → content for any row where content is empty
UPDATE `courses`
SET `content` = `description`
WHERE (`content` IS NULL OR `content` = '')
  AND `description` IS NOT NULL
  AND `description` != '';

-- Backfill: copy content → description for any row where description is empty
UPDATE `courses`
SET `description` = `content`
WHERE (`description` IS NULL OR `description` = '')
  AND `content` IS NOT NULL
  AND `content` != '';

-- Verify (run this SELECT manually after the script):
-- DESCRIBE courses;
-- SELECT id, name, category, is_featured, active,
--        (description IS NOT NULL AND description != '') AS has_desc,
--        (content IS NOT NULL AND content != '') AS has_content
-- FROM courses;
