-- ============================================================
-- Multi-Language Support: Add Hindi (_hi) columns
-- Gmind Institute Database Migration
-- ============================================================
--
-- This script adds bilingual columns to existing tables.
-- English columns remain the default; _hi columns are optional.
-- If _hi columns are empty, the PHP getBilingual() function
-- automatically falls back to the English value.
--
-- Run this ONCE on your database. All new columns allow NULL
-- so existing data is not affected.
-- ============================================================

-- ─── Courses Table ────────────────────────────────────────
ALTER TABLE `courses`
    ADD COLUMN `name_hi` VARCHAR(255) NULL AFTER `name`,
    ADD COLUMN `description_hi` TEXT NULL AFTER `description`,
    ADD COLUMN `duration_hi` VARCHAR(100) NULL AFTER `duration`;

-- ─── Sliders Table ────────────────────────────────────────
ALTER TABLE `sliders`
    ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`,
    ADD COLUMN `subtitle_hi` TEXT NULL AFTER `subtitle`;

-- ─── FAQs Table ───────────────────────────────────────────
ALTER TABLE `faqs`
    ADD COLUMN `question_hi` TEXT NULL AFTER `question`,
    ADD COLUMN `answer_hi` TEXT NULL AFTER `answer`;

-- ─── Testimonials Table ───────────────────────────────────
ALTER TABLE `testimonials`
    ADD COLUMN `review_hi` TEXT NULL AFTER `review`,
    ADD COLUMN `name_hi` VARCHAR(255) NULL AFTER `name`,
    ADD COLUMN `course_name_hi` VARCHAR(255) NULL AFTER `course_name`;

-- ─── Blog Posts Table ─────────────────────────────────────
ALTER TABLE `blog_posts`
    ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`,
    ADD COLUMN `content_hi` LONGTEXT NULL AFTER `content`;

-- ─── Branch News Table ────────────────────────────────────
ALTER TABLE `branch_news`
    ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`,
    ADD COLUMN `content_hi` LONGTEXT NULL AFTER `content`;

-- ─── Announcements Table ──────────────────────────────────
ALTER TABLE `announcements`
    ADD COLUMN `text_hi` TEXT NULL AFTER `text`;

-- ─── Exams Table ──────────────────────────────────────────
ALTER TABLE `exams`
    ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`,
    ADD COLUMN `instructions_hi` TEXT NULL AFTER `instructions`;

-- ─── Exam Questions Table ─────────────────────────────────
ALTER TABLE `exam_questions`
    ADD COLUMN `question_text_hi` TEXT NULL AFTER `question_text`,
    ADD COLUMN `option_a_hi` VARCHAR(500) NULL AFTER `option_a`,
    ADD COLUMN `option_b_hi` VARCHAR(500) NULL AFTER `option_b`,
    ADD COLUMN `option_c_hi` VARCHAR(500) NULL AFTER `option_c`,
    ADD COLUMN `option_d_hi` VARCHAR(500) NULL AFTER `option_d`;

-- ─── Practice Sets Table ──────────────────────────────────
ALTER TABLE `practice_sets`
    ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`,
    ADD COLUMN `description_hi` TEXT NULL AFTER `description`;

-- ─── Practice Questions Table ─────────────────────────────
ALTER TABLE `practice_questions`
    ADD COLUMN `question_text_hi` TEXT NULL AFTER `question_text`,
    ADD COLUMN `option_a_hi` VARCHAR(500) NULL AFTER `option_a`,
    ADD COLUMN `option_b_hi` VARCHAR(500) NULL AFTER `option_b`,
    ADD COLUMN `option_c_hi` VARCHAR(500) NULL AFTER `option_c`,
    ADD COLUMN `option_d_hi` VARCHAR(500) NULL AFTER `option_d`;

-- ─── Gallery Table ────────────────────────────────────────
ALTER TABLE `gallery`
    ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`;

