-- Migration: Add Gamification (Badges) and School Collaboration (Public resources)
CREATE TABLE IF NOT EXISTS badges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(100) DEFAULT 'bi-award',
    threshold_percent DECIMAL(5,2) DEFAULT NULL, -- Optional mark threshold
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS learner_badges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    learner_id INT NOT NULL,
    badge_id INT NOT NULL,
    awarded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE CASCADE,
    FOREIGN KEY (badge_id) REFERENCES badges(id) ON DELETE CASCADE
);

-- Add is_public to resources and forum_posts for cross-school collaboration
SET @dbname = DATABASE();
SET @tablename = 'resources';
SET @columnname = 'is_public';
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 resources ADD COLUMN is_public BOOLEAN DEFAULT 0'
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @tablename = 'forum_posts';
SET @columnname = 'is_public';
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 forum_posts ADD COLUMN is_public BOOLEAN DEFAULT 0'
));
PREPARE stmt FROM @preparedStatement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Seed some initial badges
INSERT IGNORE INTO badges (name, description, icon, threshold_percent) VALUES 
('Academic Star', 'Achieved an average of over 80% in assessments.', 'bi-star-fill', 80.00),
('Consistent Performer', 'Achieved an average of over 60% in assessments.', 'bi-check-circle-fill', 60.00),
('Math Whiz', 'Top performer in Mathematics assessments.', 'bi-calculator', NULL),
('Science Explorer', 'Excellence in Science projects.', 'bi-microscope', NULL);