-- Game systems: items, economy, social, content
-- Run after 001_initial_schema.sql

CREATE TABLE IF NOT EXISTS item_definitions (
    id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    item_type ENUM('gear','consumable','scroll','quest','premium','insurance') NOT NULL,
    slot ENUM('helmet','necklace','shoulder','mail','gloves','leggings','boots','weapon','none') NOT NULL DEFAULT 'none',
    armor INT NOT NULL DEFAULT 0,
    stat_strength INT NOT NULL DEFAULT 0,
    stat_dexterity INT NOT NULL DEFAULT 0,
    stat_agility INT NOT NULL DEFAULT 0,
    stat_intelligence INT NOT NULL DEFAULT 0,
    stat_spirit INT NOT NULL DEFAULT 0,
    weapon_speed_ms INT NOT NULL DEFAULT 3000,
    is_tradable TINYINT(1) NOT NULL DEFAULT 1,
    is_quest_item TINYINT(1) NOT NULL DEFAULT 0,
    is_premium TINYINT(1) NOT NULL DEFAULT 0,
    stackable TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS item_instances (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    definition_id VARCHAR(64) NOT NULL,
    owner_character_id BIGINT UNSIGNED NULL,
    location ENUM('equipped','backpack','bank','mail','ground','shop') NOT NULL DEFAULT 'backpack',
    slot_name VARCHAR(32) NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_items_owner (owner_character_id),
    KEY idx_items_location (location),
    CONSTRAINT fk_items_definition FOREIGN KEY (definition_id) REFERENCES item_definitions(id),
    CONSTRAINT fk_items_owner FOREIGN KEY (owner_character_id) REFERENCES characters(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_slots (
    account_id BIGINT UNSIGNED NOT NULL,
    slot_index TINYINT UNSIGNED NOT NULL,
    item_instance_id BIGINT UNSIGNED NULL,
    PRIMARY KEY (account_id, slot_index),
    CONSTRAINT fk_bank_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 bank_gold (
    account_id BIGINT UNSIGNED PRIMARY KEY,
    amount INT UNSIGNED NOT NULL DEFAULT 0,
    CONSTRAINT fk_bank_gold_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 shop_listings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    town_id VARCHAR(64) NOT NULL,
    item_definition_id VARCHAR(64) NOT NULL,
    price INT UNSIGNED NOT NULL,
    UNIQUE KEY uq_shop_town_item (town_id, item_definition_id),
    CONSTRAINT fk_shop_item FOREIGN KEY (item_definition_id) REFERENCES item_definitions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS monster_definitions (
    id VARCHAR(64) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    zone_id VARCHAR(64) NOT NULL,
    hp INT NOT NULL,
    damage INT NOT NULL,
    armor INT NOT NULL DEFAULT 0,
    xp_reward INT NOT NULL DEFAULT 10,
    gold_min INT NOT NULL DEFAULT 1,
    gold_max INT NOT NULL DEFAULT 5,
    respawn_seconds INT NOT NULL DEFAULT 60,
    spawn_x INT NOT NULL,
    spawn_y INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS loot_tables (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    monster_id VARCHAR(64) NOT NULL,
    item_definition_id VARCHAR(64) NOT NULL,
    drop_chance DECIMAL(5,4) NOT NULL DEFAULT 0.1000,
    CONSTRAINT fk_loot_monster FOREIGN KEY (monster_id) REFERENCES monster_definitions(id),
    CONSTRAINT fk_loot_item FOREIGN KEY (item_definition_id) REFERENCES item_definitions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clans (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL,
    name_normalized VARCHAR(32) NOT NULL,
    leader_character_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_clans_name (name_normalized)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS clan_members (
    clan_id BIGINT UNSIGNED NOT NULL,
    character_id BIGINT UNSIGNED NOT NULL,
    role ENUM('leader','officer','member') NOT NULL DEFAULT 'member',
    joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (clan_id, character_id),
    UNIQUE KEY uq_clan_member_character (character_id),
    CONSTRAINT fk_clan_members_clan FOREIGN KEY (clan_id) REFERENCES clans(id) ON DELETE CASCADE,
    CONSTRAINT fk_clan_members_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 mail_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sender_character_id BIGINT UNSIGNED NOT NULL,
    recipient_character_id BIGINT UNSIGNED NOT NULL,
    subject VARCHAR(255) NOT NULL DEFAULT 'Delivery',
    gold_attached INT UNSIGNED NOT NULL DEFAULT 0,
    item_instance_id BIGINT UNSIGNED NULL,
    postage_paid INT UNSIGNED NOT NULL DEFAULT 100,
    status ENUM('pending','claimed','returned','deleted') NOT NULL DEFAULT 'pending',
    sent_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    KEY idx_mail_recipient (recipient_character_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel ENUM('world','town','clan','group','trade','combat','system','dm') NOT NULL,
    sender_character_id BIGINT UNSIGNED NULL,
    recipient_character_id BIGINT UNSIGNED NULL,
    town_id VARCHAR(64) NULL,
    clan_id BIGINT UNSIGNED NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_chat_channel (channel, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS direct_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sender_character_id BIGINT UNSIGNED NOT NULL,
    recipient_character_id BIGINT UNSIGNED NOT NULL,
    body TEXT NOT NULL,
    read_at TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_dm_recipient (recipient_character_id, read_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gold_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    character_id BIGINT UNSIGNED NULL,
    account_id BIGINT UNSIGNED NULL,
    amount INT NOT NULL,
    balance_after INT UNSIGNED NOT NULL,
    reason VARCHAR(64) NOT NULL,
    reference_id VARCHAR(64) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_gold_tx_character (character_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS loot_piles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zone_id VARCHAR(64) NOT NULL,
    tile_x INT NOT NULL,
    tile_y INT NOT NULL,
    owner_character_id BIGINT UNSIGNED NULL,
    gold INT UNSIGNED NOT NULL DEFAULT 0,
    protected TINYINT(1) NOT NULL DEFAULT 1,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_loot_piles_zone (zone_id, tile_x, tile_y)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS loot_pile_items (
    pile_id BIGINT UNSIGNED NOT NULL,
    item_instance_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (pile_id, item_instance_id),
    CONSTRAINT fk_pile_items_pile FOREIGN KEY (pile_id) REFERENCES loot_piles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS quests (
    id VARCHAR(64) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    quest_type ENUM('kill','collect','explore','puzzle') NOT NULL DEFAULT 'kill'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS character_quests (
    character_id BIGINT UNSIGNED NOT NULL,
    quest_id VARCHAR(64) NOT NULL,
    status ENUM('active','completed','failed') NOT NULL DEFAULT 'active',
    progress INT NOT NULL DEFAULT 0,
    started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (character_id, quest_id),
    CONSTRAINT fk_cq_character FOREIGN KEY (character_id) REFERENCES characters(id) ON DELETE CASCADE,
    CONSTRAINT fk_cq_quest FOREIGN KEY (quest_id) REFERENCES quests(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shrines (
    id VARCHAR(64) PRIMARY KEY,
    zone_id VARCHAR(64) NOT NULL,
    tile_x INT NOT NULL,
    tile_y INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS character_shrine_visits (
    character_id BIGINT UNSIGNED NOT NULL,
    shrine_id VARCHAR(64) NOT NULL,
    visited_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (character_id, shrine_id),
    CONSTRAINT fk_csv_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 boss_encounters (
    id VARCHAR(64) PRIMARY KEY,
    zone_id VARCHAR(64) NOT NULL,
    name VARCHAR(128) NOT NULL,
    hp INT NOT NULL,
    tile_x INT NOT NULL,
    tile_y INT NOT NULL,
    state ENUM('idle','active','resetting') NOT NULL DEFAULT 'idle'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS puzzle_states (
    puzzle_id VARCHAR(64) PRIMARY KEY,
    solved TINYINT(1) NOT NULL DEFAULT 0,
    solved_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS trade_sessions (
    id VARCHAR(64) PRIMARY KEY,
    initiator_character_id BIGINT UNSIGNED NOT NULL,
    target_character_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','active','completed','cancelled') NOT NULL DEFAULT 'pending',
    initiator_gold INT UNSIGNED NOT NULL DEFAULT 0,
    target_gold INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE characters ADD COLUMN clan_id BIGINT UNSIGNED NULL AFTER account_id;
ALTER TABLE characters ADD COLUMN has_gear_insurance TINYINT(1) NOT NULL DEFAULT 0 AFTER is_dead;