-- ANATL Portal Database Schema
-- Version 1.2

DROP DATABASE IF EXISTS anatl_portal;
CREATE DATABASE anatl_portal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE anatl_portal;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  email VARCHAR(150) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','airline','customs','ground_handler','tenant') NOT NULL,
  company_name VARCHAR(150),
  is_active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE flights (
  id INT AUTO_INCREMENT PRIMARY KEY,
  flight_no VARCHAR(20) NOT NULL,
  airline VARCHAR(100),
  origin VARCHAR(100),
  destination VARCHAR(100),
  std DATETIME,
  sta DATETIME,
  etd DATETIME,
  eta DATETIME,
  status ENUM('scheduled','boarding','departed','arrived','delayed','cancelled') DEFAULT 'scheduled',
  terminal VARCHAR(10),
  gate VARCHAR(10),
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE facility_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  location VARCHAR(200) NOT NULL,
  description TEXT NOT NULL,
  photo_path VARCHAR(255),
  reporter_email VARCHAR(150),
  status ENUM('open','in_progress','resolved') DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE flight_subscribers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(150) NOT NULL,
  flight_no VARCHAR(20) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE slot_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type ENUM('arrival','departure') NOT NULL,
  flight_no VARCHAR(20) NOT NULL,
  flight_date DATE NOT NULL,
  requested_time TIME NOT NULL,
  aircraft_type VARCHAR(50),
  doc_path VARCHAR(255),
  status ENUM('submitted','review','approved','rejected') DEFAULT 'submitted',
  admin_notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE flight_plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  flight_no VARCHAR(20) NOT NULL,
  route VARCHAR(255),
  etd DATETIME NOT NULL,
  eta DATETIME NOT NULL,
  aircraft_reg VARCHAR(20),
  doc_path VARCHAR(255),
  status ENUM('submitted','review','approved','rejected') DEFAULT 'submitted',
  admin_notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE safety_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  incident_date DATE NOT NULL,
  category ENUM('bird_strike','ground_damage','fuel_spill','near_miss','other'),
  description TEXT NOT NULL,
  location VARCHAR(200),
  is_anonymous TINYINT(1) DEFAULT 0,
  doc_path VARCHAR(255),
  status ENUM('submitted','under_review','closed') DEFAULT 'submitted',
  admin_notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE customs_content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  section ENUM('visa','allowed_goods','restricted_goods','prohibited_goods','declaration') NOT NULL,
  lang ENUM('id','en','pt','tet') NOT NULL,
  content LONGTEXT,
  updated_by INT,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_section_lang (section, lang),
  FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE irregular_flights (
  id INT AUTO_INCREMENT PRIMARY KEY,
  flight_no VARCHAR(20) NOT NULL,
  airline VARCHAR(100),
  arrival_dt DATETIME NOT NULL,
  origin VARCHAR(100),
  aircraft_type VARCHAR(50),
  pax_count INT DEFAULT 0,
  notes TEXT,
  created_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE service_reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  flight_id INT NOT NULL,
  vendor_id INT NOT NULL,
  report_path VARCHAR(255),
  submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (flight_id) REFERENCES irregular_flights(id),
  FOREIGN KEY (vendor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  flight_id INT NOT NULL,
  vendor_id INT NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  due_date DATE NOT NULL,
  status ENUM('unpaid','paid') DEFAULT 'unpaid',
  pdf_path VARCHAR(255),
  confirmed_at DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (flight_id) REFERENCES irregular_flights(id),
  FOREIGN KEY (vendor_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rental_units (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  location VARCHAR(200),
  area_m2 DECIMAL(8,2),
  price_monthly DECIMAL(15,2),
  description TEXT,
  photo_path VARCHAR(255),
  is_available TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rental_applications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  unit_id INT NOT NULL,
  business_name VARCHAR(150) NOT NULL,
  business_type VARCHAR(100),
  duration_months INT NOT NULL,
  doc_path VARCHAR(255),
  status ENUM('submitted','review','site_visit','approved','rejected','contract') DEFAULT 'submitted',
  admin_notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (unit_id) REFERENCES rental_units(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rental_contracts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  application_id INT NOT NULL,
  contract_path VARCHAR(255),
  start_date DATE,
  end_date DATE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (application_id) REFERENCES rental_applications(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE maintenance_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  unit_id INT NOT NULL,
  description TEXT NOT NULL,
  photo_path VARCHAR(255),
  status ENUM('open','in_progress','resolved') DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (unit_id) REFERENCES rental_units(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cms_posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('news','announcement','notam') NOT NULL,
  lang ENUM('id','en','pt','tet') DEFAULT 'id',
  title VARCHAR(255) NOT NULL,
  content LONGTEXT NOT NULL,
  published TINYINT(1) DEFAULT 0,
  created_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert test users
INSERT INTO users (username, email, password_hash, role, company_name, is_active) VALUES
('admin', 'admin@anatl.tl', '$2y$10$N9qo8uLOickgx2ZMRZoMyeLDfS/F4.r5TjYQw.k9T2PwSfIK0PJd6', 'admin', 'ANATL', 1),
('garuda', 'garuda@airline.tl', '$2y$10$N9qo8uLOickgx2ZMRZoMyeLDfS/F4.r5TjYQw.k9T2PwSfIK0PJd6', 'airline', 'Garuda Indonesia', 1),
('customs', 'customs@anatl.tl', '$2y$10$N9qo8uLOickgx2ZMRZoMyeLDfS/F4.r5TjYQw.k9T2PwSfIK0PJd6', 'customs', 'Bea Cukai', 1),
('handler', 'handler@service.tl', '$2y$10$N9qo8uLOickgx2ZMRZoMyeLDfS/F4.r5TjYQw.k9T2PwSfIK0PJd6', 'ground_handler', 'Ground Services', 1),
('tenant', 'tenant@business.tl', '$2y$10$N9qo8uLOickgx2ZMRZoMyeLDfS/F4.r5TjYQw.k9T2PwSfIK0PJd6', 'tenant', 'Business Unit', 1);

-- Insert sample flights
INSERT INTO flights (flight_no, airline, origin, destination, std, sta, status, terminal, gate) VALUES
('GA101', 'Garuda Indonesia', 'Jakarta', 'Dili', '2025-05-29 10:00:00', '2025-05-29 12:30:00', 'scheduled', 'A', '1'),
('LA202', 'LATAM', 'Sydney', 'Dili', '2025-05-29 14:30:00', '2025-05-29 17:00:00', 'scheduled', 'A', '2'),
('SQ303', 'Singapore Airlines', 'Singapore', 'Dili', '2025-05-29 16:00:00', '2025-05-29 18:30:00', 'boarding', 'A', '3');

-- Insert sample rental units
INSERT INTO rental_units (name, location, area_m2, price_monthly, description, is_available) VALUES
('Coffee Shop Unit A1', 'Terminal Utama, Level 2', 25.50, 2500000, 'Unit penjualan kopi dengan lokasi strategis', 1),
('Retail Store B2', 'Terminal Utama, Level 1', 45.00, 5000000, 'Toko retail untuk produk duty-free', 1),
('Office Suite C3', 'Gedung Administrasi', 60.00, 7500000, 'Kantor dengan fasilitas lengkap', 0);
