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

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','manager','agent','viewer') NOT NULL DEFAULT 'agent',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_airports (
  user_id INT NOT NULL,
  airport_code ENUM('MAN','EDI') NOT NULL,
  PRIMARY KEY(user_id, airport_code),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS airlines (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  iata_code VARCHAR(3) NOT NULL UNIQUE,
  logo_path VARCHAR(255) DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS delay_codes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL,
  reason VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS flights (
  id INT AUTO_INCREMENT PRIMARY KEY,
  airport_code ENUM('MAN','EDI') NOT NULL,
  airline_id INT NOT NULL,
  flight_date DATE NOT NULL,
  inbound_flight_no VARCHAR(20) NOT NULL,
  outbound_flight_no VARCHAR(20) NOT NULL,
  aircraft_reg VARCHAR(20) NOT NULL,
  origin VARCHAR(4) DEFAULT NULL,
  destination VARCHAR(4) DEFAULT NULL,
  stand VARCHAR(20) DEFAULT NULL,
  sta TIME NOT NULL,
  std TIME NOT NULL,
  ata TIME DEFAULT NULL,
  atd TIME DEFAULT NULL,
  assigned_user_id INT DEFAULT NULL,
  status ENUM('scheduled','in_progress','completed') NOT NULL DEFAULT 'scheduled',
  completed_by INT DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  delay_code VARCHAR(20) DEFAULT NULL,
  delay_reason TEXT DEFAULT NULL,
  operational_notes TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (airline_id) REFERENCES airlines(id),
  FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
  FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS checklist_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_text TEXT NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_mandatory TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS flight_checklist_responses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  flight_id INT NOT NULL,
  checklist_item_id INT NOT NULL,
  response ENUM('pending','yes','no','na') NOT NULL DEFAULT 'pending',
  comments TEXT DEFAULT NULL,
  updated_by INT DEFAULT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_response (flight_id, checklist_item_id),
  FOREIGN KEY (flight_id) REFERENCES flights(id) ON DELETE CASCADE,
  FOREIGN KEY (checklist_item_id) REFERENCES checklist_items(id),
  FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT DEFAULT NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id INT DEFAULT NULL,
  details TEXT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

INSERT IGNORE INTO airlines (name, iata_code, logo_path) VALUES
('Air Transat', 'TS', 'assets/logos/air-transat.svg'),
('Air Canada', 'AC', 'assets/logos/air-canada.svg');

INSERT IGNORE INTO delay_codes (code, reason) VALUES
('65', 'Late passenger or baggage acceptance'),
('89', 'Restrictions at airport of departure or arrival');

INSERT INTO checklist_items (item_text, sort_order, is_mandatory) VALUES
('Review arrival flight and identify on time or delayed arrival.', 10, 1),
('For early arrivals and on time ops, ensure the ops agent pulls back the ETD by -10 at -60. No earlier as it affects the onload list.', 20, 1),
('Review staffing levels FOH/ramp/Ops/Cleaning/GSE.', 30, 1),
('Touch base with the station duty manager to identify any operational challenges.', 40, 1),
('ID any issues with catering/fueling.', 50, 1),
('ID our stand and ensure that it is favourable.', 60, 1),
('ID PRMs and ensure they are aware for both arrival and departure and ID any challenges. Follow up may be required during arrival/departure.', 70, 1),
('When on MSDT, ensure the attached checklist is followed.', 80, 1),
('Ensure cleaning on to J class first, even if Y is still disembarking. Put pressure to complete in 25 minutes.', 90, 1),
('Ensure the crew are at the aircraft in good time. Once J class is almost done, get them down.', 100, 1),
('Start staging pax once crew are all down.', 110, 1),
('Review Smartsuite throughout the turnaround and ID any missing bags, especially transfers. Communicate to bag hall/FTC.', 120, 1),
('GO target is to ensure all doors, BTW and ATW are closed at -5.', 130, 1),
('Ensure the GHA completes reports. Ramp SCI, Synopsis, LV checklist in iAuditor.', 140, 1),
('On delayed flights, review with the GHA the applicable delay code before posting and ensure backup comments are added in S4A.', 150, 1);

-- Demo admin password: admin123
INSERT IGNORE INTO users (name, email, password_hash, role) VALUES
('PFS Admin', 'admin@pfs.local', '$2y$10$u5SOKb6a1BVZ8jFszSTvC.Ot3Eh7K/4nScGXx2/gw5PZayhT2xfXq', 'admin');
INSERT IGNORE INTO user_airports (user_id, airport_code) SELECT id, 'MAN' FROM users WHERE email='admin@pfs.local';
INSERT IGNORE INTO user_airports (user_id, airport_code) SELECT id, 'EDI' FROM users WHERE email='admin@pfs.local';