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

DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS attendance_logs;
DROP TABLE IF EXISTS visitors;
DROP TABLE IF EXISTS alerts;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS activity_logs;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    username VARCHAR(60) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'staff', 'security') NOT NULL DEFAULT 'staff',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admission_number VARCHAR(40) NOT NULL UNIQUE,
    first_name VARCHAR(80) NOT NULL,
    last_name VARCHAR(80) NOT NULL,
    grade VARCHAR(30) NOT NULL,
    class_name VARCHAR(30) NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL DEFAULT 'Other',
    parent_name VARCHAR(120) NOT NULL,
    parent_phone VARCHAR(30) NOT NULL,
    parent_email VARCHAR(120) NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    qr_code VARCHAR(80) NOT NULL UNIQUE,
    photo_path VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    INDEX idx_students_name (last_name, first_name),
    INDEX idx_students_grade_class (grade, class_name)
) ENGINE=InnoDB;

CREATE TABLE attendance_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NOT NULL,
    event_type ENUM('IN', 'OUT') NOT NULL,
    event_time DATETIME NOT NULL,
    device_name VARCHAR(80) NOT NULL,
    gate_name VARCHAR(80) NOT NULL,
    operator_user_id INT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_attendance_student_time (student_id, event_time),
    INDEX idx_attendance_event_time (event_time),
    CONSTRAINT fk_attendance_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_operator FOREIGN KEY (operator_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE visitors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    id_number VARCHAR(30) NOT NULL,
    phone VARCHAR(30) NOT NULL,
    purpose VARCHAR(150) NOT NULL,
    host_person VARCHAR(120) NOT NULL,
    vehicle_registration VARCHAR(20) NULL,
    photo_path VARCHAR(255) NULL,
    time_in DATETIME NOT NULL,
    time_out DATETIME NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    INDEX idx_visitors_time_in (time_in),
    INDEX idx_visitors_current (time_out),
    CONSTRAINT fk_visitors_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_visitors_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE alerts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(60) NOT NULL,
    message TEXT NOT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    status ENUM('active', 'resolved') NOT NULL DEFAULT 'active',
    INDEX idx_alert_status_time (status, created_at),
    CONSTRAINT fk_alerts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id INT UNSIGNED NULL,
    channel VARCHAR(30) NOT NULL,
    recipient VARCHAR(120) NOT NULL,
    message TEXT NOT NULL,
    delivery_status VARCHAR(30) NOT NULL,
    provider_response VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_notifications_student (student_id),
    INDEX idx_notifications_created_at (created_at),
    CONSTRAINT fk_notifications_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    description VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL,
    INDEX idx_activity_created_at (created_at),
    INDEX idx_activity_user_action (user_id, action),
    CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO users (full_name, username, password_hash, role, status, created_at) VALUES
('System Administrator', 'admin', '$2y$10$pBkhk0Lrz5S5ce9w2Jp2Gu2mRbpe4h1BUDV2j/Ok3ns8lfTLqtLqy', 'admin', 'active', NOW()),
('Thandi Nkosi', 'staff1', '$2y$10$pBkhk0Lrz5S5ce9w2Jp2Gu2mRbpe4h1BUDV2j/Ok3ns8lfTLqtLqy', 'staff', 'active', NOW()),
('David Mokoena', 'security1', '$2y$10$pBkhk0Lrz5S5ce9w2Jp2Gu2mRbpe4h1BUDV2j/Ok3ns8lfTLqtLqy', 'security', 'active', NOW());

INSERT INTO students (
    admission_number, first_name, last_name, grade, class_name, gender,
    parent_name, parent_phone, parent_email, status, qr_code, created_at
) VALUES
('ADM-1001', 'Lethabo', 'Molefe', 'Grade 5', '5A', 'Female', 'Palesa Molefe', '+27821234567', 'palesa.molefe@example.com', 'active', 'SS-ALPHA101', NOW()),
('ADM-1002', 'Sipho', 'Dlamini', 'Grade 7', '7B', 'Male', 'Nomsa Dlamini', '+27827654321', 'nomsa.dlamini@example.com', 'active', 'SS-BRAVO202', NOW()),
('ADM-1003', 'Ava', 'Naidoo', 'Grade 3', '3C', 'Female', 'Kiran Naidoo', '+27829881234', 'kiran.naidoo@example.com', 'active', 'SS-CHARLIE303', NOW()),
('ADM-1004', 'Neo', 'Mabena', 'Grade 10', '10A', 'Male', 'Lerato Mabena', '+27825550111', 'lerato.mabena@example.com', 'inactive', 'SS-DELTA404', NOW());

INSERT INTO attendance_logs (student_id, event_type, event_time, device_name, gate_name, operator_user_id, created_at) VALUES
(1, 'IN', CONCAT(CURDATE(), ' 07:12:00'), 'Scanner A', 'Main Gate', 3, NOW()),
(2, 'IN', CONCAT(CURDATE(), ' 07:33:00'), 'Scanner A', 'Main Gate', 3, NOW()),
(3, 'IN', CONCAT(CURDATE(), ' 07:56:00'), 'Scanner B', 'North Gate', 3, NOW()),
(2, 'OUT', CONCAT(CURDATE(), ' 14:05:00'), 'Scanner A', 'Main Gate', 3, NOW()),
(1, 'OUT', DATE_SUB(NOW(), INTERVAL 1 DAY), 'Scanner A', 'Main Gate', 3, NOW());

INSERT INTO visitors (full_name, id_number, phone, purpose, host_person, vehicle_registration, time_in, time_out, created_by, updated_by) VALUES
('Martha Jacobs', '8201015800088', '+27831230000', 'Uniform supplier meeting', 'Mr Khumalo', 'CA 123456', CONCAT(CURDATE(), ' 09:10:00'), NULL, 2, NULL),
('Eugene Smith', '7504025100087', '+27839995555', 'Parent meeting', 'Ms Daniels', NULL, DATE_SUB(NOW(), INTERVAL 1 DAY), DATE_SUB(NOW(), INTERVAL 1 DAY - INTERVAL 1 HOUR), 2, 2);

INSERT INTO alerts (type, message, created_by, created_at, status) VALUES
('Medical', 'Learner support requested near Grade 5 block.', 2, DATE_SUB(NOW(), INTERVAL 2 HOUR), 'active'),
('Security', 'Visitor escort required at the administration office.', 3, DATE_SUB(NOW(), INTERVAL 1 DAY), 'resolved');

INSERT INTO notifications (student_id, channel, recipient, message, delivery_status, provider_response, created_at) VALUES
(1, 'log', '+27821234567', 'Your child Lethabo Molefe has arrived at school at 07:12.', 'logged', 'Stored in database log.', NOW()),
(2, 'sms', '+27827654321', 'Your child Sipho Dlamini has left school at 14:05.', 'pending', 'Connect Twilio, BulkSMS, or another SMS gateway here.', NOW());

INSERT INTO settings (setting_key, setting_value, updated_at) VALUES
('school_name', 'SafeSchool Demo Academy', NOW()),
('school_logo', '', NOW()),
('school_address', '123 Ubuntu Avenue, Johannesburg, 2001', NOW()),
('contact_number', '+27 11 555 0100', NOW()),
('notification_sms_enabled', '0', NOW()),
('notification_whatsapp_enabled', '0', NOW()),
('default_gate_name', 'Main Gate', NOW()),
('footer_credit', 'SafeSchool by CodeForge Labs', NOW());

INSERT INTO activity_logs (user_id, action, description, ip_address, user_agent, created_at) VALUES
(1, 'login', 'Default administrator account seeded.', '127.0.0.1', 'Seeder', NOW()),
(2, 'student.create', 'Sample student data added during installation.', '127.0.0.1', 'Seeder', NOW()),
(3, 'scan', 'Sample scan events added during installation.', '127.0.0.1', 'Seeder', NOW());