Shatteredvoid/database-schema.sql

-- Shattered Void MMO - Comprehensive Database Schema
-- This schema supports all game systems with flexibility for future expansion

-- ===== CORE SYSTEM TABLES =====

-- System configuration with hot-reloading support
CREATE TABLE system_config (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value JSONB NOT NULL,
    config_type VARCHAR(20) NOT NULL CHECK (config_type IN ('string', 'number', 'boolean', 'json', 'array')),
    description TEXT,
    requires_restart BOOLEAN DEFAULT false,
    is_public BOOLEAN DEFAULT false, -- Can be exposed to client
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_by INTEGER -- Will reference admin_users(id) after table creation
);

-- Game tick system with user grouping
CREATE TABLE game_tick_config (
    id SERIAL PRIMARY KEY,
    tick_interval_ms INTEGER NOT NULL DEFAULT 60000,
    user_groups_count INTEGER NOT NULL DEFAULT 10,
    max_retry_attempts INTEGER NOT NULL DEFAULT 5,
    bonus_tick_threshold INTEGER NOT NULL DEFAULT 3,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE game_tick_log (
    id BIGSERIAL PRIMARY KEY,
    tick_number BIGINT NOT NULL,
    user_group INTEGER NOT NULL,
    started_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP,
    status VARCHAR(20) NOT NULL CHECK (status IN ('running', 'completed', 'failed', 'retrying')),
    retry_count INTEGER DEFAULT 0,
    error_message TEXT,
    processed_players INTEGER DEFAULT 0,
    performance_metrics JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Event system configuration
CREATE TABLE event_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    trigger_type VARCHAR(20) NOT NULL CHECK (trigger_type IN ('admin', 'player', 'system', 'mixed')),
    is_active BOOLEAN DEFAULT true,
    config_schema JSONB, -- JSON schema for event configuration
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE event_instances (
    id BIGSERIAL PRIMARY KEY,
    event_type_id INTEGER NOT NULL REFERENCES event_types(id),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    config JSONB NOT NULL,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    status VARCHAR(20) NOT NULL CHECK (status IN ('scheduled', 'active', 'completed', 'cancelled')),
    created_by INTEGER, -- Will reference admin_users(id) after table creation
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Plugin system for extensibility
CREATE TABLE plugins (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    version VARCHAR(20) NOT NULL,
    description TEXT,
    plugin_type VARCHAR(50) NOT NULL, -- 'combat', 'event', 'resource', etc.
    is_active BOOLEAN DEFAULT false,
    config JSONB,
    dependencies JSONB, -- Array of required plugins
    hooks JSONB, -- Available hook points
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===== USER MANAGEMENT =====

-- Admin users with role-based access
CREATE TABLE admin_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) NOT NULL DEFAULT 'moderator',
    permissions JSONB, -- Specific permissions array
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Player accounts
CREATE TABLE players (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email_verified BOOLEAN DEFAULT false,
    email_verification_token VARCHAR(255),
    reset_password_token VARCHAR(255),
    reset_password_expires TIMESTAMP,
    user_group INTEGER NOT NULL CHECK (user_group >= 0 AND user_group < 10),
    is_active BOOLEAN DEFAULT true,
    is_banned BOOLEAN DEFAULT false,
    ban_reason TEXT,
    ban_expires TIMESTAMP,
    last_login TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Player preferences and game settings
CREATE TABLE player_settings (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    setting_key VARCHAR(100) NOT NULL,
    setting_value JSONB NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(player_id, setting_key)
);

-- WebSocket subscriptions for real-time updates
CREATE TABLE player_subscriptions (
    id BIGSERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    subscription_type VARCHAR(50) NOT NULL, -- 'colony', 'fleet', 'battle', 'event', etc.
    resource_id INTEGER, -- Specific resource ID (colony_id, fleet_id, etc.)
    filters JSONB, -- Additional filtering criteria
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP
);

-- ===== GALAXY & COLONIES =====

-- Planet types with generation rules
CREATE TABLE planet_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    base_resources JSONB NOT NULL, -- Starting resource deposits
    resource_modifiers JSONB, -- Production modifiers by resource type
    max_population INTEGER,
    special_features JSONB, -- Array of special features
    rarity_weight INTEGER DEFAULT 100, -- For random generation
    is_active BOOLEAN DEFAULT true
);

-- Galaxy sectors for organization
CREATE TABLE galaxy_sectors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    coordinates VARCHAR(10) UNIQUE NOT NULL, -- e.g., "A3"
    description TEXT,
    danger_level INTEGER DEFAULT 1 CHECK (danger_level BETWEEN 1 AND 10),
    special_rules JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Player colonies
CREATE TABLE colonies (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    coordinates VARCHAR(20) UNIQUE NOT NULL, -- Format: "A3-91-X"
    sector_id INTEGER REFERENCES galaxy_sectors(id),
    planet_type_id INTEGER NOT NULL REFERENCES planet_types(id),
    population INTEGER DEFAULT 0 CHECK (population >= 0),
    max_population INTEGER DEFAULT 1000,
    morale INTEGER DEFAULT 100 CHECK (morale BETWEEN 0 AND 100),
    loyalty INTEGER DEFAULT 100 CHECK (loyalty BETWEEN 0 AND 100),
    founded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Building types with upgrade paths
CREATE TABLE building_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    category VARCHAR(50) NOT NULL, -- 'production', 'military', 'research', 'infrastructure'
    max_level INTEGER DEFAULT 10,
    base_cost JSONB NOT NULL, -- Resource costs for level 1
    cost_multiplier DECIMAL(3,2) DEFAULT 1.5, -- Cost increase per level
    base_production JSONB, -- Resource production at level 1
    production_multiplier DECIMAL(3,2) DEFAULT 1.2, -- Production increase per level
    prerequisites JSONB, -- Required buildings/research
    special_effects JSONB, -- Special abilities or bonuses
    is_unique BOOLEAN DEFAULT false, -- Only one per colony
    is_active BOOLEAN DEFAULT true
);

-- Colony buildings
CREATE TABLE colony_buildings (
    id SERIAL PRIMARY KEY,
    colony_id INTEGER NOT NULL REFERENCES colonies(id) ON DELETE CASCADE,
    building_type_id INTEGER NOT NULL REFERENCES building_types(id),
    level INTEGER DEFAULT 1 CHECK (level > 0),
    health_percentage INTEGER DEFAULT 100 CHECK (health_percentage BETWEEN 0 AND 100),
    is_under_construction BOOLEAN DEFAULT false,
    construction_started TIMESTAMP,
    construction_completes TIMESTAMP,
    last_production TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(colony_id, building_type_id)
);

-- ===== RESOURCES & ECONOMY =====

-- Resource types
CREATE TABLE resource_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    category VARCHAR(30) NOT NULL, -- 'basic', 'advanced', 'rare', 'currency'
    max_storage INTEGER, -- NULL for unlimited
    decay_rate DECIMAL(5,4), -- Daily decay percentage
    trade_value DECIMAL(10,2), -- Base trade value
    is_tradeable BOOLEAN DEFAULT true,
    is_active BOOLEAN DEFAULT true
);

-- Player resource stockpiles
CREATE TABLE player_resources (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    resource_type_id INTEGER NOT NULL REFERENCES resource_types(id),
    amount BIGINT DEFAULT 0 CHECK (amount >= 0),
    storage_capacity BIGINT,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(player_id, resource_type_id)
);

-- Colony resource production/consumption tracking
CREATE TABLE colony_resource_production (
    id BIGSERIAL PRIMARY KEY,
    colony_id INTEGER NOT NULL REFERENCES colonies(id) ON DELETE CASCADE,
    resource_type_id INTEGER NOT NULL REFERENCES resource_types(id),
    production_rate INTEGER DEFAULT 0, -- Per hour
    consumption_rate INTEGER DEFAULT 0, -- Per hour
    current_stored BIGINT DEFAULT 0,
    storage_capacity BIGINT,
    last_calculated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(colony_id, resource_type_id)
);

-- Trade system
CREATE TABLE trade_routes (
    id SERIAL PRIMARY KEY,
    from_colony_id INTEGER NOT NULL REFERENCES colonies(id) ON DELETE CASCADE,
    to_colony_id INTEGER NOT NULL REFERENCES colonies(id) ON DELETE CASCADE,
    resource_type_id INTEGER NOT NULL REFERENCES resource_types(id),
    amount_per_trip INTEGER NOT NULL,
    price_per_unit DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'paused', 'cancelled')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT check_different_colonies CHECK (from_colony_id != to_colony_id)
);

-- ===== FLEET & COMBAT =====

-- Ship design system
CREATE TABLE ship_designs (
    id SERIAL PRIMARY KEY,
    player_id INTEGER REFERENCES players(id) ON DELETE CASCADE, -- NULL for standard designs
    name VARCHAR(100) NOT NULL,
    ship_class VARCHAR(50) NOT NULL, -- 'fighter', 'corvette', 'destroyer', 'cruiser', 'battleship'
    hull_type VARCHAR(50) NOT NULL,
    components JSONB NOT NULL, -- Weapon, shield, engine configurations
    stats JSONB NOT NULL, -- Calculated stats: hp, attack, defense, speed, etc.
    cost JSONB NOT NULL, -- Resource cost to build
    build_time INTEGER NOT NULL, -- In minutes
    is_public BOOLEAN DEFAULT false, -- Available to all players
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Player fleets
CREATE TABLE fleets (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    current_location VARCHAR(20) NOT NULL, -- Coordinates
    destination VARCHAR(20), -- If moving
    fleet_status VARCHAR(20) DEFAULT 'idle' CHECK (fleet_status IN ('idle', 'moving', 'in_combat', 'constructing', 'repairing')),
    movement_started TIMESTAMP,
    arrival_time TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Ships in fleets
CREATE TABLE fleet_ships (
    id SERIAL PRIMARY KEY,
    fleet_id INTEGER NOT NULL REFERENCES fleets(id) ON DELETE CASCADE,
    ship_design_id INTEGER NOT NULL REFERENCES ship_designs(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    health_percentage INTEGER DEFAULT 100 CHECK (health_percentage BETWEEN 0 AND 100),
    experience INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Combat system with plugin support
CREATE TABLE combat_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    plugin_name VARCHAR(100), -- References plugins table
    config JSONB,
    is_active BOOLEAN DEFAULT true
);

CREATE TABLE battles (
    id BIGSERIAL PRIMARY KEY,
    battle_type VARCHAR(50) NOT NULL,
    location VARCHAR(20) NOT NULL,
    combat_type_id INTEGER REFERENCES combat_types(id),
    participants JSONB NOT NULL, -- Array of fleet/player IDs
    status VARCHAR(20) DEFAULT 'preparing' CHECK (status IN ('preparing', 'active', 'completed', 'cancelled')),
    battle_data JSONB, -- Combat calculations and state
    result JSONB, -- Winner, casualties, loot, etc.
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===== RESEARCH & TECHNOLOGY =====

-- Technology tree
CREATE TABLE technologies (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    category VARCHAR(50) NOT NULL, -- 'military', 'industrial', 'social', 'exploration'
    tier INTEGER NOT NULL DEFAULT 1 CHECK (tier > 0),
    prerequisites JSONB, -- Array of required technology IDs
    research_cost JSONB NOT NULL, -- Resource costs
    research_time INTEGER NOT NULL, -- In minutes
    effects JSONB, -- Bonuses, unlocks, etc.
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Player research progress
CREATE TABLE player_research (
    id SERIAL PRIMARY KEY,
    player_id INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    technology_id INTEGER NOT NULL REFERENCES technologies(id),
    status VARCHAR(20) DEFAULT 'available' CHECK (status IN ('unavailable', 'available', 'researching', 'completed')),
    progress INTEGER DEFAULT 0 CHECK (progress >= 0),
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    UNIQUE(player_id, technology_id)
);

-- Research labs and facilities
CREATE TABLE research_facilities (
    id SERIAL PRIMARY KEY,
    colony_id INTEGER NOT NULL REFERENCES colonies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    facility_type VARCHAR(50) NOT NULL,
    research_bonus DECIMAL(3,2) DEFAULT 1.0, -- Multiplier for research speed
    specialization JSONB, -- Categories this facility is good at
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===== FACTIONS & DIPLOMACY =====

-- Player factions/alliances
CREATE TABLE factions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    tag VARCHAR(10) UNIQUE NOT NULL, -- Short identifier
    leader_id INTEGER REFERENCES players(id),
    faction_type VARCHAR(20) DEFAULT 'alliance' CHECK (faction_type IN ('alliance', 'corporation', 'empire')),
    is_recruiting BOOLEAN DEFAULT true,
    requirements JSONB, -- Join requirements
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Faction membership
CREATE TABLE faction_members (
    id SERIAL PRIMARY KEY,
    faction_id INTEGER NOT NULL REFERENCES factions(id) ON DELETE CASCADE,
    player_id INTEGER NOT NULL REFERENCES players(id) ON DELETE CASCADE,
    rank VARCHAR(50) DEFAULT 'member',
    permissions JSONB, -- What they can do
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    contribution_score INTEGER DEFAULT 0,
    UNIQUE(faction_id, player_id)
);

-- Diplomatic relations
CREATE TABLE diplomatic_relations (
    id SERIAL PRIMARY KEY,
    faction_a_id INTEGER NOT NULL REFERENCES factions(id) ON DELETE CASCADE,
    faction_b_id INTEGER NOT NULL REFERENCES factions(id) ON DELETE CASCADE,
    relation_type VARCHAR(20) NOT NULL CHECK (relation_type IN ('allied', 'friendly', 'neutral', 'hostile', 'war')),
    established_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    established_by INTEGER REFERENCES players(id),
    CONSTRAINT check_different_factions CHECK (faction_a_id != faction_b_id),
    UNIQUE(faction_a_id, faction_b_id)
);

-- ===== AUDIT & STATISTICS =====

-- Comprehensive audit log
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(50) NOT NULL, -- 'player', 'colony', 'fleet', 'battle', etc.
    entity_id INTEGER NOT NULL,
    action VARCHAR(100) NOT NULL,
    actor_type VARCHAR(20) NOT NULL CHECK (actor_type IN ('player', 'admin', 'system')),
    actor_id INTEGER,
    changes JSONB, -- Before/after values
    metadata JSONB, -- Additional context
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Data retention policy
CREATE TABLE data_retention_policies (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    retention_days INTEGER NOT NULL DEFAULT 30,
    archive_before_delete BOOLEAN DEFAULT true,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(table_name)
);

-- Game statistics for analysis
CREATE TABLE game_statistics (
    id BIGSERIAL PRIMARY KEY,
    stat_type VARCHAR(100) NOT NULL,
    stat_key VARCHAR(200) NOT NULL,
    stat_value DECIMAL(15,4) NOT NULL,
    dimensions JSONB, -- Additional grouping dimensions
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Performance metrics
CREATE TABLE performance_metrics (
    id BIGSERIAL PRIMARY KEY,
    metric_name VARCHAR(100) NOT NULL,
    metric_value DECIMAL(10,4) NOT NULL,
    tags JSONB, -- Additional metadata
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===== INDEXES FOR PERFORMANCE =====

-- User management indexes
CREATE INDEX idx_players_user_group ON players(user_group);
CREATE INDEX idx_players_email ON players(email);
CREATE INDEX idx_players_username ON players(username);
CREATE INDEX idx_player_subscriptions_player_id ON player_subscriptions(player_id);
CREATE INDEX idx_player_subscriptions_type ON player_subscriptions(subscription_type);
CREATE INDEX idx_player_subscriptions_expires ON player_subscriptions(expires_at);

-- Colony and building indexes
CREATE INDEX idx_colonies_player_id ON colonies(player_id);
CREATE INDEX idx_colonies_coordinates ON colonies(coordinates);
CREATE INDEX idx_colonies_sector ON colonies(sector_id);
CREATE INDEX idx_colony_buildings_colony_id ON colony_buildings(colony_id);
CREATE INDEX idx_colony_buildings_construction ON colony_buildings(construction_completes) WHERE is_under_construction = true;

-- Resource indexes
CREATE INDEX idx_player_resources_player_id ON player_resources(player_id);
CREATE INDEX idx_colony_production_colony_id ON colony_resource_production(colony_id);
CREATE INDEX idx_trade_routes_from_colony ON trade_routes(from_colony_id);
CREATE INDEX idx_trade_routes_to_colony ON trade_routes(to_colony_id);

-- Fleet and combat indexes
CREATE INDEX idx_ship_designs_player_id ON ship_designs(player_id);
CREATE INDEX idx_ship_designs_class ON ship_designs(ship_class);
CREATE INDEX idx_fleets_player_id ON fleets(player_id);
CREATE INDEX idx_fleets_location ON fleets(current_location);
CREATE INDEX idx_fleets_arrival ON fleets(arrival_time) WHERE arrival_time IS NOT NULL;
CREATE INDEX idx_fleet_ships_fleet_id ON fleet_ships(fleet_id);
CREATE INDEX idx_battles_location ON battles(location);
CREATE INDEX idx_battles_status ON battles(status);
CREATE INDEX idx_battles_completed ON battles(completed_at);

-- Research indexes
CREATE INDEX idx_player_research_player_id ON player_research(player_id);
CREATE INDEX idx_player_research_status ON player_research(status);
CREATE INDEX idx_research_facilities_colony_id ON research_facilities(colony_id);

-- Faction indexes
CREATE INDEX idx_factions_leader_id ON factions(leader_id);
CREATE INDEX idx_faction_members_faction_id ON faction_members(faction_id);
CREATE INDEX idx_faction_members_player_id ON faction_members(player_id);
CREATE INDEX idx_diplomatic_relations_faction_a ON diplomatic_relations(faction_a_id);
CREATE INDEX idx_diplomatic_relations_faction_b ON diplomatic_relations(faction_b_id);

-- Audit and statistics indexes
CREATE INDEX idx_audit_log_entity ON audit_log(entity_type, entity_id);
CREATE INDEX idx_audit_log_actor ON audit_log(actor_type, actor_id);
CREATE INDEX idx_audit_log_created_at ON audit_log(created_at);
CREATE INDEX idx_audit_log_action ON audit_log(action);
CREATE INDEX idx_game_statistics_type_key ON game_statistics(stat_type, stat_key);
CREATE INDEX idx_game_statistics_recorded_at ON game_statistics(recorded_at);
CREATE INDEX idx_performance_metrics_name ON performance_metrics(metric_name);
CREATE INDEX idx_performance_metrics_recorded_at ON performance_metrics(recorded_at);

-- Game tick indexes
CREATE INDEX idx_game_tick_log_tick_number ON game_tick_log(tick_number);
CREATE INDEX idx_game_tick_log_user_group ON game_tick_log(user_group);
CREATE INDEX idx_game_tick_log_status ON game_tick_log(status);

-- Event system indexes
CREATE INDEX idx_event_instances_event_type ON event_instances(event_type_id);
CREATE INDEX idx_event_instances_status ON event_instances(status);
CREATE INDEX idx_event_instances_start_time ON event_instances(start_time);

-- ===== ADD FOREIGN KEY CONSTRAINTS =====

-- Add foreign key constraints that couldn't be added during table creation
ALTER TABLE system_config ADD CONSTRAINT fk_system_config_admin FOREIGN KEY (updated_by) REFERENCES admin_users(id);
ALTER TABLE event_instances ADD CONSTRAINT fk_event_instances_admin FOREIGN KEY (created_by) REFERENCES admin_users(id);

-- ===== TRIGGERS FOR AUTOMATIC TIMESTAMPS =====

-- Function to automatically update updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Apply updated_at triggers to relevant tables
CREATE TRIGGER update_system_config_updated_at BEFORE UPDATE ON system_config FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_game_tick_config_updated_at BEFORE UPDATE ON game_tick_config FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_event_instances_updated_at BEFORE UPDATE ON event_instances FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_plugins_updated_at BEFORE UPDATE ON plugins FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_admin_users_updated_at BEFORE UPDATE ON admin_users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_players_updated_at BEFORE UPDATE ON players FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_player_settings_updated_at BEFORE UPDATE ON player_settings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_colonies_updated_at BEFORE UPDATE ON colonies FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_colony_buildings_updated_at BEFORE UPDATE ON colony_buildings FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_ship_designs_updated_at BEFORE UPDATE ON ship_designs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ===== INITIAL DATA =====

-- Insert initial system configuration
INSERT INTO system_config (config_key, config_value, config_type, description, is_public) VALUES
('game_tick_interval_ms', '60000', 'number', 'Game tick interval in milliseconds', false),
('max_user_groups', '10', 'number', 'Maximum number of user groups for tick processing', false),
('max_retry_attempts', '5', 'number', 'Maximum retry attempts for failed ticks', false),
('data_retention_days', '30', 'number', 'Default data retention period in days', false),
('max_colonies_per_player', '10', 'number', 'Maximum colonies a player can own', true),
('starting_resources', '{"scrap": 1000, "energy": 500, "data_cores": 0, "rare_elements": 0}', 'json', 'Starting resources for new players', false),
('websocket_ping_interval', '30000', 'number', 'WebSocket ping interval in milliseconds', false);

-- Insert resource types
INSERT INTO resource_types (name, description, category, is_tradeable) VALUES
('scrap', 'Basic salvaged materials from the ruins of civilization', 'basic', true),
('energy', 'Power cells and energy storage units', 'basic', true),
('data_cores', 'Advanced information storage containing pre-collapse knowledge', 'advanced', true),
('rare_elements', 'Exotic materials required for advanced technologies', 'rare', true);

-- Insert initial planet types
INSERT INTO planet_types (name, description, base_resources, resource_modifiers, max_population, rarity_weight) VALUES
('Terran', 'Earth-like worlds with balanced resources', '{"scrap": 500, "energy": 300}', '{"scrap": 1.0, "energy": 1.0, "data_cores": 1.0, "rare_elements": 1.0}', 10000, 100),
('Industrial', 'Former manufacturing worlds rich in salvageable materials', '{"scrap": 1000, "energy": 200}', '{"scrap": 1.5, "energy": 0.8, "data_cores": 1.2, "rare_elements": 0.9}', 8000, 80),
('Research', 'Academic worlds with data archives and laboratories', '{"scrap": 300, "energy": 400, "data_cores": 100}', '{"scrap": 0.7, "energy": 1.1, "data_cores": 2.0, "rare_elements": 1.3}', 6000, 60),
('Mining', 'Resource extraction worlds with rare element deposits', '{"scrap": 400, "energy": 250, "rare_elements": 20}', '{"scrap": 1.1, "energy": 0.9, "data_cores": 0.8, "rare_elements": 2.5}', 7000, 70),
('Fortress', 'Heavily fortified military installations', '{"scrap": 600, "energy": 500}', '{"scrap": 1.2, "energy": 1.3, "data_cores": 1.1, "rare_elements": 1.1}', 5000, 40),
('Derelict', 'Abandoned worlds with scattered ruins and mysteries', '{"scrap": 200, "energy": 100, "data_cores": 50, "rare_elements": 10}', '{"scrap": 0.8, "energy": 0.6, "data_cores": 1.5, "rare_elements": 1.8}', 3000, 30);

-- Insert initial building types
INSERT INTO building_types (name, description, category, max_level, base_cost, base_production, special_effects) VALUES
('Command Center', 'Central administration building that coordinates colony operations', 'infrastructure', 10, '{"scrap": 100, "energy": 50}', '{}', '{"colony_slots": 1}'),
('Salvage Yard', 'Processes scrap metal and salvageable materials', 'production', 10, '{"scrap": 50, "energy": 25}', '{"scrap": 10}', '{}'),
('Power Plant', 'Generates energy for colony operations', 'production', 10, '{"scrap": 75, "energy": 0}', '{"energy": 8}', '{}'),
('Research Lab', 'Conducts technological research and development', 'research', 10, '{"scrap": 100, "energy": 75, "data_cores": 10}', '{}', '{"research_speed": 0.1}'),
('Housing Complex', 'Provides living space for colony population', 'infrastructure', 15, '{"scrap": 60, "energy": 30}', '{}', '{"population_capacity": 100}'),
('Defense Grid', 'Automated defense systems protecting the colony', 'military', 10, '{"scrap": 150, "energy": 100, "rare_elements": 5}', '{}', '{"defense_rating": 50}'),
('Data Archive', 'Stores and processes information from data cores', 'research', 8, '{"scrap": 80, "energy": 60, "data_cores": 5}', '{"data_cores": 2}', '{"research_bonus": 0.05}'),
('Mining Facility', 'Extracts rare elements from planetary deposits', 'production', 12, '{"scrap": 200, "energy": 150, "data_cores": 20}', '{"rare_elements": 1}', '{}');

-- Insert initial game tick configuration
INSERT INTO game_tick_config (tick_interval_ms, user_groups_count, max_retry_attempts, bonus_tick_threshold) VALUES
(60000, 10, 5, 3);

-- Insert basic combat type
INSERT INTO combat_types (name, description, config) VALUES
('instant_resolution', 'Basic instant combat resolution with detailed logs', '{"calculate_experience": true, "detailed_logs": true}');

-- Insert initial event types
INSERT INTO event_types (name, description, trigger_type, config_schema) VALUES
('galaxy_crisis', 'Major galaxy-wide crisis events', 'admin', '{"duration_hours": {"type": "number", "min": 1, "max": 168}}'),
('discovery_event', 'Random discovery events triggered by exploration', 'player', '{"discovery_type": {"type": "string", "enum": ["artifact", "technology", "resource"]}}'),
('faction_war', 'Large-scale conflicts between factions', 'mixed', '{"participating_factions": {"type": "array", "items": {"type": "number"}}}');

-- Insert data retention policies
INSERT INTO data_retention_policies (table_name, retention_days, archive_before_delete) VALUES
('audit_log', 90, true),
('game_tick_log', 7, false),
('battles', 30, true),
('performance_metrics', 14, false),
('game_statistics', 365, true);

-- Insert initial galaxy sectors
INSERT INTO galaxy_sectors (name, coordinates, description, danger_level) VALUES
('Core Worlds', 'A1', 'The former heart of galactic civilization', 3),
('Industrial Belt', 'B2', 'Manufacturing and resource processing sector', 4),
('Frontier Region', 'C3', 'Outer rim territories with unexplored systems', 6),
('Research Zone', 'D4', 'Former academic and scientific installations', 2),
('Contested Space', 'E5', 'Battlegrounds of the great collapse', 8),
('Dead Zone', 'F6', 'Heavily damaged region with dangerous anomalies', 9);

-- Create initial admin user (password should be changed immediately)
INSERT INTO admin_users (username, email, password_hash, role, permissions) VALUES
('admin', 'admin@shatteredvoid.game', '$2b$10$example.hash.change.immediately', 'administrator', '["all"]');

-- Insert initial plugin for basic combat
INSERT INTO plugins (name, version, description, plugin_type, is_active, config, hooks) VALUES
('basic_combat', '1.0.0', 'Basic instant combat resolution system', 'combat', true, '{"damage_variance": 0.1, "experience_gain": 1.0}', '["pre_combat", "post_combat", "damage_calculation"]');