-- ============================================================================
-- Gmind Institute - Fix Missing Tables & Settings
-- ============================================================================
-- Run this SQL on your database (advaysin_gmind) via phpMyAdmin or MySQL CLI
-- This adds the gallery table and missing settings that the frontend needs
-- ============================================================================

-- ============================================================================
-- 1. Gallery Table - Photo gallery for homepage and gallery page
-- ============================================================================
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';

-- ============================================================================
-- 2. Missing Settings - Required by HomeController and views
-- ============================================================================
-- Uses ON DUPLICATE KEY UPDATE so it's safe to run multiple times

-- Site logo path (for header logo display)
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('site_logo', 'uploads/logo.png')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Affiliation page logo
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('affiliation_logo', '')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Homepage stat: placement rate
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('stat_placement_rate', '85')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Homepage stat: total students
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('stat_total_students', '10000')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Homepage stat: total courses
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('stat_total_courses', '25')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Homepage stat: total branches
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('stat_total_branches', '50')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Homepage stat: years of experience
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('stat_years_experience', '15')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Contact phone
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('contact_phone', '9305640290')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Contact email
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('contact_email', 'info@gmind.in')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Address
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('address', '123 Education Street, New Delhi, India')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Site phone (used by header/footer)
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('site_phone', '9305640290')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- Site address (used by header/footer)
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES ('site_address', '123 Education Street, New Delhi, India')
ON DUPLICATE KEY UPDATE `setting_value` = `setting_value`;

-- ============================================================================
-- 3. Sample Gallery Data (optional - makes homepage look complete)
-- ============================================================================
INSERT INTO `gallery` (`title`, `category`, `description`, `image`, `sort_order`, `active`) VALUES
    ('Computer Lab', 'Campus', 'State-of-the-art computer lab with modern workstations', 'uploads/gallery/computer-lab.jpg', 1, 1),
    ('Classroom Training', 'Classroom', 'Interactive classroom sessions with experienced faculty', 'uploads/gallery/classroom-training.jpg', 2, 1),
    ('Annual Day Celebration', 'Events', 'Students performing at the annual day event', 'uploads/gallery/annual-day.jpg', 3, 1),
    ('Placement Drive', 'Events', 'Campus placement drive by leading IT companies', 'uploads/gallery/placement-drive.jpg', 4, 1),
    ('Library', 'Campus', 'Well-stocked library with digital resources', 'uploads/gallery/library.jpg', 5, 1),
    ('Workshop on AI', 'Events', 'Hands-on workshop on Artificial Intelligence and Machine Learning', 'uploads/gallery/ai-workshop.jpg', 6, 1),
    ('Student Projects', 'Classroom', 'Students showcasing their final semester projects', 'uploads/gallery/student-projects.jpg', 7, 1),
    ('Graduation Ceremony', 'Events', 'Proud moments at the graduation ceremony', 'uploads/gallery/graduation.jpg', 8, 1);

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