-- EduSmart SMS Database Schema
-- Run this in phpMyAdmin or MySQL CLI

CREATE DATABASE IF NOT EXISTS edusmart_sms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE edusmart_sms;

CREATE TABLE schools (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    address VARCHAR(255),
    logo VARCHAR(255),
    motto VARCHAR(255),
    county VARCHAR(100),
    type ENUM('primary','secondary','mixed') DEFAULT 'secondary',
    is_active TINYINT(1) DEFAULT 1,
    settings JSON,
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(20),
    avatar VARCHAR(255),
    password VARCHAR(255) NOT NULL,
    role ENUM('super-admin','admin','teacher','student','parent','accountant','deputy','librarian') NOT NULL,
    two_fa_enabled TINYINT(1) DEFAULT 0,
    two_fa_code VARCHAR(255),
    two_fa_expires_at DATETIME,
    is_active TINYINT(1) DEFAULT 1,
    last_login_at DATETIME,
    last_login_ip VARCHAR(45),
    created_at DATETIME,
    updated_at DATETIME,
    INDEX idx_school (school_id),
    INDEX idx_email (email)
);

CREATE TABLE academic_years (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    start_date DATE,
    end_date DATE,
    is_current TINYINT(1) DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE terms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    academic_year_id INT NOT NULL,
    name VARCHAR(20) NOT NULL,
    start_date DATE,
    end_date DATE,
    is_current TINYINT(1) DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON DELETE CASCADE
);

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10),
    head_of_dept INT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    level INT NOT NULL,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE streams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    class_teacher_id INT,
    capacity INT DEFAULT 45,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
);

CREATE TABLE subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    department_id INT,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10),
    is_compulsory TINYINT(1) DEFAULT 1,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    school_id INT NOT NULL,
    admission_no VARCHAR(30) UNIQUE NOT NULL,
    stream_id INT,
    dob DATE,
    gender ENUM('male','female'),
    kcpe_index VARCHAR(20),
    kcpe_marks INT,
    admission_date DATE,
    status ENUM('active','transferred','graduated','suspended') DEFAULT 'active',
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE SET NULL
);

CREATE TABLE teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    school_id INT NOT NULL,
    tsc_no VARCHAR(20),
    employee_no VARCHAR(20),
    department_id INT,
    joining_date DATE,
    employment_type ENUM('permanent','contract','bom') DEFAULT 'permanent',
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE parents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    occupation VARCHAR(100),
    relationship VARCHAR(30) DEFAULT 'parent',
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE parent_student (
    parent_id INT NOT NULL,
    student_id INT NOT NULL,
    PRIMARY KEY (parent_id, student_id),
    FOREIGN KEY (parent_id) REFERENCES parents(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE TABLE teacher_subject_streams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    subject_id INT NOT NULL,
    stream_id INT NOT NULL,
    term_id INT NOT NULL,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    FOREIGN KEY (term_id) REFERENCES terms(id) ON DELETE CASCADE
);

CREATE TABLE timetables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stream_id INT NOT NULL,
    subject_id INT NOT NULL,
    teacher_id INT NOT NULL,
    term_id INT NOT NULL,
    day_of_week TINYINT NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    room VARCHAR(30),
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
);

CREATE TABLE exams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    term_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('formative','summative','project','portfolio','endterm') DEFAULT 'summative',
    weight DECIMAL(5,2) DEFAULT 100,
    start_date DATE,
    end_date DATE,
    is_published TINYINT(1) DEFAULT 0,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (term_id) REFERENCES terms(id) ON DELETE CASCADE
);

CREATE TABLE marks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    exam_id INT NOT NULL,
    subject_id INT NOT NULL,
    score DECIMAL(5,2),
    out_of DECIMAL(5,2) DEFAULT 100,
    grade VARCHAR(5),
    points TINYINT,
    remarks VARCHAR(50),
    entered_by INT,
    created_at DATETIME,
    updated_at DATETIME,
    UNIQUE KEY uq_mark (student_id, exam_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE
);

