-- ============================================================================
-- Gmind Institute - Fix Gallery, Branches, and Toppers Display
-- ============================================================================
-- Run this SQL on your database (advaysin_gmind) via phpMyAdmin or MySQL CLI
-- This fixes gallery/branches not showing on front-end and ensures tables exist
-- ============================================================================

-- ============================================================================
-- 1. Fix Gallery Items — Ensure all gallery items have active = 1
--    (If items were added but not showing on the front page, this fixes it)
-- ============================================================================
UPDATE `gallery` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- ============================================================================
-- 2. Fix Branches — Ensure all branches have active = 1
--    (If branches were added but not showing on the front page, this fixes it)
-- ============================================================================
UPDATE `branches` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- ============================================================================
-- 3. Ensure the gallery table exists with correct structure
-- ============================================================================
CREATE TABLE IF NOT EXISTS `gallery` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL COMMENT 'Image title',
    `category` VARCHAR(100) DEFAULT NULL COMMENT 'Category e.g. Events, Campus, Classroom',
    `description` TEXT DEFAULT NULL COMMENT 'Brief description',
    `image` VARCHAR(500) NOT NULL COMMENT 'Path to gallery image',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display order (lower = first)',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_gallery_category` (`category`),
    INDEX `idx_gallery_active_sort` (`active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Photo gallery items';

-- ============================================================================
-- 4. Ensure the toppers table exists with correct structure
-- ============================================================================
CREATE TABLE IF NOT EXISTS `toppers` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Student name',
    `course_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to courses.id',
    `marks` VARCHAR(50) DEFAULT NULL COMMENT 'Marks obtained (text for flexibility)',
    `grade` VARCHAR(20) DEFAULT NULL COMMENT 'Grade e.g. A+, Distinction',
    `photo` VARCHAR(500) DEFAULT NULL COMMENT 'Path to student photo',
    `batch_year` YEAR DEFAULT NULL COMMENT 'Batch/academic year',
    `branch_id` BIGINT UNSIGNED DEFAULT NULL COMMENT 'FK to branches.id (nullable for institute-level toppers)',
    `sort_order` INT NOT NULL DEFAULT 0 COMMENT 'Display order (lower = first)',
    `active` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=visible, 0=hidden',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_toppers_course` (`course_id`),
    INDEX `idx_toppers_branch` (`branch_id`),
    INDEX `idx_toppers_batch_year` (`batch_year`),
    INDEX `idx_toppers_active_sort` (`active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Top-performing students';

-- ============================================================================
-- 5. Ensure the testimonials table has the photo column
-- ============================================================================
-- Add photo column if it doesn't exist (for admin-added testimonials with photo)
SET @dbname = DATABASE();
SET @tablename = 'testimonials';
SET @columnname = 'photo';
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 `testimonials` ADD COLUMN `photo` VARCHAR(500) DEFAULT NULL COMMENT ''Path to reviewer photo'' AFTER `review`'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- ============================================================================
-- 6. Verify gallery items are accessible (quick check query)
-- ============================================================================
-- Run this to verify: SELECT id, title, active, image FROM gallery ORDER BY id;

-- ============================================================================
-- End of Fix
-- ============================================================================
