-- Realm of Forgotten Souls — initial schema
-- Run via: php apps/api/bin/migrate.php

CREATE TABLE IF NOT EXISTS accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firebase_uid VARCHAR(128) NULL UNIQUE,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NULL,
    phone VARCHAR(32) NULL,
    theme VARCHAR(32) NOT NULL DEFAULT 'dark',
    age_verified_at TIMESTAMP NULL,
    email_verified_at TIMESTAMP NULL,
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    is_banned TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_accounts_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS characters (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(12) NOT NULL,
    name_normalized VARCHAR(12) NOT NULL,
    presentation ENUM('male','female','unspecified') NOT NULL DEFAULT 'unspecified',
    race ENUM('mortal','undead','lich') NOT NULL DEFAULT 'mortal',
    base_level INT UNSIGNED NOT NULL DEFAULT 1,
    base_xp BIGINT UNSIGNED NOT NULL DEFAULT 0,
    fighter_level INT UNSIGNED NOT NULL DEFAULT 1,
    fighter_xp BIGINT UNSIGNED NOT NULL DEFAULT 0,
    ranger_level INT UNSIGNED NOT NULL DEFAULT 1,
    ranger_xp BIGINT UNSIGNED NOT NULL DEFAULT 0,
    mage_level INT UNSIGNED NOT NULL DEFAULT 1,
    mage_xp BIGINT UNSIGNED NOT NULL DEFAULT 0,
    healer_level INT UNSIGNED NOT NULL DEFAULT 1,
    healer_xp BIGINT UNSIGNED NOT NULL DEFAULT 0,
    strength INT NOT NULL DEFAULT 10,
    dexterity INT NOT NULL DEFAULT 10,
    agility INT NOT NULL DEFAULT 10,
    intelligence INT NOT NULL DEFAULT 10,
    spirit INT NOT NULL DEFAULT 10,
    hp INT NOT NULL DEFAULT 100,
    max_hp INT NOT NULL DEFAULT 100,
    mana INT NOT NULL DEFAULT 50,
    max_mana INT NOT NULL DEFAULT 50,
    fatigue INT NOT NULL DEFAULT 100,
    max_fatigue INT NOT NULL DEFAULT 100,
    gold INT UNSIGNED NOT NULL DEFAULT 100,
    carried_gold INT UNSIGNED NOT NULL DEFAULT 100,
    location_type ENUM('town','world') NOT NULL DEFAULT 'town',
    location_id VARCHAR(64) NOT NULL DEFAULT 'hammerfall',
    tile_x INT NOT NULL DEFAULT 25,
    tile_y INT NOT NULL DEFAULT 25,
    last_town_id VARCHAR(64) NOT NULL DEFAULT 'hammerfall',
    is_dead TINYINT(1) NOT NULL DEFAULT 0,
    player_kills INT UNSIGNED NOT NULL DEFAULT 0,
    monster_kills INT UNSIGNED NOT NULL DEFAULT 0,
    donations_total INT UNSIGNED NOT NULL DEFAULT 0,
    last_rename_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_characters_name_normalized (name_normalized),
    KEY idx_characters_account (account_id),
    CONSTRAINT fk_characters_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS name_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    character_id BIGINT UNSIGNED NOT NULL,
    old_name VARCHAR(12) NOT NULL,
    new_name VARCHAR(12) NOT NULL,
    changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_name_history_character (character_id),
    CONSTRAINT fk_name_history_character FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(64) PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    character_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(512) NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_sessions_account (account_id),
    KEY idx_sessions_expires (expires_at),
    CONSTRAINT fk_sessions_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS news (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    author_account_id BIGINT UNSIGNED NULL,
    published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    is_published TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_news_published (is_published, published_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS feedback (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    category ENUM('report','issue','compliment','improvement') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_feedback_account (account_id),
    CONSTRAINT fk_feedback_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS donations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NOT NULL,
    stripe_payment_id VARCHAR(128) NULL,
    amount_cents INT UNSIGNED NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    status ENUM('pending','completed','refunded','chargeback') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_donations_account (account_id),
    CONSTRAINT fk_donations_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS titles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    character_id BIGINT UNSIGNED NOT NULL,
    title_key VARCHAR(64) NOT NULL,
    granted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP NULL,
    UNIQUE KEY uq_titles_active (character_id, title_key, revoked_at),
    CONSTRAINT fk_titles_character FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS zones (
    id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    width INT NOT NULL,
    height INT NOT NULL,
    zone_type ENUM('town','world','boss','outpost') NOT NULL DEFAULT 'world',
    safe_reentry_x INT NOT NULL DEFAULT 25,
    safe_reentry_y INT NOT NULL DEFAULT 25
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS zone_tiles (
    zone_id VARCHAR(64) NOT NULL,
    x INT NOT NULL,
    y INT NOT NULL,
    tile_type ENUM('floor','wall','water','portal','hazard') NOT NULL DEFAULT 'floor',
    is_blocked TINYINT(1) NOT NULL DEFAULT 0,
    sprite_key VARCHAR(64) NOT NULL DEFAULT 'floor_dark',
    PRIMARY KEY (zone_id, x, y),
    CONSTRAINT fk_zone_tiles_zone FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_account_id BIGINT UNSIGNED NULL,
    action VARCHAR(64) NOT NULL,
    entity_type VARCHAR(64) NULL,
    entity_id VARCHAR(64) NULL,
    details JSON NULL,
    correlation_id VARCHAR(64) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;