-- Create database
CREATE DATABASE IF NOT EXISTS rtdu_system;
USE rtdu_system;

-- Fees table
CREATE TABLE fees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    month_year DATE NOT NULL,
    description TEXT,
    due_date DATE NOT NULL,
    status ENUM('paid', 'unpaid') DEFAULT 'unpaid',
    payment_date TIMESTAMP NULL DEFAULT NULL,
    payment_method ENUM('cash', 'bank_transfer', 'online_payment') NULL,
    reference_number VARCHAR(100) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create sequence table for fee reference numbers
CREATE TABLE fee_sequence (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sequence_value INT NOT NULL DEFAULT 0
);

-- Insert initial sequence value
INSERT INTO fee_sequence (sequence_value) VALUES (0);

-- Create trigger to auto-generate reference number
DELIMITER //
CREATE TRIGGER before_fee_insert 
BEFORE INSERT ON fees
FOR EACH ROW 
BEGIN
    DECLARE next_val INT;
    UPDATE fee_sequence SET sequence_value = LAST_INSERT_ID(sequence_value + 1);
    SET next_val = LPAD(LAST_INSERT_ID(), 3, '0');
    SET NEW.reference_number = CONCAT('RTDU-FEE-', next_val);
END//
DELIMITER ;

-- Trips table
CREATE TABLE trips (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    vehicle_plate VARCHAR(20) NOT NULL,
    has_passengers BOOLEAN DEFAULT FALSE,
    current_country VARCHAR(100) NOT NULL,
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    start_date TIMESTAMP NOT NULL,
    end_date TIMESTAMP NULL,
    status ENUM('active', 'completed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Emergency reports table
CREATE TABLE emergency_reports (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    latitude DECIMAL(10,8) NOT NULL,
    longitude DECIMAL(11,8) NOT NULL,
    priority ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium',
    status ENUM('pending', 'in_progress', 'resolved') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Notifications table
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('fee', 'emergency', 'trip', 'system') NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Country entries table
CREATE TABLE country_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    country VARCHAR(100) NOT NULL,
    entry_date DATE NOT NULL,
    comments TEXT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Add index for faster lookups
CREATE INDEX idx_country_entries_user ON country_entries(user_id);
CREATE INDEX idx_country_entries_status ON country_entries(status);

-- Members table
CREATE TABLE members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    member_number VARCHAR(20) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    father_name VARCHAR(255) NOT NULL,
    mother_name VARCHAR(255) NOT NULL,
    gender ENUM('male', 'female', 'other') NOT NULL,
    date_of_birth DATE NOT NULL,
    place_of_birth VARCHAR(255) NOT NULL,
    sector VARCHAR(100) NOT NULL,
    national_id VARCHAR(50) NOT NULL,
    id_issue_place VARCHAR(255) NOT NULL,
    drivers_license VARCHAR(50),
    license_category VARCHAR(50),
    license_country VARCHAR(100),
    profile_picture VARCHAR(255),
    marital_status ENUM('single', 'married', 'divorced', 'widowed') NOT NULL,
    has_health_insurance BOOLEAN DEFAULT FALSE,
    has_social_security BOOLEAN DEFAULT FALSE,
    residence_status VARCHAR(100) NOT NULL,
    spouse_name VARCHAR(255),
    spouse_national_id VARCHAR(50),
    spouse_telephone VARCHAR(20),
    employer_name VARCHAR(255),
    employer_contact VARCHAR(100),
    has_employment_contract BOOLEAN DEFAULT FALSE,
    blood_type VARCHAR(10),
    has_chronic_illness BOOLEAN DEFAULT FALSE,
    chronic_illness_details TEXT,
    salary_through_bank BOOLEAN DEFAULT FALSE,
    education_level VARCHAR(100),
    employment_status BOOLEAN DEFAULT FALSE,
    unemployment_reason TEXT,
    work_experience TEXT,
    phone_number VARCHAR(20) NOT NULL,
    email VARCHAR(255) NOT NULL,
    emergency_contact_name VARCHAR(255) NOT NULL,
    emergency_contact_phone VARCHAR(20) NOT NULL,
    emergency_contact_address TEXT NOT NULL,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    admin_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Children table for members
CREATE TABLE member_children (
    id INT PRIMARY KEY AUTO_INCREMENT,
    member_id INT NOT NULL,
    child_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
);

-- Add indexes for faster lookups
CREATE INDEX idx_members_user ON members(user_id);
CREATE INDEX idx_members_status ON members(status);
CREATE INDEX idx_members_number ON members(member_number);