-- ─── Toppers Table ────────────────────────────────────────
ALTER TABLE `toppers`
    ADD COLUMN `name_hi` VARCHAR(255) NULL AFTER `name`,
    ADD COLUMN `course_name_hi` VARCHAR(255) NULL AFTER `course_name`,
    ADD COLUMN `branch_name_hi` VARCHAR(255) NULL AFTER `branch_name`;

-- ─── Branches Table ───────────────────────────────────────
ALTER TABLE `branches`
    ADD COLUMN `city_hi` VARCHAR(100) NULL AFTER `city`,
    ADD COLUMN `address_hi` TEXT NULL AFTER `address`;

-- ─── Pages Table (About, etc.) ────────────────────────────
ALTER TABLE `pages`
    ADD COLUMN `title_hi` VARCHAR(255) NULL AFTER `title`,
    ADD COLUMN `content_hi` LONGTEXT NULL AFTER `content`;

-- ─── Settings Table (bilingual site settings) ─────────────
-- Note: Settings use key-value pairs, so we add a _hi suffix
-- to setting_key for Hindi versions of settings values
INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('site_tagline_hi', ''),
    ('site_address_hi', ''),
    ('offer_popup_title_hi', ''),
    ('offer_popup_description_hi', ''),
    ('newsletter_title_hi', ''),
    ('newsletter_subtitle_hi', ''),
    ('newsletter_button_text_hi', ''),
    ('demo_class_title_hi', ''),
    ('demo_class_subtitle_hi', '');

-- ============================================================
-- Verification Queries (run to check migration success)
-- ============================================================
-- SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
--   WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'courses'
--   AND COLUMN_NAME LIKE '%_hi';

-- ============================================================
-- Rollback Script (to undo this migration)
-- ============================================================
-- ALTER TABLE `courses` DROP COLUMN `name_hi`, DROP COLUMN `description_hi`, DROP COLUMN `duration_hi`;
-- ALTER TABLE `sliders` DROP COLUMN `title_hi`, DROP COLUMN `subtitle_hi`;
-- ALTER TABLE `faqs` DROP COLUMN `question_hi`, DROP COLUMN `answer_hi`;
-- ALTER TABLE `testimonials` DROP COLUMN `review_hi`, DROP COLUMN `name_hi`, DROP COLUMN `course_name_hi`;
-- ALTER TABLE `blog_posts` DROP COLUMN `title_hi`, DROP COLUMN `content_hi`;
-- ALTER TABLE `branch_news` DROP COLUMN `title_hi`, DROP COLUMN `content_hi`;
-- ALTER TABLE `announcements` DROP COLUMN `text_hi`;
-- ALTER TABLE `exams` DROP COLUMN `title_hi`, DROP COLUMN `instructions_hi`;
-- ALTER TABLE `exam_questions` DROP COLUMN `question_text_hi`, DROP COLUMN `option_a_hi`, DROP COLUMN `option_b_hi`, DROP COLUMN `option_c_hi`, DROP COLUMN `option_d_hi`;
-- ALTER TABLE `practice_sets` DROP COLUMN `title_hi`, DROP COLUMN `description_hi`;
-- ALTER TABLE `practice_questions` DROP COLUMN `question_text_hi`, DROP COLUMN `option_a_hi`, DROP COLUMN `option_b_hi`, DROP COLUMN `option_c_hi`, DROP COLUMN `option_d_hi`;
-- ALTER TABLE `gallery` DROP COLUMN `title_hi`;
-- ALTER TABLE `toppers` DROP COLUMN `name_hi`, DROP COLUMN `course_name_hi`, DROP COLUMN `branch_name_hi`;
-- ALTER TABLE `branches` DROP COLUMN `city_hi`, DROP COLUMN `address_hi`;
-- ALTER TABLE `pages` DROP COLUMN `title_hi`, DROP COLUMN `content_hi`;
-- DELETE FROM `settings` WHERE `setting_key` LIKE '%_hi';
