-- Sci-Fi MMO Database Schema
-- Uses MySQL/MariaDB

SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- USERS & AUTHENTICATION
-- =====================================================

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE characters (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(50) UNIQUE NOT NULL,
    credits BIGINT DEFAULT 10000,
    current_ship_id INT NULL,
    current_system_id INT NOT NULL,
    faction_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- =====================================================
-- GALAXY & NAVIGATION
-- =====================================================

CREATE TABLE solar_systems (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    coord_x DOUBLE NOT NULL,
    coord_y DOUBLE NOT NULL,
    coord_z DOUBLE NOT NULL,
    security_level DECIMAL(3,2) DEFAULT 0.5, -- 1.0 = safe, 0.0 = lawless
    star_type ENUM('O', 'B', 'A', 'F', 'G', 'K', 'M', 'Neutron', 'BlackHole') DEFAULT 'G',
    seed INT NOT NULL, -- For procedural regeneration
    is_discovered BOOLEAN DEFAULT FALSE,
    discovered_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_coords (coord_x, coord_y, coord_z)
);

CREATE TABLE planets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    system_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    planet_type ENUM('Rocky', 'Gas Giant', 'Ice', 'Lava', 'Ocean', 'Desert', 'Terran', 'Barren') NOT NULL,
    orbit_distance DOUBLE NOT NULL, -- AU from star
    orbit_angle DOUBLE DEFAULT 0, -- Current position in orbit
    size_class ENUM('Dwarf', 'Small', 'Medium', 'Large', 'Giant') NOT NULL,
    has_rings BOOLEAN DEFAULT FALSE,
    resource_richness DECIMAL(3,2) DEFAULT 0.5,
    FOREIGN KEY (system_id) REFERENCES solar_systems(id) ON DELETE CASCADE
);

CREATE TABLE stargates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_system_id INT NOT NULL,
    to_system_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    travel_time INT DEFAULT 30, -- seconds
    FOREIGN KEY (from_system_id) REFERENCES solar_systems(id) ON DELETE CASCADE,
    FOREIGN KEY (to_system_id) REFERENCES solar_systems(id) ON DELETE CASCADE,
    UNIQUE KEY unique_gate (from_system_id, to_system_id)
);

CREATE TABLE asteroid_belts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    system_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    orbit_distance DOUBLE NOT NULL,
    density DECIMAL(3,2) DEFAULT 0.5, -- How many asteroids
    primary_ore ENUM('Veldspar', 'Scordite', 'Pyroxeres', 'Plagioclase', 'Omber', 'Kernite', 'Jaspet', 'Hemorphite', 'Hedbergite', 'Gneiss', 'Dark Ochre', 'Spodumain', 'Crokite', 'Bistot', 'Arkonor', 'Mercoxit') NOT NULL,
    FOREIGN KEY (system_id) REFERENCES solar_systems(id) ON DELETE CASCADE
);

-- =====================================================
-- SHIPS & MODULES
-- =====================================================

CREATE TABLE ship_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    class ENUM('Shuttle', 'Frigate', 'Destroyer', 'Cruiser', 'Battlecruiser', 'Battleship', 'Carrier', 'Dreadnought', 'Titan', 'Industrial', 'Mining Barge', 'Freighter') NOT NULL,
    hull_hp INT NOT NULL,
    shield_hp INT NOT NULL,
    armor_hp INT NOT NULL,
    cargo_capacity INT NOT NULL,
    high_slots INT NOT NULL,
    mid_slots INT NOT NULL,
    low_slots INT NOT NULL,
    powergrid INT NOT NULL,
    cpu INT NOT NULL,
    max_velocity INT NOT NULL, -- m/s
    warp_speed DECIMAL(4,2) NOT NULL, -- AU/s
    base_price BIGINT NOT NULL,
    description TEXT
);

