-- School Arrival/Departure Notification System
-- Import this in phpMyAdmin (cPanel) before using the app

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','staff') NOT NULL DEFAULT 'staff',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admission_no VARCHAR(30) NOT NULL UNIQUE,
    barcode VARCHAR(50) UNIQUE,          -- printed on student ID card
    first_name VARCHAR(60) NOT NULL,
    last_name VARCHAR(60) NOT NULL,
    class VARCHAR(30) NOT NULL,
    photo VARCHAR(255) DEFAULT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS parents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    relationship VARCHAR(30) DEFAULT 'Parent',
    phone VARCHAR(20) NOT NULL,          -- format: 234XXXXXXXXXX (no leading 0/+)
    email VARCHAR(120) DEFAULT NULL,
    receive_sms TINYINT(1) DEFAULT 1,
    receive_email TINYINT(1) DEFAULT 1,
    receive_whatsapp TINYINT(1) DEFAULT 1,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS attendance_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    event_type ENUM('IN','OUT') NOT NULL,
    event_time DATETIME NOT NULL,
    recorded_by INT DEFAULT NULL,        -- users.id
    method ENUM('scan','manual') DEFAULT 'manual',
    sms_status VARCHAR(20) DEFAULT NULL, -- sent / failed / skipped
    email_status VARCHAR(20) DEFAULT NULL,
    whatsapp_status VARCHAR(20) DEFAULT NULL,
    photo VARCHAR(255) DEFAULT NULL,     -- check-in/out snapshot, relative path
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(60) PRIMARY KEY,
    setting_value VARCHAR(255) NOT NULL
);

-- Default settings (edit values after import)
INSERT INTO settings (setting_key, setting_value) VALUES
('school_name', 'Your School Name'),
('termii_api_key', 'PASTE_TERMII_API_KEY'),
('termii_sender_id', 'SCHOOL'),
('termii_whatsapp_number_id', 'PASTE_TERMII_WHATSAPP_NUMBER_ID'),
('smtp_host', 'smtp.hostinger.com'),
('smtp_port', '465'),
('smtp_username', 'noreply@yourschool.com'),
('smtp_password', 'PASTE_EMAIL_PASSWORD'),
('smtp_from_name', 'Your School Name')
ON DUPLICATE KEY UPDATE setting_key=setting_key;

-- Default admin login: username = admin, password = admin123
-- (change immediately after first login)
INSERT INTO users (full_name, username, password, role) VALUES
('Administrator', 'admin', '$2b$12$.Jc/PBCCvQ2Pe9OLk8aAQOGrtBnrLvWQoGzcpXtD.S.ga7ILHwQqS', 'admin')
ON DUPLICATE KEY UPDATE username=username;