CREATE TABLE IF NOT EXISTS users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(80) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL,
 full_name VARCHAR(160) NOT NULL, email VARCHAR(190) DEFAULT '', role VARCHAR(60) NOT NULL, department VARCHAR(160) DEFAULT '', designation VARCHAR(160) DEFAULT '',
 approver_user_id INT UNSIGNED NULL, department_id INT UNSIGNED NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, must_change_password TINYINT(1) NOT NULL DEFAULT 0, credentials_sent_at DATETIME NULL, last_password_change_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 INDEX(role), INDEX(department), INDEX(department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS requisitions (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, req_no VARCHAR(40) UNIQUE NOT NULL, applicant_user_id INT UNSIGNED NOT NULL,
 applicant_name VARCHAR(160) NOT NULL, applicant_designation VARCHAR(160) DEFAULT '', department VARCHAR(160) DEFAULT '', contact VARCHAR(80) DEFAULT '',
 use_type ENUM('Official','Private') NOT NULL DEFAULT 'Official', payment_status ENUM('Paid','Unpaid') NOT NULL DEFAULT 'Unpaid',
 required_date DATE NOT NULL, report_time TIME NULL, return_date DATE NULL, return_time TIME NULL, vehicle_type VARCHAR(100) NOT NULL,
 report_at VARCHAR(255) DEFAULT '', destination TEXT NOT NULL, purpose TEXT, applicant_signature VARCHAR(255) DEFAULT '', applicant_signed_at DATETIME NULL,
 workflow_stage VARCHAR(80) NOT NULL DEFAULT 'draft', current_assignee_user_id INT UNSIGNED NULL, current_assignee_role VARCHAR(60) DEFAULT '', final_decision VARCHAR(40) DEFAULT '', decision_reason TEXT,
 vehicle_id INT UNSIGNED NULL, driver_id INT UNSIGNED NULL, vehicle_no VARCHAR(80) DEFAULT '', driver_name VARCHAR(160) DEFAULT '', helper_name VARCHAR(160) DEFAULT '',
 meter_out DECIMAL(12,2) DEFAULT 0, meter_in DECIMAL(12,2) DEFAULT 0, mileage DECIMAL(12,2) DEFAULT 0, rate_per_km DECIMAL(12,2) DEFAULT 0, cost DECIMAL(12,2) DEFAULT 0,
 toll_tax DECIMAL(12,2) DEFAULT 0, driver_charges DECIMAL(12,2) DEFAULT 0, total_charges DECIMAL(12,2) DEFAULT 0, transport_remarks TEXT, duty_status VARCHAR(40) DEFAULT '',
 completed_at DATETIME NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 INDEX(applicant_user_id),INDEX(current_assignee_user_id),INDEX(workflow_stage),INDEX(required_date),INDEX(department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS workflow_actions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,requisition_id INT UNSIGNED NOT NULL,actor_user_id INT UNSIGNED NOT NULL,actor_name VARCHAR(160) NOT NULL,actor_role VARCHAR(60) NOT NULL,
 action VARCHAR(120) NOT NULL,from_stage VARCHAR(80) DEFAULT '',to_stage VARCHAR(80) DEFAULT '',remarks TEXT,decision VARCHAR(40) DEFAULT '',created_at DATETIME NOT NULL, INDEX(requisition_id),INDEX(actor_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS departments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(160) UNIQUE NOT NULL,faculty VARCHAR(160) DEFAULT '',approver_user_id INT UNSIGNED NULL,is_active TINYINT(1) DEFAULT 1,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,INDEX(approver_user_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS vehicles (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,vehicle_no VARCHAR(80) UNIQUE NOT NULL,vehicle_type VARCHAR(100) DEFAULT '',make_model VARCHAR(160) DEFAULT '',capacity VARCHAR(60) DEFAULT '',status VARCHAR(40) DEFAULT 'Available',notes TEXT,is_active TINYINT(1) DEFAULT 1,
 registration_no VARCHAR(100) DEFAULT '',chassis_no VARCHAR(100) DEFAULT '',engine_no VARCHAR(100) DEFAULT '',ownership VARCHAR(40) DEFAULT 'Owned',fuel_type VARCHAR(40) DEFAULT '',seating_capacity INT DEFAULT 0,
 insurance_expiry DATE NULL,fitness_expiry DATE NULL,token_tax_expiry DATE NULL,photo VARCHAR(255) DEFAULT '',registration_document VARCHAR(255) DEFAULT '',odometer DECIMAL(12,2) DEFAULT 0,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL, INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS drivers (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,full_name VARCHAR(160) NOT NULL,employee_no VARCHAR(80) DEFAULT '',phone VARCHAR(80) DEFAULT '',license_no VARCHAR(100) DEFAULT '',status VARCHAR(40) DEFAULT 'Available',is_active TINYINT(1) DEFAULT 1,
 cnic VARCHAR(40) DEFAULT '',license_expiry DATE NULL,medical_expiry DATE NULL,emergency_contact VARCHAR(80) DEFAULT '',shift_name VARCHAR(80) DEFAULT '',photo VARCHAR(255) DEFAULT '',attendance_status VARCHAR(40) DEFAULT 'Present',leave_until DATE NULL,performance_score DECIMAL(5,2) DEFAULT 0,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL, INDEX(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS duty_assignments (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,requisition_id INT UNSIGNED UNIQUE NOT NULL,vehicle_id INT UNSIGNED NOT NULL,driver_id INT UNSIGNED NOT NULL,helper_name VARCHAR(160) DEFAULT '',assigned_by INT UNSIGNED NOT NULL,assigned_at DATETIME NOT NULL,released_at DATETIME NULL,status VARCHAR(40) DEFAULT 'Reserved',notes TEXT,INDEX(status)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS fuel_entries (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,requisition_id INT UNSIGNED NULL,vehicle_id INT UNSIGNED NOT NULL,driver_id INT UNSIGNED NULL,fuel_date DATE NOT NULL,station VARCHAR(160) DEFAULT '',slip_no VARCHAR(80) DEFAULT '',liters DECIMAL(10,2) DEFAULT 0,cost DECIMAL(12,2) DEFAULT 0,odometer DECIMAL(12,2) DEFAULT 0,notes TEXT,created_by INT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,INDEX(vehicle_id,fuel_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS workshop_jobs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,vehicle_id INT UNSIGNED NOT NULL,job_date DATE NOT NULL,job_type VARCHAR(100) NOT NULL,description TEXT,vendor VARCHAR(160) DEFAULT '',cost DECIMAL(12,2) DEFAULT 0,odometer DECIMAL(12,2) DEFAULT 0,next_service_date DATE NULL,next_service_odometer DECIMAL(12,2) DEFAULT 0,status VARCHAR(40) DEFAULT 'Open',notes TEXT,created_by INT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,INDEX(vehicle_id,job_date)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS notifications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,requisition_id INT UNSIGNED NULL,title VARCHAR(190) NOT NULL,message TEXT NOT NULL,type VARCHAR(40) DEFAULT 'info',is_read TINYINT(1) DEFAULT 0,created_at DATETIME NOT NULL,INDEX(user_id,is_read,created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS attachments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,requisition_id INT UNSIGNED NOT NULL,uploaded_by INT UNSIGNED NOT NULL,original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,mime_type VARCHAR(120) DEFAULT '',file_size INT UNSIGNED DEFAULT 0,note VARCHAR(255) DEFAULT '',created_at DATETIME NOT NULL,INDEX(requisition_id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS delegations (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,from_user_id INT UNSIGNED NOT NULL,to_user_id INT UNSIGNED NOT NULL,start_date DATE NOT NULL,end_date DATE NOT NULL,reason TEXT,is_active TINYINT(1) DEFAULT 1,created_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS app_settings (`key` VARCHAR(100) PRIMARY KEY,value LONGTEXT NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NULL,user_name VARCHAR(160) DEFAULT '',event VARCHAR(120) NOT NULL,entity VARCHAR(80) DEFAULT '',entity_id INT UNSIGNED NULL,ip_address VARCHAR(64) DEFAULT '',user_agent VARCHAR(255) DEFAULT '',meta_json LONGTEXT,created_at DATETIME NOT NULL,INDEX(user_id),INDEX(entity,entity_id),INDEX(created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS roles (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 role_key VARCHAR(60) UNIQUE NOT NULL,
 role_name VARCHAR(120) NOT NULL,
 is_system TINYINT(1) NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS role_permissions (
 role_id INT UNSIGNED NOT NULL,
 permission_key VARCHAR(100) NOT NULL,
 allowed TINYINT(1) NOT NULL DEFAULT 1,
 PRIMARY KEY(role_id,permission_key), INDEX(permission_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;