-- stages 
-- stage 0 : launch planes to country's base airport (accepts everyhing sendds nothing back)
-- stage 1 : add NPCs and cargo planes and shop buildings.
-- stage 2 : add player progression, building upgrades, effects and more complex recipes.
-- stage 3 : TBD



-- good
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    username varchar(255) NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    -- NPC users (central-port faction leaders) have is_npc=TRUE and an empty
    -- password_hash so they can never log in; excluded from login + user lists.
    is_npc BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP DEFAULT NULL
);

-- product table act as a product identifier ie egg, milk, flour etc
create table if not exists products (
    id smallint unsigned primary key auto_increment,
    name varchar(255) not null
);

-- for stage 2 will not be used
-- increasing speed (crafting,currency type generation etc)), 
CREATE table if not exists effects (
    id smallint unsigned primary key auto_increment,
    name varchar(255) not null
);

-- used airport grouping and for contracts, each country has 3 produce they can export there will not be cap
create table if not exists countries (
    id smallint unsigned primary key auto_increment,
    name varchar(255) not null,
    continent ENUM('void','Africa', 'Antarctica', 'Asia', 'Europe', 'North America', 'Oceania', 'South America') NOT NULL, -- universal enum
    produce_1_id smallint unsigned not null,
    produce_2_id smallint unsigned not null, 
    produce_3_id smallint unsigned not null,
    foreign key (produce_1_id) references products(id),
    foreign key (produce_2_id) references products(id),
    foreign key (produce_3_id) references products(id)
);

-- main table 
-- primary access table act as a player identifier, each player can only have one airport, but they can have multiple planes and buildings
create table if not exists airports (
    id integer unsigned primary key auto_increment,
    name varchar(255) not null,
    user_id  integer unique not null,
    origin_country_id smallint unsigned not null,
    -- resources
    cash bigint unsigned not null default 100,
    current_passengers INTEGER UNSIGNED NOT NULL DEFAULT 100,
    max_passenger_capacity INTEGER UNSIGNED NOT NULL DEFAULT 100,
    current_fuel INTEGER UNSIGNED NOT NULL DEFAULT 500,
    max_fuel_capacity INTEGER UNSIGNED NOT NULL DEFAULT 1000,
    -- resource generation
    fuel_generation_rate INT UNSIGNED NOT NULL DEFAULT 20,
    passenger_generation_rate INT UNSIGNED NOT NULL DEFAULT 5,
    resources_last_updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- progression
    is_available boolean not null default true, -- if true can be sent planes from anyone
    -- central ports are system-owned (faction NPC) airports, one per continent
    is_central boolean not null default false,
    player_level integer unsigned not null default 1,
    experience_points bigint unsigned not null default 0,
    -- per-type building caps (raised by level-gated capacity upgrades, see internal/db CapUpgradeTiers)
    storage_cap   integer unsigned not null default 3,
    operation_cap integer unsigned not null default 2,
    is_deleted BOOLEAN DEFAULT FALSE,
    foreign key (origin_country_id) references countries(id),
    foreign key (user_id) references users(id),
    constraint chk_fuel_capacity CHECK (current_fuel <= max_fuel_capacity),
    constraint chk_positive_fuel CHECK (current_fuel >= 0),
    CONSTRAINT chk_passenger_capacity CHECK (current_passengers <= max_passenger_capacity),
    CONSTRAINT chk_positive_passenger CHECK (current_passengers >= 0)
);


-- one NPC faction per continent; owns that continent's central port. Lore /
-- progression hook for later stages (major faction leaders).
create table if not exists factions (
    id integer unsigned primary key auto_increment,
    name varchar(255) not null,
    continent ENUM('void','Africa', 'Antarctica', 'Asia', 'Europe', 'North America', 'Oceania', 'South America') NOT NULL UNIQUE,
    leader_name varchar(255) not null,
    background TEXT,
    user_id integer not null,
    central_airport_id integer unsigned not null,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    foreign key (user_id) references users(id),
    foreign key (central_airport_id) references airports(id)
);

-- for stage 1, used as template for recipes.
create table if not exists recipes (
    id integer unsigned primary key auto_increment,
    name varchar(255) not null,
    ingredient_1_id smallint unsigned ,
    ingredient_1_amount integer unsigned ,
    ingredient_2_id smallint unsigned,
    ingredient_2_amount integer unsigned ,
    ingredient_3_id smallint unsigned ,
    ingredient_3_amount integer unsigned ,
    product_id smallint unsigned not null,
    yield_amount integer unsigned not null,
    foreign key (ingredient_1_id) references products(id),
    foreign key (ingredient_2_id) references products(id),
    foreign key (ingredient_3_id) references products(id),
    foreign key (product_id) references products(id)
);





-- BUILDNGS

-- hangars fuel tanks etc
CREATE table if not exists buildings (
    -- shared
    id integer unsigned primary key auto_increment,
    name varchar(255) not null,
    min_level integer unsigned not null DEfAULT 1,
    construction_cost_cash integer unsigned not null default 100,
    upgrade_tier TINYINT UNSIGNED  DEFAULT 0,
    upgrade_cost_cash_to_uptier integer unsigned  default null,
    building_type ENUM('none','storage', 'support', 'operation', 'passive', 'shop', 'production') not null default 'none',
     -- production specific
    -- storage
    capacity integer unsigned , 
    storage_type ENUM( 'fuel' , 'plane', 'product'),
    storage_subtype ENUM('none', 'airplane', 'helicopter', 'seaplane','spaceship'),
    -- support
    effect_id smallint unsigned,
    effect_value integer,
    -- operation
    operation_size enum('small','medium'  ,'large', 'huge'),
    operation_type enum('runway', 'service', 'production'),
   
    -- passive income
    passive_type enum('passengers', 'fuel'),
    passive_rate integer ,
    
    foreign key (effect_id) references effects(id)
    
    );