CREATE TABLE attendances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    stream_id INT NOT NULL,
    date DATE NOT NULL,
    status ENUM('present','absent','late','excused') DEFAULT 'present',
    reason VARCHAR(255),
    recorded_by INT,
    created_at DATETIME,
    updated_at DATETIME,
    UNIQUE KEY uq_attendance (student_id, date),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE TABLE fee_structures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    term_id INT NOT NULL,
    class_id INT,
    name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    is_mandatory TINYINT(1) DEFAULT 1,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

CREATE TABLE fee_invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    term_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    status ENUM('unpaid','partial','paid') DEFAULT 'unpaid',
    created_at DATETIME,
    updated_at DATETIME,
    UNIQUE KEY uq_invoice (student_id, term_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    receipt_no VARCHAR(30) UNIQUE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('cash','mpesa','bank','cheque') DEFAULT 'cash',
    transaction_ref VARCHAR(50),
    bank_name VARCHAR(100),
    payment_date DATE NOT NULL,
    received_by INT,
    notes TEXT,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (invoice_id) REFERENCES fee_invoices(id) ON DELETE CASCADE
);

CREATE TABLE assignments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    subject_id INT NOT NULL,
    stream_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    file_path VARCHAR(255),
    due_date DATE NOT NULL,
    total_marks DECIMAL(5,2) DEFAULT 10,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
);

CREATE TABLE notifications_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT,
    type VARCHAR(20),
    recipient VARCHAR(100),
    message TEXT,
    status ENUM('pending','sent','failed') DEFAULT 'pending',
    provider VARCHAR(50),
    sent_by INT,
    group_label VARCHAR(100),
    created_at DATETIME,
    updated_at DATETIME
);

CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    school_id INT,
    action VARCHAR(100) NOT NULL,
    model VARCHAR(50),
    model_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent VARCHAR(255),
    created_at DATETIME,
    updated_at DATETIME,
    INDEX idx_user_date (user_id, created_at)
);

CREATE TABLE login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    successful TINYINT(1) DEFAULT 0,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login (email, ip_address, attempted_at)
);

-- Seed: Default school
INSERT INTO schools (name, code, email, phone, address, county, type, is_active, created_at, updated_at)
VALUES ('EduSmart High School','SCH001','info@edusmart.ac.ke','+254700000000','Nairobi, Kenya','Nairobi','secondary',1,NOW(),NOW());

-- Seed: Academic year & term
INSERT INTO academic_years (school_id, name, start_date, end_date, is_current, created_at, updated_at)
VALUES (1,'2025','2025-01-06','2025-11-28',1,NOW(),NOW());

INSERT INTO terms (academic_year_id, name, start_date, end_date, is_current, created_at, updated_at)
VALUES (1,'Term 1','2025-01-06','2025-04-04',1,NOW(),NOW());

-- Seed: Classes
INSERT INTO classes (school_id, name, level, created_at, updated_at) VALUES
(1,'Form 1',1,NOW(),NOW()),(1,'Form 2',2,NOW(),NOW()),
(1,'Form 3',3,NOW(),NOW()),(1,'Form 4',4,NOW(),NOW());

-- Seed: Streams
INSERT INTO streams (class_id, name, capacity, created_at, updated_at) VALUES
(1,'North',45,NOW(),NOW()),(1,'South',45,NOW(),NOW()),
(2,'North',45,NOW(),NOW()),(2,'South',45,NOW(),NOW()),
(3,'North',45,NOW(),NOW()),(3,'South',45,NOW(),NOW()),
(4,'North',45,NOW(),NOW()),(4,'South',45,NOW(),NOW());

-- Seed: Departments
INSERT INTO departments (school_id, name, code, created_at, updated_at) VALUES
(1,'Sciences','SCI',NOW(),NOW()),(1,'Languages','LANG',NOW(),NOW()),
(1,'Humanities','HUM',NOW(),NOW()),(1,'Technical','TECH',NOW(),NOW());