CREATE TABLE ships (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ship_type_id INT NOT NULL,
    owner_id INT NULL, -- NULL = NPC
    name VARCHAR(100),
    current_system_id INT NOT NULL,
    current_planet_id INT NULL, -- If orbiting a planet
    position_x DOUBLE DEFAULT 0,
    position_y DOUBLE DEFAULT 0,
    position_z DOUBLE DEFAULT 0,
    hull_hp_current INT NOT NULL,
    shield_hp_current INT NOT NULL,
    armor_hp_current INT NOT NULL,
    is_docked BOOLEAN DEFAULT FALSE,
    docked_at_base_id INT NULL,
    is_warping BOOLEAN DEFAULT FALSE,
    warp_destination_id INT NULL,
    warp_arrival_time TIMESTAMP NULL,
    is_in_combat BOOLEAN DEFAULT FALSE,
    target_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ship_type_id) REFERENCES ship_types(id),
    FOREIGN KEY (owner_id) REFERENCES characters(id) ON DELETE SET NULL,
    FOREIGN KEY (current_system_id) REFERENCES solar_systems(id)
);

CREATE TABLE module_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slot_type ENUM('High', 'Mid', 'Low', 'Rig') NOT NULL,
    category ENUM('Weapon', 'Mining', 'Shield', 'Armor', 'Propulsion', 'Electronic', 'Engineering', 'Drone') NOT NULL,
    powergrid_usage INT NOT NULL,
    cpu_usage INT NOT NULL,
    -- Weapon stats
    damage_per_cycle INT DEFAULT 0,
    damage_type ENUM('EM', 'Thermal', 'Kinetic', 'Explosive') NULL,
    cycle_time DECIMAL(5,2) DEFAULT 0, -- seconds
    optimal_range INT DEFAULT 0, -- meters
    -- Mining stats
    mining_yield INT DEFAULT 0, -- ore units per cycle
    -- Tank stats
    hp_bonus INT DEFAULT 0,
    resistance_bonus DECIMAL(4,2) DEFAULT 0,
    -- Prop stats
    velocity_bonus DECIMAL(4,2) DEFAULT 0,
    base_price BIGINT NOT NULL,
    description TEXT
);

CREATE TABLE fitted_modules (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ship_id INT NOT NULL,
    module_type_id INT NOT NULL,
    slot_number INT NOT NULL,
    is_active BOOLEAN DEFAULT FALSE,
    is_overloaded BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (ship_id) REFERENCES ships(id) ON DELETE CASCADE,
    FOREIGN KEY (module_type_id) REFERENCES module_types(id),
    UNIQUE KEY unique_slot (ship_id, slot_number)
);

-- =====================================================
-- INVENTORY & RESOURCES
-- =====================================================

CREATE TABLE item_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category ENUM('Ore', 'Mineral', 'Component', 'Ship', 'Module', 'Blueprint', 'Commodity', 'Salvage') NOT NULL,
    volume DECIMAL(10,2) NOT NULL, -- m3
    base_price BIGINT DEFAULT 0,
    description TEXT
);

CREATE TABLE inventory (
    id INT PRIMARY KEY AUTO_INCREMENT,
    item_type_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    location_type ENUM('Ship', 'Base', 'Station', 'Character') NOT NULL,
    location_id INT NOT NULL,
    FOREIGN KEY (item_type_id) REFERENCES item_types(id)
);

-- =====================================================
-- PLANETARY BASES & STRUCTURES
-- =====================================================

CREATE TABLE planetary_bases (
    id INT PRIMARY KEY AUTO_INCREMENT,
    planet_id INT NOT NULL,
    owner_id INT NOT NULL,
    faction_id INT NULL,
    name VARCHAR(100) NOT NULL,
    base_type ENUM('Outpost', 'Colony', 'Industrial Complex', 'Military Fortress', 'Research Station') NOT NULL,
    shield_hp INT DEFAULT 10000,
    shield_hp_current INT DEFAULT 10000,
    armor_hp INT DEFAULT 50000,
    armor_hp_current INT DEFAULT 50000,
    is_under_attack BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (planet_id) REFERENCES planets(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_id) REFERENCES characters(id)
);

