-- ============================================================================
-- Gmind Institute - Comprehensive Database Fix (Run on Server)
-- ============================================================================
-- Run this SQL on your database (advaysin_gmind) via phpMyAdmin or MySQL CLI
-- This fixes: gallery not showing, branch not showing, toppers table,
--             testimonials photo column, and ensures all existing data is active
-- ============================================================================

-- ============================================================================
-- 1. Fix Gallery Items — Set all existing gallery items to active = 1
--    (Gallery items added via admin were not showing on front page)
-- ============================================================================
UPDATE `gallery` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- ============================================================================
-- 2. Fix Branches — Set all existing branches to active = 1
--    (Branches added via admin were not showing on front page)
-- ============================================================================
UPDATE `branches` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- ============================================================================
-- 3. Fix Toppers — Set all existing toppers to active = 1
-- ============================================================================
UPDATE `toppers` SET `active` = 1 WHERE `active` = 0 OR `active` IS NULL;

-- ============================================================================
-- 4. 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';

-- ============================================================================
-- 5. Ensure the toppers table exists with correct structure
--    (Without foreign keys to avoid constraint issues on existing data)
-- ============================================================================
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';

-- ============================================================================
-- 6. Ensure the testimonials table has the photo column
--    (Adding photo upload support for admin-added testimonials)
-- ============================================================================
-- Check if photo column exists, add if missing
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;

-- ============================================================================
-- 7. Ensure the testimonials table has the is_approved column
--    (Needed for testimonial approval workflow)
-- ============================================================================
SET @columnname2 = 'is_approved';
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      TABLE_SCHEMA = @dbname
      AND TABLE_NAME = @tablename
      AND COLUMN_NAME = @columnname2
  ) > 0,
  'SELECT 1',
  'ALTER TABLE `testimonials` ADD COLUMN `is_approved` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''1=approved for display'' AFTER `photo`'
));
PREPARE alterIfNotExists2 FROM @preparedStatement2;
EXECUTE alterIfNotExists2;
DEALLOCATE PREPARE alterIfNotExists2;

-- ============================================================================
-- 8. Ensure the announcements table exists
-- ============================================================================
CREATE TABLE IF NOT EXISTS `announcements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` VARCHAR(1000) NOT NULL COMMENT 'Announcement text',
    `link` VARCHAR(500) DEFAULT NULL COMMENT 'Optional URL for more details',
    `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_announcements_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Site-wide announcements';

-- ============================================================================
-- 9. Ensure the affiliations table exists
-- ============================================================================
CREATE TABLE IF NOT EXISTS `affiliations` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Organization name',
    `logo` VARCHAR(500) DEFAULT NULL COMMENT 'Path to organization logo',
    `website` VARCHAR(500) DEFAULT NULL COMMENT 'Organization website URL',
    `type` ENUM('affiliation','partner') NOT NULL DEFAULT 'affiliation' COMMENT 'Type of relationship',
    `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_affiliations_type_sort` (`type`, `sort_order`),
    INDEX `idx_affiliations_active` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Affiliations and partners';

-- ============================================================================
-- 10. Ensure the branch_news table exists
-- ============================================================================
CREATE TABLE IF NOT EXISTS `branch_news` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `branch_id` BIGINT UNSIGNED NOT NULL COMMENT 'FK to branches.id',
    `title` VARCHAR(255) NOT NULL COMMENT 'News headline',
    `slug` VARCHAR(255) NOT NULL COMMENT 'URL-friendly slug (unique)',
    `content` TEXT NOT NULL COMMENT 'News article body',
    `image` VARCHAR(500) DEFAULT NULL COMMENT 'Path to featured image',
    `published_at` DATETIME DEFAULT NULL COMMENT 'Scheduled publish timestamp',
    `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`),
    UNIQUE INDEX `uk_branch_news_slug` (`slug`),
    INDEX `idx_branch_news_branch` (`branch_id`),
    INDEX `idx_branch_news_active_published` (`active`, `published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='News articles per branch';

-- ============================================================================
-- 11. Verify data - Quick check queries (run manually if needed)
-- ============================================================================
-- SELECT id, title, active, image FROM gallery ORDER BY id;
-- SELECT id, name, city, active FROM branches ORDER BY id;
-- SELECT id, name, active FROM toppers ORDER BY id;
-- SELECT id, name, is_approved, photo FROM testimonials ORDER BY id;

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