56 lines
2.1 KiB
SQL
56 lines
2.1 KiB
SQL
-- 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);
|