-- stage 1
create table if not exists building_products (
    id integer unsigned primary key auto_increment,
    building_id integer unsigned not null,

    recipe_id integer unsigned,
    storage_product integer unsigned ,
    storage_1 integer unsigned ,
    storage_2 integer unsigned ,
    storage_3 integer unsigned ,
    foreign key (building_id) references buildings(id),
    foreign key (recipe_id) references recipes(id)
);


-- many to many relation between airport and buildings
create table if not exists airport_buildings (
    id integer unsigned primary key auto_increment,
    airport_id integer unsigned not null,
    building_id integer unsigned not null,
    -- shop specific
    recipe_id integer unsigned ,
    storage_product integer unsigned ,
    storage_1 integer unsigned ,
    storage_2 integer unsigned ,
    storage_3 integer unsigned ,
    level integer unsigned not null default 1,
    foreign key (airport_id) references airports(id) ,
    foreign key (building_id) references buildings(id)
);




-- planes  
-- template for planes
create table if not exists planes (
    id integer unsigned primary key auto_increment,
    name varchar(255) not null,
    min_level integer unsigned not null default 1,
    operation_cost_fuel integer unsigned not null,
    operation_cost_cash integer unsigned not null DEFAULT 10,
    max_passengers integer unsigned,
    travel_time_inSeconds integer unsigned not null,
    operation_type enum('passenger', 'cargo') not null,
    aircraft_size enum('small', 'medium', 'large', 'huge') not null,
    aircraft_type enum('plane', 'helicopter', 'seaplane','spaceship') not null,
    buying_cost_cash integer unsigned not null DEFAULT 100,
    completed_flight_experience integer unsigned not null DEFAULT 20,
    completed_flight_cash_reward integer unsigned  DEFAULT 50,
    completed_flight_cargo_reward integer unsigned DEFAULT null,
    boarding_time_seconds integer unsigned not null DEFAULT 0,
    fueling_time_seconds integer unsigned not null DEFAULT 0

);

-- actual planes owned by players, tracks location, status, assigned hangar etc
CREATE TABLE IF NOT EXISTS player_planes (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    airport_id INT UNSIGNED NOT NULL,
    plane_id INTEGER UNSIGNED NOT NULL,
    
    -- 1. assignment (Must always point to a hangar they own they move between suitable hangars)
    assigned_hangar_id INT UNSIGNED NOT NULL, 
    
    -- 2. Real-time physical location (stored, Service Bay, Runway, or NULL if flying)
    current_building_id INT UNSIGNED DEFAULT NULL, 
   --  assigned_cargo_id INTEGER UNSIGNED DEFAULT NULL, -- stage 1 For cargo planes, track what they're carrying

    status ENUM('idle', 'maintenance', 'boarding', 'taxiing', 'flying', 'unloading') NOT NULL DEFAULT 'idle',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -- fixed at purchase (no ON UPDATE) so the 5-min full-refund sell window is reliable
    purchased_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    flight_start_time TIMESTAMP NULL DEFAULT NULL,
    -- where a dispatched plane is flying to (a central port); NULL when idle/home
    destination_airport_id INT UNSIGNED DEFAULT NULL,


    FOREIGN KEY (airport_id) REFERENCES airports(id),
    FOREIGN KEY (plane_id) REFERENCES planes(id),
    FOREIGN KEY (assigned_hangar_id) REFERENCES airport_buildings(id),
    FOREIGN KEY (current_building_id) REFERENCES airport_buildings(id),
    FOREIGN KEY (destination_airport_id) REFERENCES airports(id)

);

-- stage 1
CREATE TABLE IF NOT EXISTS airport_inventory (
    airport_id INTEGER unsigned NOT NULL,
    product_id SMALLINT UNSIGNED NOT NULL,
    quantity INTEGER UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (airport_id, product_id),
    FOREIGN KEY (airport_id) REFERENCES airports(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);


-- stage ? tbd
CREATE TABLE IF NOT EXISTS airport_contracts (
    id INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    airport_id INTEGER unsigned NOT NULL,
    title VARCHAR(255) NOT NULL,
    
    -- What they need to deliver
    required_product_id SMALLINT UNSIGNED NOT NULL,
    required_amount INTEGER UNSIGNED NOT NULL,
    current_amount_delivered INTEGER UNSIGNED NOT NULL DEFAULT 0,
    
   
    -- Status
    is_completed BOOLEAN NOT NULL DEFAULT FALSE,
    expires_at TIMESTAMP NOT NULL,
    
    FOREIGN KEY (airport_id) REFERENCES airports(id),
    FOREIGN KEY (required_product_id) REFERENCES products(id)
    
);


-- logs

create table if not exists completed_flight_routes_log (
    id integer unsigned primary key auto_increment,
    origin_airport_id integer unsigned not null,
    destination_airport_id integer unsigned not null,
    start_time timestamp not null default current_timestamp,
    end_time timestamp default null,
    foreign key (origin_airport_id) references airports(id),
    foreign key (destination_airport_id) references airports(id)
);
CREATE TABLE IF NOT EXISTS currency_transaction_logs (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    airport_id INT UNSIGNED NOT NULL,
    amount_changed INT NOT NULL, -- Negative for buying, positive for earning
    currency_type ENUM('cash', 'fuel', 'passengers') NOT NULL,
    reason VARCHAR(255) NOT NULL, -- e.g., 'bought_plane_id_12', 'completed_flight_34'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (airport_id) REFERENCES airports(id)
);