-- +goose Up
-- HEMS contacts/users module schema extension

ALTER TABLE roles
  ADD COLUMN IF NOT EXISTS code VARCHAR(64) NULL,
  ADD UNIQUE KEY IF NOT EXISTS uq_roles_code (code);

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS username VARCHAR(100) NULL,
  ADD UNIQUE KEY IF NOT EXISTS uq_users_username (username),
  ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS pilot_profiles (
  user_id BINARY(16) NOT NULL,
  pilot_category ENUM('regular','freelance','dry_lease') NOT NULL,
  short_name VARCHAR(120) NULL,
  license_no VARCHAR(120) NULL,
  license_issued_at DATETIME NULL,
  license_expired_at DATETIME NULL,
  technician_license_no VARCHAR(120) NULL,
  has_ifr_qualification TINYINT(1) NOT NULL DEFAULT 0,
  is_chief_pilot TINYINT(1) NOT NULL DEFAULT 0,
  is_dul TINYINT(1) NOT NULL DEFAULT 0,
  total_flight_minutes INT NOT NULL DEFAULT 0,
  responsible_pilot_minutes INT NOT NULL DEFAULT 0,
  second_pilot_minutes INT NOT NULL DEFAULT 0,
  double_command_minutes INT NOT NULL DEFAULT 0,
  flight_instructor_minutes INT NOT NULL DEFAULT 0,
  night_flight_minutes INT NOT NULL DEFAULT 0,
  ifr_flight_minutes INT NOT NULL DEFAULT 0,
  location VARCHAR(255) NULL,
  postcode VARCHAR(32) NULL,
  street_line VARCHAR(255) NULL,
  weight_kg DECIMAL(8,2) NULL,
  photo_file_id VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BINARY(16) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by BINARY(16) NULL,
  PRIMARY KEY (user_id),
  KEY idx_pilot_profiles_category (pilot_category),
  KEY idx_pilot_profiles_license (license_no),
  CONSTRAINT fk_pilot_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS doctor_profiles (
  user_id BINARY(16) NOT NULL,
  short_name VARCHAR(120) NULL,
  medical_license_no VARCHAR(120) NULL,
  license_issued_at DATETIME NULL,
  license_expired_at DATETIME NULL,
  specialization VARCHAR(120) NULL,
  sub_specialization VARCHAR(120) NULL,
  is_chief_physician TINYINT(1) NOT NULL DEFAULT 0,
  location VARCHAR(255) NULL,
  postcode VARCHAR(32) NULL,
  street_line VARCHAR(255) NULL,
  photo_file_id VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BINARY(16) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by BINARY(16) NULL,
  PRIMARY KEY (user_id),
  KEY idx_doctor_profiles_medical_license (medical_license_no),
  CONSTRAINT fk_doctor_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS air_rescuer_profiles (
  user_id BINARY(16) NOT NULL,
  short_name VARCHAR(120) NULL,
  is_chief_physician_in_charge TINYINT(1) NOT NULL DEFAULT 0,
  responsible_flight_rescuer TINYINT(1) NOT NULL DEFAULT 0,
  location VARCHAR(255) NULL,
  postcode VARCHAR(32) NULL,
  street_line VARCHAR(255) NULL,
  photo_file_id VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BINARY(16) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by BINARY(16) NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT fk_air_rescuer_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS technician_profiles (
  user_id BINARY(16) NOT NULL,
  short_name VARCHAR(120) NULL,
  license_no VARCHAR(120) NULL,
  is_chief_technician TINYINT(1) NOT NULL DEFAULT 0,
  is_responsible_complaints TINYINT(1) NOT NULL DEFAULT 0,
  location VARCHAR(255) NULL,
  postcode VARCHAR(32) NULL,
  street_line VARCHAR(255) NULL,
  photo_file_id VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BINARY(16) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by BINARY(16) NULL,
  PRIMARY KEY (user_id),
  KEY idx_technician_profiles_license (license_no),
  CONSTRAINT fk_technician_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS flight_assistant_profiles (
  user_id BINARY(16) NOT NULL,
  short_name VARCHAR(120) NULL,
  location VARCHAR(255) NULL,
  postcode VARCHAR(32) NULL,
  street_line VARCHAR(255) NULL,
  photo_file_id VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BINARY(16) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by BINARY(16) NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT fk_flight_assistant_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS staff_profiles (
  user_id BINARY(16) NOT NULL,
  short_name VARCHAR(120) NULL,
  role_label VARCHAR(120) NULL,
  location VARCHAR(255) NULL,
  postcode VARCHAR(32) NULL,
  street_line VARCHAR(255) NULL,
  photo_file_id VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BINARY(16) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by BINARY(16) NULL,
  PRIMARY KEY (user_id),
  CONSTRAINT fk_staff_profiles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

-- +goose Down
DROP TABLE IF EXISTS staff_profiles;
DROP TABLE IF EXISTS flight_assistant_profiles;
DROP TABLE IF EXISTS technician_profiles;
DROP TABLE IF EXISTS air_rescuer_profiles;
DROP TABLE IF EXISTS doctor_profiles;
DROP TABLE IF EXISTS pilot_profiles;

ALTER TABLE users
  DROP COLUMN IF EXISTS is_active,
  DROP INDEX IF EXISTS uq_users_username,
  DROP COLUMN IF EXISTS username;

ALTER TABLE roles
  DROP INDEX IF EXISTS uq_roles_code,
  DROP COLUMN IF EXISTS code;