CREATE TABLE defense_systems (
    id INT PRIMARY KEY AUTO_INCREMENT,
    base_id INT NOT NULL,
    defense_type ENUM('Point Defense', 'Missile Battery', 'Railgun', 'Laser Array', 'Shield Generator', 'ECM') NOT NULL,
    level INT DEFAULT 1,
    is_active BOOLEAN DEFAULT TRUE,
    damage_output INT DEFAULT 100,
    range_km INT DEFAULT 1000,
    FOREIGN KEY (base_id) REFERENCES planetary_bases(id) ON DELETE CASCADE
);

-- =====================================================
-- FACTORIES & MANUFACTURING
-- =====================================================

CREATE TABLE factories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    base_id INT NOT NULL,
    factory_type ENUM('Ship Yard', 'Module Factory', 'Refinery', 'Component Assembly') NOT NULL,
    efficiency DECIMAL(3,2) DEFAULT 1.0,
    slots_total INT DEFAULT 5,
    slots_used INT DEFAULT 0,
    FOREIGN KEY (base_id) REFERENCES planetary_bases(id) ON DELETE CASCADE
);

CREATE TABLE blueprints (
    id INT PRIMARY KEY AUTO_INCREMENT,
    output_type ENUM('Ship', 'Module', 'Component') NOT NULL,
    output_id INT NOT NULL, -- References ship_types or module_types
    name VARCHAR(100) NOT NULL,
    base_build_time INT NOT NULL, -- seconds
    material_efficiency DECIMAL(3,2) DEFAULT 1.0,
    time_efficiency DECIMAL(3,2) DEFAULT 1.0
);

CREATE TABLE blueprint_materials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    blueprint_id INT NOT NULL,
    item_type_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (blueprint_id) REFERENCES blueprints(id) ON DELETE CASCADE,
    FOREIGN KEY (item_type_id) REFERENCES item_types(id)
);

CREATE TABLE manufacturing_jobs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    factory_id INT NOT NULL,
    blueprint_id INT NOT NULL,
    owner_id INT NOT NULL,
    runs INT DEFAULT 1,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NOT NULL,
    status ENUM('Pending', 'Running', 'Completed', 'Cancelled') DEFAULT 'Running',
    FOREIGN KEY (factory_id) REFERENCES factories(id) ON DELETE CASCADE,
    FOREIGN KEY (blueprint_id) REFERENCES blueprints(id),
    FOREIGN KEY (owner_id) REFERENCES characters(id)
);

-- =====================================================
-- PLAYER FACTIONS
-- =====================================================

CREATE TABLE factions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    ticker VARCHAR(5) UNIQUE NOT NULL, -- Short tag like [CORP]
    description TEXT,
    leader_id INT NOT NULL,
    credits BIGINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (leader_id) REFERENCES characters(id)
);

CREATE TABLE faction_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    faction_id INT NOT NULL,
    character_id INT NOT NULL,
    role ENUM('Leader', 'Director', 'Manager', 'Member', 'Recruit') DEFAULT 'Recruit',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (faction_id) REFERENCES factions(id) ON DELETE CASCADE,
    FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE,
    UNIQUE KEY unique_membership (faction_id, character_id)
);

CREATE TABLE faction_relations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    faction_id INT NOT NULL,
    target_faction_id INT NOT NULL,
    standing DECIMAL(4,2) DEFAULT 0, -- -10 to +10
    status ENUM('War', 'Hostile', 'Neutral', 'Friendly', 'Allied') DEFAULT 'Neutral',
    FOREIGN KEY (faction_id) REFERENCES factions(id) ON DELETE CASCADE,
    FOREIGN KEY (target_faction_id) REFERENCES factions(id) ON DELETE CASCADE
);

-- =====================================================
-- ECONOMY & MARKET
-- =====================================================

