-- 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;