218 lines
7.2 KiB
Text
218 lines
7.2 KiB
Text
|
-- ============================================================================
|
||
|
|
-- 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());
|