-- ============================================================================
-- 1. AUTHENTICATION & USERS
-- ============================================================================

-- name: CreateUser :execresult
INSERT INTO users (username, password_hash) 
VALUES (?, ?);

-- name: UpdateUserPasswordByUserName :exec
UPDATE users 
SET password_hash = ? 
WHERE username = ?;

-- name: GetUserByUsername :one
SELECT id, username, password_hash 
FROM users 
WHERE username = ? LIMIT 1;

-- name: GetUserIdByUsername :one
SELECT id 
FROM users 
WHERE username = ? LIMIT 1;

-- name: DeleteUserByUsername :exec
UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE username = ?;


-- name: DeleteAirportByUserName :exec
UPDATE airports
SET is_deleted = TRUE
WHERE user_id = (SELECT id FROM users WHERE username = ?);

-- ============================================================================
-- 2. AIRPORT CORE MANAGEMENT
-- ============================================================================

-- airport row management

-- name: CreateAirportByUserName :execresult
INSERT INTO airports (name, user_id, origin_country_id) 
VALUES (?, (SELECT id FROM users WHERE username = ?), ?);


-- name: GetAirportByUserId :one
SELECT id, name, user_id, origin_country_id, cash, current_passengers, max_passenger_capacity, current_fuel, max_fuel_capacity, is_available, player_level, experience_points, passenger_generation_rate, fuel_generation_rate, resources_last_updated_at, is_deleted
FROM airports 
WHERE user_id = ? LIMIT 1;
-- name: GetAirportByUserName :one
SELECT id, name, user_id, origin_country_id, cash, current_passengers, max_passenger_capacity, current_fuel, max_fuel_capacity, is_available, player_level, experience_points ,passenger_generation_rate, fuel_generation_rate, resources_last_updated_at, is_deleted
FROM airports 
WHERE user_id = (SELECT id FROM users WHERE username = ?) LIMIT 1;


-- airport resource management


-- name: UpdateAirportCurrencies :exec
UPDATE airports 
SET cash = ?, 
    current_passengers = ?, 
    current_fuel = ?
WHERE id = ?;

-- name: UpdateAirportResources :exec
UPDATE airports
SET 
    current_fuel = LEAST(
        max_fuel_capacity, 
        current_fuel + (fuel_generation_rate * TIMESTAMPDIFF(SECOND, resources_last_updated_at, NOW()))
    ),
    current_passengers = LEAST(
        max_passenger_capacity, 
        current_passengers + (passenger_generation_rate * TIMESTAMPDIFF(SECOND, resources_last_updated_at, NOW()))
    ),
    resources_last_updated_at = NOW()
WHERE id = ?;

-- name: UpdateAirportCash :exec
UPDATE airports 
SET cash = ?
WHERE id = ?;
-- name: UpdateAirportpassengers :exec
UPDATE airports 
SET current_passengers = ?
WHERE id = ?;
-- name: UpdateAirportFuel :exec
UPDATE airports 
SET current_fuel = ?
WHERE id = ?;
-- name: UpdateAirportmaxPassengers :exec
UPDATE airports 
SET max_passenger_capacity = ?
WHERE id = ?;
-- name: UpdateAirportMaxFuel :exec
UPDATE airports 
SET max_fuel_capacity = ?
WHERE id = ?;

-- name: UpdateAirportFuelRate :exec
UPDATE airports 
SET fuel_generation_rate = ?
WHERE id = ?;
-- name: UpdateAirportPassengerRate :exec
UPDATE airports 
SET passenger_generation_rate = ?
WHERE id = ?;

-- name: UpdateAirportResourcesUpdated :exec
update airports
set resources_last_updated_at = ?
where id = ?;


-- name: updatePlayerExperience :exec
UPDATE airports 
SET experience_points =  ?
WHERE id = ?;

-- name: updatePlayerLevel :exec
UPDATE airports 
SET player_level =  ?
WHERE id = ?;



-- ============================================================================
-- 3. country
-- ============================================================================
-- name: GetAllCountries :many
SELECT id, name FROM countries;
-- name: GetCountryById :one
SELECT id, name FROM countries WHERE id = ?;
-- name: GetCountryByName :one
SELECT id, name FROM countries WHERE name = ?;

