-- Galaktik İmparatorluk Oyunu Veritabanı Yapısı
-- MySQL/MariaDB için

CREATE DATABASE IF NOT EXISTS galaktik_imparatorluk CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE galaktik_imparatorluk;

-- Kullanıcılar Tablosu
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    points INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    is_active TINYINT(1) DEFAULT 1,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gezegenler Tablosu
CREATE TABLE IF NOT EXISTS planets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL DEFAULT 'Ana Gezegen',
    galaxy INT NOT NULL DEFAULT 1,
    `system` INT NOT NULL DEFAULT 1,
    `planet` INT NOT NULL DEFAULT 1,
    temperature_min INT DEFAULT -20,
    temperature_max INT DEFAULT 20,
    fields_used INT DEFAULT 0,
    fields_max INT DEFAULT 200,
    population INT DEFAULT 0,
    production_bonus DECIMAL(5,2) DEFAULT 100.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_coords (galaxy, `system`, `planet`),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Kaynaklar Tablosu
CREATE TABLE IF NOT EXISTS resources (
    id INT AUTO_INCREMENT PRIMARY KEY,
    planet_id INT NOT NULL,
    metal DECIMAL(15,2) DEFAULT 500.00,
    crystal DECIMAL(15,2) DEFAULT 500.00,
    deuterium DECIMAL(15,2) DEFAULT 0.00,
    energy_used INT DEFAULT 0,
    energy_max INT DEFAULT 0,
    metal_production DECIMAL(10,2) DEFAULT 0.00,
    crystal_production DECIMAL(10,2) DEFAULT 0.00,
    deuterium_production DECIMAL(10,2) DEFAULT 0.00,
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (planet_id) REFERENCES planets(id) ON DELETE CASCADE,
    UNIQUE KEY unique_planet (planet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Binalar Tablosu
CREATE TABLE IF NOT EXISTS buildings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    planet_id INT NOT NULL,
    building_type VARCHAR(50) NOT NULL,
    level INT DEFAULT 0,
    construction_start TIMESTAMP NULL,
    construction_end TIMESTAMP NULL,
    FOREIGN KEY (planet_id) REFERENCES planets(id) ON DELETE CASCADE,
    UNIQUE KEY unique_building (planet_id, building_type),
    INDEX idx_planet_id (planet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Araştırmalar Tablosu
CREATE TABLE IF NOT EXISTS research (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    research_type VARCHAR(50) NOT NULL,
    level INT DEFAULT 0,
    research_start TIMESTAMP NULL,
    research_end TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_research (user_id, research_type),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Filo Tablosu
CREATE TABLE IF NOT EXISTS fleet (
    id INT AUTO_INCREMENT PRIMARY KEY,
    planet_id INT NOT NULL,
    ship_type VARCHAR(50) NOT NULL,
    quantity INT DEFAULT 0,
    FOREIGN KEY (planet_id) REFERENCES planets(id) ON DELETE CASCADE,
    UNIQUE KEY unique_ship (planet_id, ship_type),
    INDEX idx_planet_id (planet_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Filo Hareketleri Tablosu
CREATE TABLE IF NOT EXISTS fleet_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    from_planet_id INT NOT NULL,
    to_planet_id INT,
    mission_type VARCHAR(20) NOT NULL,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    arrival_time TIMESTAMP NOT NULL,
    return_time TIMESTAMP NULL,
    metal DECIMAL(15,2) DEFAULT 0,
    crystal DECIMAL(15,2) DEFAULT 0,
    deuterium DECIMAL(15,2) DEFAULT 0,
    status VARCHAR(20) DEFAULT 'flying',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (from_planet_id) REFERENCES planets(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mesajlar Tablosu
CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_user_id INT,
    to_user_id INT NOT NULL,
    subject VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (to_user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_to_user (to_user_id),
    INDEX idx_is_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bina Tipleri ve Özellikleri (Referans Tablosu)
CREATE TABLE IF NOT EXISTS building_types (
    building_type VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    base_metal_cost DECIMAL(15,2) DEFAULT 0,
    base_crystal_cost DECIMAL(15,2) DEFAULT 0,
    base_deuterium_cost DECIMAL(15,2) DEFAULT 0,
    cost_multiplier DECIMAL(5,2) DEFAULT 1.5,
    max_level INT DEFAULT 100
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan Bina Tipleri
INSERT INTO building_types (building_type, name, description, base_metal_cost, base_crystal_cost, base_deuterium_cost) VALUES
('metal_mine', 'Metal Madeni', 'Metal üretir', 60, 15, 0),
('crystal_mine', 'Kristal Madeni', 'Kristal üretir', 48, 24, 0),
('deuterium_mine', 'Deuterium Sentezleyici', 'Deuterium üretir', 225, 75, 0),
('solar_plant', 'Güneş Enerjisi Santrali', 'Enerji üretir', 75, 30, 0),
('research_lab', 'Araştırma Laboratuvarı', 'Araştırma yapılır', 200, 400, 200),
('shipyard', 'Tersane', 'Gemi üretilir', 400, 200, 100),
('metal_storage', 'Metal Deposu', 'Metal depolar', 1000, 0, 0),
('crystal_storage', 'Kristal Deposu', 'Kristal depolar', 1000, 500, 0),
('deuterium_storage', 'Deuterium Deposu', 'Deuterium depolar', 1000, 1000, 0);

-- Araştırma Tipleri (Referans Tablosu)
CREATE TABLE IF NOT EXISTS research_types (
    research_type VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    base_metal_cost DECIMAL(15,2) DEFAULT 0,
    base_crystal_cost DECIMAL(15,2) DEFAULT 0,
    base_deuterium_cost DECIMAL(15,2) DEFAULT 0,
    cost_multiplier DECIMAL(5,2) DEFAULT 2.0,
    max_level INT DEFAULT 100
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Varsayılan Araştırma Tipleri
INSERT INTO research_types (research_type, name, description, base_metal_cost, base_crystal_cost, base_deuterium_cost) VALUES
('energy_tech', 'Enerji Teknolojisi', 'Enerji üretimini artırır', 0, 800, 400),
('laser_tech', 'Lazer Teknolojisi', 'Lazer silahları geliştirir', 200, 100, 0),
('ion_tech', 'İyon Teknolojisi', 'İyon silahları geliştirir', 1000, 300, 100),
('hyperspace_tech', 'Hiperuzay Teknolojisi', 'Uzay teknolojileri', 0, 4000, 2000),
('plasma_tech', 'Plazma Teknolojisi', 'Plazma silahları', 2000, 4000, 1000);