-- Terminal Report Generator Database Setup

-- Run this SQL to create required tables



USE school_finance;



-- Subjects Table

CREATE TABLE IF NOT EXISTS subjects (

    id INT AUTO_INCREMENT PRIMARY KEY,

    subject_name VARCHAR(100) NOT NULL,

    subject_code VARCHAR(20), 

    active ENUM('yes', 'no') DEFAULT 'yes',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Insert default subjects

INSERT INTO subjects (subject_name, subject_code) VALUES

('English Language', 'ENG'),

('Our Wold Our People', 'OWOP'),

('Intergrated Science', 'NAT'),

('Mathematics', 'MATH'),

('Social Studied', 'HIST'),

('I.C.T.', 'ICT'),

('Creative Arts', 'ART'),

('French Language', 'FREN'),

('R.M.E.', 'RME');



-- Scores Table

CREATE TABLE IF NOT EXISTS scores (

    id INT AUTO_INCREMENT PRIMARY KEY,

    student_id INT NOT NULL,

    subject_id INT NOT NULL,

    class_score DECIMAL(5,2) DEFAULT 0,

    exam_score DECIMAL(5,2) DEFAULT 0,

    total_score DECIMAL(5,2) DEFAULT 0,

    grade VARCHAR(2) DEFAULT 'W',

    remark VARCHAR(50) DEFAULT 'Weak',

    term VARCHAR(20) NOT NULL,

    academic_year VARCHAR(20) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,

    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,

    UNIQUE KEY unique_score (student_id, subject_id, term, academic_year)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Student Report Details Table (for attendance, conduct, remarks)

CREATE TABLE IF NOT EXISTS report_details (

    id INT AUTO_INCREMENT PRIMARY KEY,

    student_id INT NOT NULL,

    term VARCHAR(20) NOT NULL,

    academic_year VARCHAR(20) NOT NULL,

    attendance_out_of INT DEFAULT 0,

    promoted_to VARCHAR(50),

    conduct TEXT,

    interest TEXT,

    teacher_remark TEXT,

    director_signature VARCHAR(255),

    headteacher_signature VARCHAR(255),

    class_teacher_signature VARCHAR(255),

    school_stamp VARCHAR(255),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,

    UNIQUE KEY unique_report (student_id, term, academic_year)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- Add school_logo to settings if not exists

INSERT INTO settings (setting_key, setting_value) 

SELECT 'school_logo', 'uploads/logos/logo.png'

WHERE NOT EXISTS (SELECT 1 FROM settings WHERE setting_key = 'school_logo');



-- Add default dates to settings

INSERT INTO settings (setting_key, setting_value) 

SELECT 'term_dates', 'First Term: Jan-Mar|Second Term: May-Aug|Third Term: Sept-Dec'

WHERE NOT EXISTS (SELECT 1 FROM settings WHERE setting_key = 'term_dates');



