-- No CREATE DATABASE or USE – assume you've already selected the right database

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL
);

INSERT INTO users (username, password_hash) VALUES ('admin', MD5('admin123'))
ON DUPLICATE KEY UPDATE username = username;

CREATE TABLE IF NOT EXISTS buses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plate VARCHAR(20) UNIQUE NOT NULL,
    capacity INT NOT NULL,
    model VARCHAR(100),
    branch VARCHAR(5) NOT NULL,
    driver_name VARCHAR(100),
    driver_phone VARCHAR(20),
    location VARCHAR(100),
    zones JSON,
    route_id INT DEFAULT NULL,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    adm VARCHAR(50) UNIQUE NOT NULL,
    branch VARCHAR(5) NOT NULL,
    firstname VARCHAR(100) NOT NULL,
    lastname VARCHAR(100) NOT NULL,
    class VARCHAR(50),
    location VARCHAR(100),
    way ENUM('oneway','twoway') NOT NULL,
    fee DECIMAL(10,2) NOT NULL,
    parent VARCHAR(100),
    phone VARCHAR(20),
    bus_id INT,
    zones JSON,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    adm VARCHAR(50) NOT NULL,
    student_name VARCHAR(200),
    branch VARCHAR(5) NOT NULL,
    route_id INT,
    zones JSON,
    way ENUM('oneway','twoway'),
    amount DECIMAL(10,2) NOT NULL,
    date DATE NOT NULL,
    status ENUM('paid','pending','overdue') DEFAULT 'pending',
    phone VARCHAR(20)
);

CREATE TABLE IF NOT EXISTS reserved_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    adm_entered VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    phone VARCHAR(20),
    date DATE NOT NULL,
    status ENUM('reserved','matched') DEFAULT 'reserved',
    matched_to INT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS routes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    branch VARCHAR(5) NOT NULL,
    bus_id INT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS route_zone_fees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    route_id INT NOT NULL,
    zone INT NOT NULL,
    one_way DECIMAL(10,2) NOT NULL,
    two_way DECIMAL(10,2) NOT NULL,
    UNIQUE KEY unique_route_zone (route_id, zone)
);

CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    date DATE NOT NULL,
    status ENUM('present','absent','late') NOT NULL,
    UNIQUE KEY unique_student_date (student_id, date)
);

CREATE TABLE IF NOT EXISTS sms_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20),
    message TEXT,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20)
);