-- Seed: Subjects
INSERT INTO subjects (school_id, department_id, name, code, is_compulsory, created_at, updated_at) VALUES
(1,1,'Mathematics','MATH',1,NOW(),NOW()),(1,1,'Physics','PHY',0,NOW(),NOW()),
(1,1,'Chemistry','CHEM',0,NOW(),NOW()),(1,1,'Biology','BIO',0,NOW(),NOW()),
(1,2,'English','ENG',1,NOW(),NOW()),(1,2,'Kiswahili','KIS',1,NOW(),NOW()),
(1,3,'History','HIST',0,NOW(),NOW()),(1,3,'Geography','GEO',0,NOW(),NOW()),
(1,3,'CRE','CRE',0,NOW(),NOW()),(1,4,'Computer Studies','COMP',0,NOW(),NOW()),
(1,4,'Business Studies','BST',0,NOW(),NOW());

-- Seed: Super Admin (password: Admin@1234)
INSERT INTO users (school_id, name, email, phone, password, role, is_active, created_at, updated_at)
VALUES (1,'Super Admin','superadmin@edusmart.ac.ke','+254700000001','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','super-admin',1,NOW(),NOW());

-- Competencies table
CREATE TABLE IF NOT EXISTS sms_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('fee','result','attendance','announcement','alert','general') DEFAULT 'general',
    title VARCHAR(200) NOT NULL,
    body TEXT NOT NULL,
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

-- Seed default templates
INSERT IGNORE INTO sms_templates (school_id, name, type, title, body, created_at, updated_at) VALUES
(1,'Fee Reminder','fee','Fee Payment Reminder','Dear Parent, this is a reminder that school fees for {term} are due. Please ensure payment is made promptly to avoid disruption of your child\'s learning. Thank you.',NOW(),NOW()),
(1,'Fee Overdue','fee','Overdue Fee Notice','Dear Parent, fees for {student} ({term}) are overdue. Outstanding balance: KES {balance}. Please make payment immediately. Contact the school bursar for assistance.',NOW(),NOW()),
(1,'Payment Received','fee','Payment Confirmation','Dear Parent, we confirm receipt of KES {amount} for {student} ({term}). Receipt No: {receipt}. Balance: KES {balance}. Thank you.',NOW(),NOW()),
(1,'Results Ready','result','Exam Results Available','Dear Parent, {student}\'s results for {exam} are now available. Log in to the parent portal to view the full report card.',NOW(),NOW()),
(1,'Absent Alert','attendance','Attendance Alert','Dear Parent, {student} was marked absent today {date}. Please contact the school if this was unplanned. Thank you.',NOW(),NOW()),
(1,'General Announcement','announcement','School Announcement','Dear Parent/Guardian, {message} For more information contact the school office. Thank you.',NOW(),NOW()),
(1,'Meeting Notice','announcement','Parent Meeting Notice','Dear Parent, you are invited to a parent meeting on {date} at {time}. Your attendance is highly appreciated. Thank you - School Administration.',NOW(),NOW());
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(20) NOT NULL,
    description TEXT,
    created_at DATETIME,
    updated_at DATETIME
);

-- Competency ratings table
CREATE TABLE IF NOT EXISTS competency_ratings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    competency_id INT NOT NULL,
    term_id INT NOT NULL,
    rating ENUM('EE','ME','AE','BE') NOT NULL,
    remarks TEXT,
    rated_by INT,
    created_at DATETIME,
    updated_at DATETIME,
    UNIQUE KEY uq_rating (student_id, competency_id, term_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

-- Seed: CBC Key Competencies
INSERT IGNORE INTO competencies (name, code, created_at, updated_at) VALUES
('Communication and Collaboration','CC',NOW(),NOW()),
('Critical Thinking and Problem Solving','CT',NOW(),NOW()),
('Creativity and Imagination','CI',NOW(),NOW()),
('Citizenship','CZ',NOW(),NOW()),
('Digital Literacy','DL',NOW(),NOW()),
('Learning to Learn','LL',NOW(),NOW()),
('Self-Efficacy','SE',NOW(),NOW());
