74 lines
2.8 KiB
SQL
74 lines
2.8 KiB
SQL
-- Migration: Add users, assessments and school association
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('Super Admin', 'Admin', 'Teacher', 'Parent') NOT NULL,
|
|
school_id INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Add school_id to learners if it doesn't exist
|
|
SET @dbname = DATABASE();
|
|
SET @tablename = 'learners';
|
|
SET @columnname = 'school_id';
|
|
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 learners ADD COLUMN school_id INT, ADD FOREIGN KEY (school_id) REFERENCES schools(id)'
|
|
));
|
|
PREPARE stmt FROM @preparedStatement;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Add school_id to attendance if it doesn't exist
|
|
SET @tablename = 'attendance';
|
|
SET @columnname = 'school_id';
|
|
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 attendance ADD COLUMN school_id INT, ADD FOREIGN KEY (school_id) REFERENCES schools(id)'
|
|
));
|
|
PREPARE stmt FROM @preparedStatement;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
CREATE TABLE IF NOT EXISTS assessments (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
type ENUM('Test', 'Exam', 'Assignment', 'Project') NOT NULL,
|
|
total_marks INT NOT NULL,
|
|
grade VARCHAR(20) NOT NULL,
|
|
school_id INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS marks (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
assessment_id INT NOT NULL,
|
|
learner_id INT NOT NULL,
|
|
marks_obtained INT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (learner_id) REFERENCES learners(id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_mark (assessment_id, learner_id)
|
|
);
|
|
|
|
-- Seed a default user (password: password123)
|
|
-- Admin for 'Soweto High' (assuming id 1 from previous seeding if it exists)
|
|
INSERT IGNORE INTO users (email, password, role, school_id) VALUES
|
|
('admin@sowetohigh.edu.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 1),
|
|
('teacher@sowetohigh.edu.za', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Teacher', 1);
|
|
|
|
-- Assign existing learners to school 1 if they aren't assigned
|
|
UPDATE learners SET school_id = 1 WHERE school_id IS NULL;
|
|
UPDATE attendance SET school_id = 1 WHERE school_id IS NULL;
|