-- name: AddCountry :execresult
INSERT INTO countries (name,continent,produce_1_id,produce_2_id,produce_3_id) 
VALUES (?, ?, ?, ?, ?);



-- ============================================================================
-- 4. BUILDINGS & PRODUCTION ENGINE
-- ============================================================================

-- name: Addproduct :execresult
insert into products (name) values (?);

-- name: GetAllProducts :many
select id, name from products;

-- name: GetProductById :one
select id, name from products where id = ?;


-- name: addStorageBuilding :execresult
INSERT INTO buildings (name,min_level,construction_cost_cash,capacity,storage_type,storage_subtype)
VALUES (?, ?, ?, ?, ?, ?);

-- name: addOperationalBuilding :execresult
INSERT INTO buildings (name,min_level,construction_cost_cash,operation_type,operation_size)
VALUES (?, ?, ?, ?, ?);

-- name: addPassiveProductionBuilding :execresult
INSERT INTO buildings (name,min_level,construction_cost_cash,passive_type,passive_rate)
VALUES (?, ?, ?, ?, ?);

-- name: addBuilding :execresult
INSERT INTO buildings (name,min_level,construction_cost_cash)
VALUES (?, ?, ?);


-- name: AddBuildingToAirportByUsername :execresult
INSERT INTO airport_buildings (airport_id, building_id) 
VALUES ((select id from airports where user_id = (select id from users where username = ?)), ?);

-- name: GetAirportBuildingsByUsername :many
SELECT ab.id AS airport_building_id,b.building_type , b.*
FROM airport_buildings ab
JOIN buildings b ON ab.building_id = b.id
WHERE ab.airport_id = (select id from airports where user_id = (select id from users where username = ?));



-- planes & fleet management

-- name: addPlane :execresult
INSERT INTO planes (name, aircraft_size,operation_type,aircraft_type ,min_level, operation_cost_fuel, operation_cost_cash, max_passengers, travel_time_inSeconds,buying_cost_cash, completed_flight_experience, completed_flight_cash_reward, completed_flight_cargo_reward)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);


-- name: GetPlaneById :one
SELECT * FROM planes WHERE id = ?;

-- name: GetAllPlanes :many
SELECT * FROM planes;

-- name: GetPlanesByOperationType :many
SELECT * FROM planes WHERE operation_type = ?;

-- name: GetPlanesByMinLevel :many
SELECT * FROM planes WHERE min_level <= ?;

-- name: GetPlanesByAircraftSize :many
SELECT * FROM planes WHERE aircraft_size = ?;

-- name: GetPlanesByAircraftType :many
SELECT * FROM planes WHERE aircraft_type = ?;

-- name: addPlaneToAirportByUsername :execresult
INSERT INTO player_planes (airport_id, plane_id, assigned_hangar_id, current_building_id, status) 
VALUES ((select id from airports where user_id = (select id from users where username = ?)), ?, ?, ?, 'idle');  

-- name: GetPlayerPlanesByUsername :many
SELECT pp.id AS player_plane_id, pp.*
FROM player_planes pp
JOIN planes p ON pp.plane_id = p.id
WHERE pp.airport_id = (select id from airports where user_id = (select id from users where username = ?));


-- name: FindAvailableInfrastructureForPlane :one
SELECT ab.id
FROM airport_buildings ab
JOIN buildings b ON ab.building_id = b.id
LEFT JOIN player_planes pp ON ab.id = pp.current_building_id
WHERE ab.airport_id = (select id from airports where user_id = (select id from users where username = ?)) 
  AND b.operation_type = ?       -- 'runway' or 'service' (service bay)
  AND b.operation_size = ?       -- matches plane's aircraft_size requirement
  AND pp.id IS NULL              -- Ensures no other plane is physically here right now
LIMIT 1;  

-- name: UpdatePlayerPlaneState :exec
UPDATE player_planes 
SET current_building_id = ?, 
    status = ?, 
    flight_start_time = ?
WHERE id = ?; 

-- name: CheckHangarOccupancyByUsername :one
SELECT COUNT(*) AS total_parked 
FROM player_planes pp
JOIN airport_buildings ab ON pp.current_building_id = ab.id
WHERE ab.airport_id = (select id from airports where user_id = (select id from users where username = ?)) 
  AND ab.building_id = ?; -- specific hangar  

  -- 
