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

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 username VARCHAR(80) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('super_admin','admin','employee') NOT NULL DEFAULT 'employee',
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 last_login DATETIME NULL
);

CREATE TABLE cars (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 car_type VARCHAR(120) NOT NULL,
 plate_number VARCHAR(60) NOT NULL,
 chassis_number VARCHAR(120) NULL,
 driver_name VARCHAR(120) NULL,
 receiver_name VARCHAR(120) NULL,
 received_at DATETIME NOT NULL,
 delivered_at DATETIME NULL,
 work_type VARCHAR(255) NULL,
 work_details TEXT NULL,
 total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 status ENUM('received','inspection','waiting','working','completed','ready','delivered') NOT NULL DEFAULT 'received',
 notes TEXT NULL,
 created_by INT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_plate (plate_number),
 INDEX idx_chassis (chassis_number),
 INDEX idx_status (status),
 CONSTRAINT fk_cars_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE payments (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 car_id INT UNSIGNED NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 method ENUM('cash','bank','other') NOT NULL DEFAULT 'cash',
 note VARCHAR(255) NULL,
 paid_by INT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_payment_car FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
 CONSTRAINT fk_payment_user FOREIGN KEY (paid_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE contracts (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 car_id INT UNSIGNED NOT NULL,
 contract_no VARCHAR(40) NOT NULL UNIQUE,
 token VARCHAR(80) NOT NULL UNIQUE,
 created_by INT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_contract_car FOREIGN KEY (car_id) REFERENCES cars(id) ON DELETE CASCADE,
 CONSTRAINT fk_contract_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 entity_type VARCHAR(60) NULL,
 entity_id INT UNSIGNED NULL,
 old_data JSON NULL,
 new_data JSON NULL,
 ip_address VARCHAR(45) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_audit_time (created_at),
 CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE expenses (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(180) NOT NULL,
 amount DECIMAL(12,2) NOT NULL,
 note VARCHAR(255) NULL,
 created_by INT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_expense_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Default admin password: ChangeMe123!
-- Generate a secure hash in setup.php or replace this hash with password_hash().
INSERT INTO users (name, username, password_hash, role)
VALUES ('مدير النظام', 'admin', '$2y$12$t8WjXIjDyNaQdiL9KbD52uSKVkcTOU51Zw6FiX7RpoViWJ7q/nc8K', 'super_admin');