-- Dozar & Tripper Management System
-- Database Setup Script for Kathmandu Municipality Environment Department

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

-- ============================================
-- TABLES
-- ============================================

-- 1. Projects/Locations Table
CREATE TABLE IF NOT EXISTS projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_name VARCHAR(100) NOT NULL,
    project_code VARCHAR(20) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Drivers Table
CREATE TABLE IF NOT EXISTS drivers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20),
    address VARCHAR(255),
    license_number VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3b. Vehicle Types Table
CREATE TABLE IF NOT EXISTS vehicle_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 3. Vehicles Table
CREATE TABLE IF NOT EXISTS vehicles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vehicle_number VARCHAR(20) UNIQUE NOT NULL,
    vehicle_type_id INT,
    vehicle_type VARCHAR(50) NOT NULL,
    entity_type ENUM('kmc','private','other') NOT NULL DEFAULT 'kmc',
    vehicle_model VARCHAR(50),
    capacity VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id)
);

-- 4. Deployed From Table (Source Entity)
CREATE TABLE IF NOT EXISTS deployed_from (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entity_name VARCHAR(100) NOT NULL,
    entity_type ENUM('kmc', 'private', 'other') DEFAULT 'other',
    contact_info VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 5. Groups Table
CREATE TABLE IF NOT EXISTS groups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    group_name VARCHAR(100) NOT NULL,
    description VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 6. Users Table
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'collector', 'viewer') NOT NULL,
    collector_access ENUM('dozar_only', 'tripper_loading_only', 'tripper_unloading_only', 'all_modules') DEFAULT 'dozar_only',
    assigned_projects VARCHAR(255),
    can_delete TINYINT(1) DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 7. Dozar Records Table
CREATE TABLE IF NOT EXISTS dozar_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    deployed_from_id INT NOT NULL,
    vehicle_id INT NOT NULL,
    driver_id INT NOT NULL,
    created_date DATE NOT NULL,
    collector_name VARCHAR(100) NOT NULL,
    work_location VARCHAR(200) NOT NULL,
    work_length_hours DECIMAL(10,2) NOT NULL,
    collector_entered_address VARCHAR(255),
    gps_latitude DECIMAL(10,8),
    gps_longitude DECIMAL(11,8),
    gps_address VARCHAR(255),
    gps_captured_at TIMESTAMP NULL,
    start_odometer DECIMAL(10,2) NOT NULL,
    end_odometer DECIMAL(10,2) DEFAULT NULL,
    start_time TIME NOT NULL,
    end_time TIME DEFAULT NULL,
    entry_location VARCHAR(200),
    group_id INT,
    remarks TEXT,
    photo_path VARCHAR(255),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (deployed_from_id) REFERENCES deployed_from(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
    FOREIGN KEY (driver_id) REFERENCES drivers(id),
    FOREIGN KEY (group_id) REFERENCES groups(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- 8. Tripper Records Table
CREATE TABLE IF NOT EXISTS tripper_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    deployed_from_id INT NOT NULL,
    vehicle_id INT NOT NULL,
    driver_id INT NOT NULL,
    created_date DATE NOT NULL,
    work_location VARCHAR(200) NOT NULL,
    entry_location VARCHAR(200),
    group_id INT,
    material_type VARCHAR(100),
    quantity_weight DECIMAL(10,2),
    loading_collector_id INT,
    loading_location VARCHAR(200),
    loading_collector_address VARCHAR(255),
    loading_gps_latitude DECIMAL(10,8),
    loading_gps_longitude DECIMAL(11,8),
    loading_gps_address VARCHAR(255),
    loading_gps_captured_at TIMESTAMP NULL,
    loading_start_odometer DECIMAL(10,2) NOT NULL,
    loading_time TIME,
    loading_photo_path VARCHAR(255),
    loading_remarks TEXT,
    unloading_collector_id INT,
    unloading_address VARCHAR(255),
    unloading_place VARCHAR(200),
    unloading_end_odometer DECIMAL(10,2),
    unloading_gps_latitude DECIMAL(10,8),
    unloading_gps_longitude DECIMAL(11,8),
    unloading_gps_address VARCHAR(255),
    unloading_gps_captured_at TIMESTAMP NULL,
    unloading_time TIME,
    unloading_photo_path VARCHAR(255),
    unloading_remarks TEXT,
    status ENUM('pending_unloading', 'completed', 'cancelled') DEFAULT 'pending_unloading',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (deployed_from_id) REFERENCES deployed_from(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
    FOREIGN KEY (driver_id) REFERENCES drivers(id),
    FOREIGN KEY (group_id) REFERENCES groups(id),
    FOREIGN KEY (loading_collector_id) REFERENCES users(id),
    FOREIGN KEY (unloading_collector_id) REFERENCES users(id)
);

-- ============================================
-- DEFAULT DATA
-- ============================================

-- Insert default projects
INSERT INTO projects (project_name, project_code, description) VALUES 
('Bishnumati', 'BISH', 'Bishnumati Project Area'),
('Bancharre', 'BAN', 'Bancharre Project Area');

-- Insert default deployed from options
INSERT INTO deployed_from (entity_name, entity_type, contact_info) VALUES 
('KMC', 'kmc', 'Kathmandu Metropolitan City'),
('Private Entity', 'private', 'Private Company'),
('Other', 'other', 'Other Sources');

-- Insert default vehicle types
INSERT INTO vehicle_types (type_name, description) VALUES 
('Dozar', 'Dozar vehicle type'),
('Tripper', 'Tripper vehicle type');

-- Insert default admin user (password: admin123)
INSERT INTO users (username, password, full_name, role, collector_access) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'System Administrator', 'admin', 'dozar_only');

-- Insert sample data for testing (optional)
-- INSERT INTO vehicles (vehicle_number, vehicle_type, vehicle_model, capacity) VALUES 
-- ('Ba 1 Ja 2977', 'Tripper', 'Tata', '5 Ton'),
-- ('Ba 2 Ja 1234', 'Dozar', 'Hitachi', '1.5 Ton');

-- INSERT INTO drivers (full_name, phone_number, address, license_number) VALUES
-- ('Ram Sharma', '9841234567', 'Kathamandu', 'DL-12345'),
-- ('Hari Pandey', '9849876543', 'Lalitpur', 'DL-67890');

-- ============================================
-- MIGRATION: Run on existing databases
-- ============================================

ALTER TABLE vehicles
    ADD COLUMN IF NOT EXISTS entity_type ENUM('kmc','private','other') NOT NULL DEFAULT 'kmc'
    AFTER vehicle_type;

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS can_delete TINYINT(1) DEFAULT 0
    AFTER assigned_projects;
