madgerm
12ddf5a185
- market.php: Buy new aircraft (6 types: Dash8 to A380) - aircraft.php: Fleet management, crew assignment, route planning - finances.php: Account balance, transactions, loans - Database.php: Extended with loan/transaction/aircraft methods - schema.sql: Aircraft types table, 10M starting balance, INSERT OR IGNORE - dashboard.php: Updated with fleet/financial overview - Fixed: duplicate function declarations in Database.php - Fixed: initializeSchema() with idempotent error handling
186 lines
6.9 KiB
SQL
186 lines
6.9 KiB
SQL
-- Airline Tycoon Browser-MMO Datenbankschema (SQLite)
|
|
-- Phase 1: User, Airlines, Flughäfen, Basis-Routen
|
|
|
|
-- Benutzer (Spieler)
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username VARCHAR(50) UNIQUE NOT NULL,
|
|
email VARCHAR(100) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
airline_name VARCHAR(100) DEFAULT '',
|
|
balance DECIMAL(15,2) DEFAULT 10000000.00,
|
|
level INTEGER DEFAULT 1,
|
|
experience INTEGER DEFAULT 0,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_login DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
is_active BOOLEAN DEFAULT 1
|
|
);
|
|
|
|
-- Flughäfen (statisch, geladen aus CSV)
|
|
CREATE TABLE IF NOT EXISTS airports (
|
|
id INTEGER PRIMARY KEY,
|
|
iata_code VARCHAR(3) UNIQUE NOT NULL,
|
|
name VARCHAR(200) NOT NULL,
|
|
city VARCHAR(100) NOT NULL,
|
|
country VARCHAR(100) NOT NULL,
|
|
continent VARCHAR(50) NOT NULL,
|
|
latitude DECIMAL(10,6) NOT NULL,
|
|
longitude DECIMAL(10,6) NOT NULL,
|
|
timezone VARCHAR(50) DEFAULT 'UTC',
|
|
passenger_volume INTEGER DEFAULT 1000000,
|
|
demand_multiplier DECIMAL(3,2) DEFAULT 1.00
|
|
);
|
|
|
|
-- Flugzeuge (Fleet)
|
|
CREATE TABLE IF NOT EXISTS aircraft (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
aircraft_type VARCHAR(50) NOT NULL,
|
|
name VARCHAR(100) DEFAULT '',
|
|
condition DECIMAL(5,2) DEFAULT 100.00,
|
|
flights_count INTEGER DEFAULT 0,
|
|
purchase_price DECIMAL(15,2) NOT NULL,
|
|
seat_capacity INTEGER NOT NULL,
|
|
range_km INTEGER NOT NULL,
|
|
fuel_efficiency DECIMAL(5,2) DEFAULT 1.00,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Routen
|
|
CREATE TABLE IF NOT EXISTS routes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
from_airport_id INTEGER NOT NULL,
|
|
to_airport_id INTEGER NOT NULL,
|
|
distance_km INTEGER NOT NULL,
|
|
base_price DECIMAL(10,2) DEFAULT 100.00,
|
|
demand_level INTEGER DEFAULT 50,
|
|
is_active BOOLEAN DEFAULT 1,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (from_airport_id) REFERENCES airports(id),
|
|
FOREIGN KEY (to_airport_id) REFERENCES airports(id)
|
|
);
|
|
|
|
-- Flüge (durchgeführte Flüge)
|
|
CREATE TABLE IF NOT EXISTS flights (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
route_id INTEGER NOT NULL,
|
|
aircraft_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
departure_time DATETIME NOT NULL,
|
|
arrival_time DATETIME NOT NULL,
|
|
passengers INTEGER DEFAULT 0,
|
|
revenue DECIMAL(15,2) DEFAULT 0.00,
|
|
fuel_cost DECIMAL(15,2) DEFAULT 0.00,
|
|
profit DECIMAL(15,2) DEFAULT 0.00,
|
|
status VARCHAR(20) DEFAULT 'completed',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (route_id) REFERENCES routes(id),
|
|
FOREIGN KEY (aircraft_id) REFERENCES aircraft(id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Statistiken (tägliche Auswertungen)
|
|
CREATE TABLE IF NOT EXISTS daily_stats (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
date DATE NOT NULL,
|
|
total_flights INTEGER DEFAULT 0,
|
|
total_passengers INTEGER DEFAULT 0,
|
|
total_revenue DECIMAL(15,2) DEFAULT 0.00,
|
|
total_profit DECIMAL(15,2) DEFAULT 0.00,
|
|
fuel_cost DECIMAL(15,2) DEFAULT 0.00,
|
|
maintenance_cost DECIMAL(15,2) DEFAULT 0.00,
|
|
UNIQUE(user_id, date),
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Kredite
|
|
CREATE TABLE IF NOT EXISTS loans (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
principal DECIMAL(15,2) NOT NULL,
|
|
current_amount DECIMAL(15,2) NOT NULL,
|
|
interest_rate DECIMAL(5,4) DEFAULT 0.02,
|
|
daily_interest DECIMAL(15,2) DEFAULT 0.00,
|
|
days_outstanding INTEGER DEFAULT 0,
|
|
status VARCHAR(20) DEFAULT 'active',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
due_date DATE,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Transaktionen (alle Buchungen)
|
|
CREATE TABLE IF NOT EXISTS transactions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
type VARCHAR(50) NOT NULL,
|
|
category VARCHAR(50) NOT NULL,
|
|
amount DECIMAL(15,2) NOT NULL,
|
|
balance_after DECIMAL(15,2) NOT NULL,
|
|
description TEXT DEFAULT '',
|
|
reference_id INTEGER,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Wartungshistorie
|
|
CREATE TABLE IF NOT EXISTS maintenance_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
aircraft_id INTEGER NOT NULL,
|
|
user_id INTEGER NOT NULL,
|
|
maintenance_type VARCHAR(50) DEFAULT 'scheduled',
|
|
cost DECIMAL(15,2) NOT NULL,
|
|
condition_before DECIMAL(5,2) DEFAULT 100.00,
|
|
condition_after DECIMAL(5,2) DEFAULT 100.00,
|
|
notes TEXT DEFAULT '',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (aircraft_id) REFERENCES aircraft(id),
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Events (Werbeaktionen, Wettbewerbe)
|
|
CREATE TABLE IF NOT EXISTS events (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
title VARCHAR(200) NOT NULL,
|
|
description TEXT,
|
|
event_type VARCHAR(50) DEFAULT 'special',
|
|
bonus_multiplier DECIMAL(3,2) DEFAULT 1.00,
|
|
start_date DATETIME NOT NULL,
|
|
end_date DATETIME NOT NULL,
|
|
is_active BOOLEAN DEFAULT 1
|
|
);
|
|
|
|
-- Indexes für Performance
|
|
CREATE INDEX IF NOT EXISTS idx_routes_user ON routes(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_flights_user ON flights(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_flights_route ON flights(route_id);
|
|
CREATE INDEX IF NOT EXISTS idx_aircraft_user ON aircraft(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_airports_iata ON airports(iata_code);
|
|
|
|
-- Flugzeugtypen (Referenztabelle)
|
|
CREATE TABLE IF NOT EXISTS aircraft_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
code VARCHAR(20) UNIQUE NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
manufacturer VARCHAR(50) NOT NULL,
|
|
range_km INTEGER NOT NULL,
|
|
capacity INTEGER NOT NULL,
|
|
buy_price DECIMAL(15,2) NOT NULL,
|
|
crew_cost_per_tick DECIMAL(10,2) NOT NULL,
|
|
fuel_per_km DECIMAL(6,4) DEFAULT 0.1500,
|
|
maintenance_interval INTEGER DEFAULT 50,
|
|
description TEXT DEFAULT '',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
INSERT OR IGNORE INTO aircraft_types (code, name, manufacturer, range_km, capacity, buy_price, crew_cost_per_tick, fuel_per_km, maintenance_interval, description) VALUES
|
|
('dash8-100', 'Dash 8-100', 'Bombardier', 1500, 37, 500000.00, 200.00, 0.12, 50, 'Kleiner RegionalturboProp mit 37 Sitzen'),
|
|
('atr72-500', 'ATR 72-500', 'ATR', 1650, 70, 1500000.00, 300.00, 0.15, 50, 'Mittlerer RegionalturboProp mit 70 Sitzen'),
|
|
('a320-200', 'A320-200', 'Airbus', 5700, 150, 8000000.00, 600.00, 0.25, 50, 'Kurz-/Mittelstrecken-Jet mit 150 Sitzen'),
|
|
('b737-800', 'B737-800', 'Boeing', 5700, 162, 9500000.00, 650.00, 0.26, 50, 'Kurz-/Mittelstrecken-Jet mit 162 Sitzen'),
|
|
('b777-200', 'B777-200', 'Boeing', 12000, 300, 35000000.00, 1200.00, 0.55, 50, 'Langstrecken-Widebody mit 300 Sitzen'),
|
|
('a380-800', 'A380-800', 'Airbus', 15200, 525, 85000000.00, 2000.00, 0.80, 50, 'Doppelstöckiger Langstrecken-Airliner mit 525 Sitzen');
|