-- ============================================================================
-- Gmind Institute - Fix All Display Issues
-- ============================================================================
-- Run this SQL on the server database to ensure all tables have the correct
-- columns for the admin CRUD and frontend display to work properly.
-- ============================================================================

-- Ensure gallery table has all required columns
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `title` VARCHAR(255) NOT NULL DEFAULT 'Gallery Image' AFTER `id`;
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `category` VARCHAR(100) DEFAULT NULL AFTER `title`;
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `description` TEXT DEFAULT NULL AFTER `category`;
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `image` VARCHAR(500) NOT NULL DEFAULT '' AFTER `description`;
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `sort_order` INT NOT NULL DEFAULT 0 AFTER `image`;
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `sort_order`;
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `active`;
ALTER TABLE `gallery` ADD COLUMN IF NOT EXISTS `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `created_at`;

-- Ensure toppers table has all required columns
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `name` VARCHAR(255) NOT NULL DEFAULT 'Student' AFTER `id`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `course_id` BIGINT UNSIGNED DEFAULT NULL AFTER `name`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `marks` VARCHAR(50) DEFAULT NULL AFTER `course_id`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `grade` VARCHAR(20) DEFAULT NULL AFTER `marks`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `photo` VARCHAR(500) DEFAULT NULL AFTER `grade`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `batch_year` YEAR DEFAULT NULL AFTER `photo`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `branch_id` BIGINT UNSIGNED DEFAULT NULL AFTER `batch_year`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `sort_order` INT NOT NULL DEFAULT 0 AFTER `branch_id`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `sort_order`;
ALTER TABLE `toppers` ADD COLUMN IF NOT EXISTS `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `active`;

-- Ensure testimonials table has photo column
ALTER TABLE `testimonials` ADD COLUMN IF NOT EXISTS `photo` VARCHAR(500) DEFAULT NULL AFTER `review`;

-- Ensure branches table has all required columns
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `name` VARCHAR(255) NOT NULL DEFAULT 'Branch' AFTER `id`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `city` VARCHAR(100) NOT NULL DEFAULT '' AFTER `name`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `state` VARCHAR(100) NOT NULL DEFAULT '' AFTER `city`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `address` TEXT NOT NULL AFTER `state`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `phone` VARCHAR(30) DEFAULT NULL AFTER `address`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `map_link` VARCHAR(500) DEFAULT NULL AFTER `phone`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `image` VARCHAR(500) DEFAULT NULL AFTER `map_link`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `image`;
ALTER TABLE `branches` ADD COLUMN IF NOT EXISTS `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `active`;

-- Ensure courses table has is_featured column
ALTER TABLE `courses` ADD COLUMN IF NOT EXISTS `is_featured` TINYINT(1) NOT NULL DEFAULT 0 AFTER `image`;

-- Set all existing courses as featured so they show on homepage
UPDATE `courses` SET `is_featured` = 1 WHERE `active` = 1 AND `is_featured` = 0;

-- Set all existing gallery items as active
UPDATE `gallery` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- Set all existing toppers as active
UPDATE `toppers` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- Set all existing branches as active
UPDATE `branches` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- Set all existing testimonials as approved
UPDATE `testimonials` SET `is_approved` = 1 WHERE `is_approved` = 0 OR `is_approved` IS NULL;

-- Ensure sliders table has all columns
ALTER TABLE `sliders` ADD COLUMN IF NOT EXISTS `title` VARCHAR(255) NOT NULL DEFAULT 'Slider' AFTER `id`;
ALTER TABLE `sliders` ADD COLUMN IF NOT EXISTS `subtitle` VARCHAR(500) DEFAULT NULL AFTER `title`;
ALTER TABLE `sliders` ADD COLUMN IF NOT EXISTS `image` VARCHAR(500) NOT NULL DEFAULT '' AFTER `subtitle`;
ALTER TABLE `sliders` ADD COLUMN IF NOT EXISTS `link` VARCHAR(500) DEFAULT NULL AFTER `image`;
ALTER TABLE `sliders` ADD COLUMN IF NOT EXISTS `sort_order` INT NOT NULL DEFAULT 0 AFTER `link`;
ALTER TABLE `sliders` ADD COLUMN IF NOT EXISTS `active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `sort_order`;

-- Ensure affiliations table has all columns
ALTER TABLE `affiliations` ADD COLUMN IF NOT EXISTS `name` VARCHAR(255) NOT NULL DEFAULT '' AFTER `id`;
ALTER TABLE `affiliations` ADD COLUMN IF NOT EXISTS `logo` VARCHAR(500) DEFAULT NULL AFTER `name`;
ALTER TABLE `affiliations` ADD COLUMN IF NOT EXISTS `website` VARCHAR(500) DEFAULT NULL AFTER `logo`;
ALTER TABLE `affiliations` ADD COLUMN IF NOT EXISTS `type` ENUM('affiliation','partner') NOT NULL DEFAULT 'affiliation' AFTER `website`;
ALTER TABLE `affiliations` ADD COLUMN IF NOT EXISTS `sort_order` INT NOT NULL DEFAULT 0 AFTER `type`;
ALTER TABLE `affiliations` ADD COLUMN IF NOT EXISTS `active` TINYINT(1) NOT NULL DEFAULT 1 AFTER `sort_order`;