CREATE TABLE market_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    item_type_id INT NOT NULL,
    character_id INT NOT NULL,
    system_id INT NOT NULL,
    order_type ENUM('Buy', 'Sell') NOT NULL,
    price BIGINT NOT NULL,
    quantity INT NOT NULL,
    quantity_remaining INT NOT NULL,
    min_volume INT DEFAULT 1,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_type_id) REFERENCES item_types(id),
    FOREIGN KEY (character_id) REFERENCES characters(id),
    FOREIGN KEY (system_id) REFERENCES solar_systems(id),
    INDEX idx_item_system (item_type_id, system_id)
);

CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    buyer_id INT,
    seller_id INT,
    item_type_id INT NOT NULL,
    quantity INT NOT NULL,
    price_per_unit BIGINT NOT NULL,
    total_price BIGINT NOT NULL,
    system_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (item_type_id) REFERENCES item_types(id)
);

-- =====================================================
-- COMBAT & NPC
-- =====================================================

CREATE TABLE npc_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    ship_type_id INT NOT NULL,
    faction_name VARCHAR(50), -- 'Pirates', 'Drones', etc.
    difficulty ENUM('Easy', 'Medium', 'Hard', 'Elite', 'Boss') NOT NULL,
    bounty BIGINT DEFAULT 0,
    loot_table_id INT NULL,
    ai_behavior ENUM('Passive', 'Defensive', 'Aggressive', 'Patrol') DEFAULT 'Aggressive',
    FOREIGN KEY (ship_type_id) REFERENCES ship_types(id)
);

CREATE TABLE combat_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    attacker_type ENUM('Player', 'NPC') NOT NULL,
    attacker_id INT NOT NULL,
    defender_type ENUM('Player', 'NPC') NOT NULL,
    defender_id INT NOT NULL,
    damage_dealt INT NOT NULL,
    damage_type ENUM('EM', 'Thermal', 'Kinetic', 'Explosive') NOT NULL,
    system_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =====================================================
-- EXPLORATION
-- =====================================================

CREATE TABLE anomalies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    system_id INT NOT NULL,
    anomaly_type ENUM('Combat Site', 'Mining Site', 'Data Site', 'Relic Site', 'Wormhole', 'Unknown') NOT NULL,
    difficulty ENUM('Easy', 'Medium', 'Hard', 'Very Hard') NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_scanned BOOLEAN DEFAULT FALSE,
    scan_difficulty DECIMAL(3,2) DEFAULT 0.5,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (system_id) REFERENCES solar_systems(id) ON DELETE CASCADE
);

CREATE TABLE exploration_sites (
    id INT PRIMARY KEY AUTO_INCREMENT,
    anomaly_id INT NOT NULL,
    site_data JSON, -- Procedural content stored as JSON
    is_completed BOOLEAN DEFAULT FALSE,
    completed_by INT NULL,
    FOREIGN KEY (anomaly_id) REFERENCES anomalies(id) ON DELETE CASCADE
);

-- =====================================================
-- GAME STATE & ACTIONS
-- =====================================================

CREATE TABLE action_queue (
    id INT PRIMARY KEY AUTO_INCREMENT,
    character_id INT NOT NULL,
    action_type ENUM('Warp', 'Jump', 'Dock', 'Undock', 'Attack', 'Mine', 'Repair', 'Manufacture', 'Trade') NOT NULL,
    target_type VARCHAR(50) NULL,
    target_id INT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completes_at TIMESTAMP NOT NULL,
    status ENUM('Queued', 'Processing', 'Completed', 'Failed', 'Cancelled') DEFAULT 'Queued',
    result_data JSON NULL,
    FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE
);

CREATE TABLE game_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    character_id INT NOT NULL,
    log_type ENUM('Combat', 'Trade', 'Travel', 'Mining', 'Manufacturing', 'Faction', 'System') NOT NULL,
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE
);

SET FOREIGN_KEY_CHECKS = 1;
