-- ============================================================================
-- 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, created_at 
FROM users 
WHERE username = ? LIMIT 1;

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

-- ============================================================================
-- 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
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
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: 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. WAREHOUSE & INVENTORY SYSTEM
-- ============================================================================


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

-- 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,currency_type,income_rate)
VALUES (?, ?, ?, ?, ?);

-- name: addProductionBuilding:execresult
INSERT INTO buildings (name,min_level,construction_cost_cash,recipe_id,storage_1,storage_2,storage_3)
VALUES (?, ?, ?, ?, ?, ?, ?);


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

-- name: GetAirportBuildings :many
SELECT ab.id AS airport_building_id, ab.level, 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 = ?));

-- ============================================================================
-- 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, pp.assigned_cargo_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 = ?, 
    assigned_cargo_id = ?,
    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, pp.assigned_cargo_id
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_country_id, start_time, end_time)
VALUES (?, ?, ?, NOW());