--
-- Encounter Journal: schema for custom_ej_* tables.
-- Source of truth for client EncounterJournal data lives here, not in DBC.
--
-- Tables are CREATE TABLE IF NOT EXISTS so re-running the file is safe.
-- DBUpdater re-applies the file when its hash changes.
--

CREATE TABLE IF NOT EXISTS `custom_ej_tier` (
  `id`         INT UNSIGNED NOT NULL,
  `name`       VARCHAR(255) NOT NULL,
  `flags`      INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Encounter Journal: tier (expansion/content bucket).';

CREATE TABLE IF NOT EXISTS `custom_ej_instance` (
  `id`                  INT UNSIGNED NOT NULL,
  `name`                VARCHAR(255) NOT NULL,
  `description`         TEXT NOT NULL,
  `button_icon`         VARCHAR(255) NOT NULL DEFAULT '',
  `small_button_icon`   VARCHAR(255) NOT NULL DEFAULT '',
  `background`          VARCHAR(255) NOT NULL DEFAULT '',
  `lore_background`     VARCHAR(255) NOT NULL DEFAULT '',
  `map_id`              INT UNSIGNED NOT NULL DEFAULT 0,
  `area_id`             INT UNSIGNED NOT NULL DEFAULT 0,
  `world_map_area_id`   INT UNSIGNED NOT NULL DEFAULT 0,
  `flags`               INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order`          INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Encounter Journal: dungeon/raid/scenario.';

CREATE TABLE IF NOT EXISTS `custom_ej_tier_instance` (
  `tier_id`     INT UNSIGNED NOT NULL,
  `instance_id` INT UNSIGNED NOT NULL,
  `sort_order`  INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`tier_id`, `instance_id`),
  KEY `idx_instance` (`instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Encounter Journal: tier <-> instance N:M.';

CREATE TABLE IF NOT EXISTS `custom_ej_encounter` (
  `id`                INT UNSIGNED NOT NULL,
  `instance_id`       INT UNSIGNED NOT NULL,
  `name`              VARCHAR(255) NOT NULL,
  `description`       TEXT NOT NULL,
  `map_position_x`    FLOAT NOT NULL DEFAULT 0,
  `map_position_y`    FLOAT NOT NULL DEFAULT 0,
  `floor_index`       INT UNSIGNED NOT NULL DEFAULT 1,
  `world_map_area_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `first_section_id`  INT UNSIGNED NOT NULL DEFAULT 0,
  `difficulty_mask`   INT UNSIGNED NOT NULL DEFAULT 0,
  `flags`             INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order`        INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_instance` (`instance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Encounter Journal: boss / encounter.';

CREATE TABLE IF NOT EXISTS `custom_ej_creature` (
  `id`                  INT UNSIGNED NOT NULL,
  `encounter_id`        INT UNSIGNED NOT NULL,
  `creature_id`         INT UNSIGNED NOT NULL DEFAULT 0,
  `name`                VARCHAR(255) NOT NULL,
  `description`         TEXT NOT NULL,
  `creature_display_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `icon`                VARCHAR(255) NOT NULL DEFAULT '',
  `difficulty_mask`     INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order`          INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_encounter` (`encounter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Encounter Journal: creature card under an encounter.';

CREATE TABLE IF NOT EXISTS `custom_ej_section` (
  `id`                    INT UNSIGNED NOT NULL,
  `encounter_id`          INT UNSIGNED NOT NULL,
  `parent_section_id`     INT UNSIGNED NOT NULL DEFAULT 0,
  `next_section_id`       INT UNSIGNED NOT NULL DEFAULT 0,
  `sub_section_id`        INT UNSIGNED NOT NULL DEFAULT 0,
  `name`                  VARCHAR(255) NOT NULL,
  `description`           TEXT NOT NULL,
  `creature_display_id`   INT UNSIGNED NOT NULL DEFAULT 0,
  `description_spell_id`  INT UNSIGNED NOT NULL DEFAULT 0,
  `icon_spell_id`         INT UNSIGNED NOT NULL DEFAULT 0,
  `icon`                  VARCHAR(255) NULL,
  `flags`                 INT UNSIGNED NOT NULL DEFAULT 0,
  `icon_flags`            INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order`            INT NOT NULL DEFAULT 0,
  `type`                  INT UNSIGNED NOT NULL DEFAULT 0,
  `difficulty_mask`       INT UNSIGNED NOT NULL DEFAULT 0,
  `creature_id`           INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_encounter` (`encounter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Encounter Journal: encounter section (Overview/Abilities tree).';

CREATE TABLE IF NOT EXISTS `custom_ej_loot` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `encounter_id`    INT UNSIGNED NOT NULL,
  `item_id`         INT UNSIGNED NOT NULL,
  `difficulty_mask` INT UNSIGNED NOT NULL DEFAULT 0,
  `faction_mask`    INT NOT NULL DEFAULT -1,
  `class_mask`      INT NOT NULL DEFAULT -1,
  `flags`           INT UNSIGNED NOT NULL DEFAULT 0,
  `sort_order`      INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_encounter` (`encounter_id`),
  KEY `idx_item`      (`item_id`),
  UNIQUE KEY `uk_loot_dedup`
    (`encounter_id`, `item_id`, `difficulty_mask`, `faction_mask`, `class_mask`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  COMMENT='Encounter Journal: explicit loot table per encounter.';
