SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
    id CHAR(36) NOT NULL,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(255) NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'user',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    license_number VARCHAR(255) NULL,
    pilot_certification VARCHAR(255) NULL,
    favorite_airports LONGTEXT NULL,
    preferences LONGTEXT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS airports (
    icao VARCHAR(8) NOT NULL,
    name VARCHAR(255) NOT NULL,
    elevation INT NULL,
    latitude DOUBLE NULL,
    longitude DOUBLE NULL,
    runways LONGTEXT NULL,
    frequencies LONGTEXT NULL,
    procedures LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (icao),
    KEY idx_airports_lat_lng (latitude, longitude)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pireps (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    icao_code VARCHAR(8) NOT NULL,
    aircraft_type VARCHAR(32) NULL,
    flight_level VARCHAR(16) NULL,
    latitude DOUBLE NULL,
    longitude DOUBLE NULL,
    weather_conditions VARCHAR(255) NULL,
    turbulence VARCHAR(128) NULL,
    icing VARCHAR(128) NULL,
    remarks TEXT NULL,
    submitted_by CHAR(36) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pireps_created (created_at),
    KEY idx_pireps_icao (icao_code),
    CONSTRAINT fk_pireps_user FOREIGN KEY (submitted_by) REFERENCES users (id)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS flight_plans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id CHAR(36) NOT NULL,
    departure_icao VARCHAR(8) NULL,
    destination_icao VARCHAR(8) NULL,
    aircraft_type VARCHAR(32) NULL,
    planned_departure DATETIME NULL,
    flight_rules VARCHAR(16) NOT NULL DEFAULT 'VFR',
    route LONGTEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_flight_plans_user (user_id),
    CONSTRAINT fk_flight_plans_user FOREIGN KEY (user_id) REFERENCES users (id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_keys (
    name VARCHAR(64) NOT NULL,
    value TEXT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS platform_settings (
    name VARCHAR(64) NOT NULL,
    value LONGTEXT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