-- name: CheckRunwayOccupancyByUsername :one
SELECT COUNT(*) AS total_occupied 
FROM player_planes pp
JOIN airport_buildings ab ON pp.current_building_id = ab.id
WHERE ab.airport_id = (select id from airports where user_id = (select id from users where username = ?)) 
  AND ab.building_id = ?; -- specific runway  

-- name: GetPlanesReadyToLandByUsername :many
SELECT pp.id AS player_plane_id, pp.airport_id, pp.assigned_hangar_id,
       p.completed_flight_experience, p.completed_flight_cash_reward, p.completed_flight_cargo_reward, p.operation_type
FROM player_planes pp
JOIN planes p ON pp.plane_id = p.id
WHERE pp.status = 'flying'
  AND ADDTIME(pp.flight_start_time, p.travel_time) <= NOW()
  AND pp.airport_id = (select id from airports where user_id = (select id from users where username = ?));    















-- ============================================================================
-- 5. FLEET & DYNAMIC INFRASTRUCTURE OPERATIONS
-- ============================================================================

-- name: BuyPlaneForPlayer :execresult
INSERT INTO player_planes (airport_id, plane_id, assigned_hangar_id, current_building_id, status) 
VALUES (?, ?, ?, ?, 'idle');

-- name: GetPlayerFleet :many
SELECT pp.id AS player_plane_id, pp.status, pp.updated_at, pp.flight_start_time, pp.current_building_id, pp.assigned_hangar_id,p.*
FROM player_planes pp
JOIN planes p ON pp.plane_id = p.id
WHERE pp.airport_id = ?;

-- name: FindAvailableInfrastructure :one
SELECT ab.id
FROM airport_buildings ab
JOIN buildings b ON ab.building_id = b.id
LEFT JOIN player_planes pp ON ab.id = pp.current_building_id
WHERE ab.airport_id = ? 
  AND b.operation_type = ?       -- 'runway' or 'service' (service bay)
  AND b.operation_size = ?       -- matches plane's aircraft_size requirement
  AND pp.id IS NULL              -- Ensures no other plane is physically here right now
LIMIT 1;

-- name: UpdatePlaneState :exec
UPDATE player_planes 
SET current_building_id = ?, 
    status = ?, 
    
    flight_start_time = ?
WHERE id = ?;

-- name: CheckHangarOccupancy :one
SELECT COUNT(*) AS total_parked 
FROM player_planes 
WHERE current_building_id = ?;


-- ============================================================================
-- 6. TIME-BASED FLIGHT ENGINE
-- ============================================================================

-- name: GetPlanesReadyToLand :many
SELECT pp.id AS player_plane_id, pp.airport_id, pp.assigned_hangar_id,
       p.completed_flight_experience, p.completed_flight_cash_reward, p.completed_flight_cargo_reward, p.operation_type
FROM player_planes pp
JOIN planes p ON pp.plane_id = p.id
WHERE pp.status = 'flying' 
  -- Uses your logic: flight_start_time + plane travel duration <= current clock
  AND ADDTIME(pp.flight_start_time, p.travel_time) <= NOW();


-- ============================================================================
-- 7. PROGRESSION CONTRACTS & ECONOMY LOGS
-- ============================================================================

-- name: GetActiveContracts :many
SELECT id, title, required_product_id, required_amount, current_amount_delivered, is_completed, expires_at 
FROM airport_contracts 
WHERE airport_id = ? AND is_completed = FALSE AND expires_at > NOW();

-- name: UpdateContractDelivery :exec
UPDATE airport_contracts 
SET current_amount_delivered = current_amount_delivered + ?
WHERE id = ? AND is_completed = FALSE;

-- name: CompleteContract :exec
UPDATE airport_contracts 
SET is_completed = TRUE 
WHERE id = ?;

-- name: LogCurrencyTransaction :exec
INSERT INTO currency_transaction_logs (airport_id, amount_changed, currency_type, reason) 
VALUES (?, ?, ?, ?);

-- name: LogCompletedFlightRoute :exec
INSERT INTO completed_flight_routes_log (origin_airport_id, destination_airport_id, start_time, end_time)
VALUES (?, ?, ?, NOW());