-- Transfer Rezervasyon Takip Sistemi
-- MySQL / MariaDB şeması

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS transfer_rezervasyon
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE transfer_rezervasyon;

-- Kullanıcı rolleri: owner (firma sahibi), admin (yönetici), driver (şoför)
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(120) NOT NULL,
    role ENUM('owner', 'admin', 'driver') NOT NULL DEFAULT 'driver',
    driver_id INT UNSIGNED NULL,
    email VARCHAR(150) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_users_role (role),
    INDEX idx_users_driver (driver_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS drivers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    phone VARCHAR(30) NULL,
    license_no VARCHAR(50) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vehicles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plate VARCHAR(20) NOT NULL UNIQUE,
    brand_model VARCHAR(100) NOT NULL,
    capacity TINYINT UNSIGNED NOT NULL DEFAULT 4,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ALTER TABLE users
    ADD CONSTRAINT fk_users_driver
    FOREIGN KEY (driver_id) REFERENCES drivers(id)
    ON DELETE SET NULL ON UPDATE CASCADE;

-- Alan bazlı görünürlük izinleri (owner yöneticiden, admin şoförden gizleyebilir)
CREATE TABLE IF NOT EXISTS field_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    target_role ENUM('admin', 'driver') NOT NULL,
    field_key VARCHAR(60) NOT NULL,
    is_visible TINYINT(1) NOT NULL DEFAULT 1,
    updated_by INT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_role_field (target_role, field_key),
    CONSTRAINT fk_permissions_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS reservations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_date DATE NOT NULL,
    arrival_datetime DATETIME NOT NULL,
    passenger_first_name VARCHAR(80) NOT NULL DEFAULT '',
    passenger_last_name VARCHAR(80) NOT NULL DEFAULT '',
    passenger_phone VARCHAR(30) NULL,
    from_location VARCHAR(255) NOT NULL DEFAULT '',
    to_location VARCHAR(255) NOT NULL DEFAULT '',
    flight_code VARCHAR(20) NULL,
    passenger_count TINYINT UNSIGNED NOT NULL DEFAULT 1,
    driver_id INT UNSIGNED NULL,
    vehicle_id INT UNSIGNED NULL,
    notes TEXT NULL,
    status ENUM('pending', 'confirmed', 'completed', 'cancelled') NOT NULL DEFAULT 'pending',
    created_by INT UNSIGNED NULL,
    updated_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_res_date (reservation_date),
    INDEX idx_res_arrival (arrival_datetime),
    INDEX idx_res_driver (driver_id),
    INDEX idx_res_vehicle (vehicle_id),
    CONSTRAINT fk_res_driver FOREIGN KEY (driver_id) REFERENCES drivers(id) ON DELETE SET NULL,
    CONSTRAINT fk_res_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id) ON DELETE SET NULL,
    CONSTRAINT fk_res_created FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_res_updated FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Otomatik kayıt geçmişi (denetim)
CREATE TABLE IF NOT EXISTS reservation_audit (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reservation_id INT UNSIGNED NOT NULL,
    field_key VARCHAR(60) NOT NULL,
    old_value TEXT NULL,
    new_value TEXT NULL,
    changed_by INT UNSIGNED NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_res (reservation_id),
    CONSTRAINT fk_audit_res FOREIGN KEY (reservation_id) REFERENCES reservations(id) ON DELETE CASCADE,
    CONSTRAINT fk_audit_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;