-- SqlAccountRepository.java
DROP TABLE IF EXISTS ACCOUNT;
CREATE TABLE ACCOUNT (
    ACCOUNT_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    USERNAME VARCHAR(32) UNIQUE,
    PASSWORD VARCHAR(256),
    PSEUDO VARCHAR(32) UNIQUE,
    PERMISSIONS INTEGER,
    QUESTION VARCHAR(64),
    ANSWER VARCHAR(255)
);

-- SqlAreaRepository.java
DROP TABLE IF EXISTS AREA;
CREATE TABLE AREA (
    AREA_ID INTEGER PRIMARY KEY,
    AREA_NAME VARCHAR(200),
    SUPERAREA_ID INTEGER
);

-- SqlBanIpRepository.java
DROP TABLE IF EXISTS BANIP;
CREATE TABLE BANIP (
    BANIP_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    IP_ADDRESS VARCHAR(64),
    UPDATED_AT DATETIME,
    EXPIRES_AT DATETIME,
    CAUSE VARCHAR(256),
    BANISHER_ID INTEGER
);

-- SqlBanishmentRepository.java
DROP TABLE IF EXISTS BANISHMENT;
CREATE TABLE BANISHMENT (
    BANISHMENT_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    ACCOUNT_ID INTEGER,
    START_DATE DATETIME,
    END_DATE DATETIME,
    CAUSE VARCHAR(255),
    BANISHER_ID INTEGER
);

-- SqlAccountBankRepository.java
DROP TABLE IF EXISTS BANK;
CREATE TABLE BANK (
    ACCOUNT_ID INTEGER,
    SERVER_ID INTEGER,
    BANK_KAMAS BIGINT,
    PRIMARY KEY (ACCOUNT_ID, SERVER_ID)
);

-- SqlBankItemRepository.java
DROP TABLE IF EXISTS BANK_ITEM;
CREATE TABLE BANK_ITEM (
    ACCOUNT_ID INTEGER,
    SERVER_ID INTEGER,
    ITEM_ENTRY_ID INTEGER,
    ITEM_TEMPLATE_ID INTEGER,
    ITEM_EFFECTS TEXT,
    QUANTITY SMALLINT,
    PRIMARY KEY (ACCOUNT_ID, SERVER_ID, ITEM_ENTRY_ID)
);

-- SqlConnectionLogRepository.java
DROP TABLE IF EXISTS CONNECTION_LOG;
CREATE TABLE CONNECTION_LOG (
    ACCOUNT_ID INTEGER NOT NULL,
    START_DATE DATETIME NOT NULL,
    IP_ADDRESS VARCHAR(45) NOT NULL,
    END_DATE DATETIME,
    SERVER_ID INTEGER,
    PLAYER_ID INTEGER,
    CLIENT_UID VARCHAR(32),
    PRIMARY KEY (ACCOUNT_ID, START_DATE)
);

-- SqlItemSetRepository.java
DROP TABLE IF EXISTS `ITEM_SET`;
CREATE TABLE `ITEM_SET` (
    `ITEM_SET_ID` INTEGER PRIMARY KEY,
    `ITEM_SET_NAME` VARCHAR(50),
    `ITEM_SET_BONUS` TEXT
);

-- SqlItemTemplateRepository.java
DROP TABLE IF EXISTS `ITEM_TEMPLATE`;
CREATE TABLE `ITEM_TEMPLATE` (
    `ITEM_TEMPLATE_ID` INTEGER PRIMARY KEY,
    `ITEM_TYPE` INTEGER,
    `ITEM_NAME` VARCHAR(50),
    `ITEM_LEVEL` INTEGER,
    `ITEM_EFFECTS` TEXT,
    `WEIGHT` INTEGER,
    `ITEM_SET_ID` INTEGER,
    `PRICE` INTEGER,
    `CONDITIONS` VARCHAR(100),
    `WEAPON_INFO` VARCHAR(100)
);

-- SqlItemTypeRepository.java
DROP TABLE IF EXISTS `ITEM_TYPE`;
CREATE TABLE `ITEM_TYPE` (
    `TYPE_ID` INTEGER PRIMARY KEY,
    `TYPE_NAME` VARCHAR(32),
    `SUPER_TYPE` INTEGER,
    `EFFECT_AREA` CHAR(2)
);

-- SqlMapTriggerRepository.java
DROP TABLE IF EXISTS MAP_TRIGGER;
CREATE TABLE MAP_TRIGGER (
    MAP_ID INTEGER,
    CELL_ID INTEGER,
    ACTION INTEGER,
    ARGUMENTS TEXT,
    CONDITIONS TEXT,
    PRIMARY KEY (MAP_ID, CELL_ID)
);

-- SqlMapTemplateRepository.java
DROP TABLE IF EXISTS maps;
CREATE TABLE maps(
    id INTEGER PRIMARY KEY,
    `date` VARCHAR(12),
    width INTEGER,
    height INTEGER,
    `key` TEXT,
    mapData TEXT,
    places TEXT,
    MAP_X INTEGER,
    MAP_Y INTEGER,
    SUBAREA_ID INTEGER,
    INDOOR BOOLEAN
);

-- SqlMonsterGroupDataRepository.java
DROP TABLE IF EXISTS `MONSTER_GROUP`;
CREATE TABLE `MONSTER_GROUP` (
    `MONSTER_GROUP_ID` INTEGER PRIMARY KEY,
    `RESPAWN_TIME` INTEGER,
    `MAX_SIZE` INTEGER,
    `MAX_COUNT` INTEGER,
    `MONSTERS` TEXT,
    `COMMENT` VARCHAR(24),
    `WIN_FIGHT_TELEPORT_MAP_ID` INTEGER,
    `WIN_FIGHT_TELEPORT_CELL_ID` INTEGER,
    `FIXED_TEAM_NUMBER` INTEGER
);

-- SqlMonsterGroupPositionRepository.java
DROP TABLE IF EXISTS `MONSTER_GROUP_POSITION`;
CREATE TABLE `MONSTER_GROUP_POSITION` (
    `MAP_ID` INTEGER,
    `CELL_ID` INTEGER,
    `MONSTER_GROUP_ID` INTEGER,
    PRIMARY KEY (`MAP_ID`, `CELL_ID`)
);

-- SqlMonsterRewardRepository.java
DROP TABLE IF EXISTS `MONSTER_REWARD`;
CREATE TABLE `MONSTER_REWARD` (
    `MONSTER_ID` INTEGER PRIMARY KEY,
    `MIN_KAMAS` INTEGER,
    `MAX_KAMAS` INTEGER,
    `EXPERIENCES` VARCHAR(200)
);

-- SqlMonsterRewardItemRepository.java
DROP TABLE IF EXISTS `MONSTER_REWARD_ITEM`;
CREATE TABLE `MONSTER_REWARD_ITEM` (
    `MONSTER_ID` INTEGER,
    `ITEM_TEMPLATE_ID` INTEGER,
    `QUANTITY` INTEGER,
    `DISCERNMENT` INTEGER,
    `RATE` INTEGER,
    PRIMARY KEY (`MONSTER_ID`, `ITEM_TEMPLATE_ID`)
);

-- SqlMonsterTemplateRepository.java
DROP TABLE IF EXISTS `MONSTER_TEMPLATE`;
CREATE TABLE `MONSTER_TEMPLATE` (
    `MONSTER_ID` INTEGER PRIMARY KEY,
    `MONSTER_NAME` VARCHAR(100),
    `GFXID` INTEGER,
    `COLORS` VARCHAR(30),
    `AI` VARCHAR(12),
    `CHARACTERISTICS` TEXT,
    `LIFE_POINTS` VARCHAR(200),
    `INITIATIVES` VARCHAR(200),
    `SPELLS` TEXT
);

-- SqlNpcRepository.java
DROP TABLE IF EXISTS NPC;
CREATE TABLE NPC(
    NPC_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    NPC_TEMPLATE_ID INTEGER,
    MAP_ID INTEGER,
    CELL_ID INTEGER,
    ORIENTATION TINYINT(1),
    QUESTIONS VARCHAR(32)
);

-- SqlNpcExchangeRepository.java
DROP TABLE IF EXISTS NPC_EXCHANGE;
CREATE TABLE NPC_EXCHANGE(
    NPC_EXCHANGE_ID INTEGER PRIMARY KEY,
    NPC_TEMPLATE_ID INTEGER,
    REQUIRED_KAMAS BIGINT,
    REQUIRED_ITEMS TEXT,
    EXCHANGED_KAMAS BIGINT,
    EXCHANGED_ITEMS TEXT
);

-- SqlQuestionRepository.java
DROP TABLE IF EXISTS NPC_QUESTION;
CREATE TABLE NPC_QUESTION(
    QUESTION_ID INTEGER PRIMARY KEY,
    RESPONSE_IDS VARCHAR(100),
    PARAMETERS VARCHAR(100),
    CONDITIONS VARCHAR(256)
);

-- SqlResponseActionRepository.java
DROP TABLE IF EXISTS NPC_RESPONSE_ACTION;
CREATE TABLE NPC_RESPONSE_ACTION(
    RESPONSE_ID INTEGER,
    `ACTION` VARCHAR(12),
    ARGUMENTS VARCHAR(255),
    PRIMARY KEY (RESPONSE_ID, ACTION)
);

-- SqlNpcTemplateRepository.java
DROP TABLE IF EXISTS NPC_TEMPLATE;
CREATE TABLE NPC_TEMPLATE(
    NPC_TEMPLATE_ID INTEGER PRIMARY KEY,
    GFXID INTEGER,
    SCALE_X INTEGER,
    SCALE_Y INTEGER,
    SEX TINYINT(1),
    COLOR1 INTEGER,
    COLOR2 INTEGER,
    COLOR3 INTEGER,
    ACCESSORIES VARCHAR(30),
    EXTRA_CLIP TINYINT(1),
    CUSTOM_ARTWORK INTEGER,
    STORE_ITEMS TEXT
);

-- SqlPlayerRepository.java
DROP TABLE IF EXISTS PLAYER;
CREATE TABLE PLAYER (
    PLAYER_ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    ACCOUNT_ID INTEGER,
    SERVER_ID INTEGER,
    PLAYER_NAME VARCHAR(32),
    RACE INTEGER(2),
    SEX INTEGER(1),
    COLOR1 INTEGER,
    COLOR2 INTEGER,
    COLOR3 INTEGER,
    PLAYER_LEVEL INTEGER,
    PLAYER_STATS TEXT,
    MAP_ID INTEGER,
    CELL_ID INTEGER,
    CHANNELS VARCHAR(16),
    BOOST_POINTS INTEGER,
    SPELL_POINTS INTEGER,
    LIFE_POINTS INTEGER,
    PLAYER_EXPERIENCE BIGINT,
    SAVED_MAP_ID INTEGER,
    SAVED_CELL_ID INTEGER,
    PLAYER_KAMAS BIGINT,
    DELETED_AT DATETIME NULL DEFAULT NULL,
    UNIQUE (PLAYER_NAME, SERVER_ID)
);

-- SqlPlayerItemRepository.java
DROP TABLE IF EXISTS PLAYER_ITEM;
CREATE TABLE PLAYER_ITEM (
    PLAYER_ID INTEGER,
    ITEM_ENTRY_ID INTEGER,
    ITEM_TEMPLATE_ID INTEGER,
    ITEM_EFFECTS TEXT,
    QUANTITY SMALLINT,
    POSITION TINYINT,
    PRIMARY KEY (PLAYER_ID, ITEM_ENTRY_ID)
);

-- SqlPlayerRaceRepository.java
DROP TABLE IF EXISTS PLAYER_RACE;
CREATE TABLE PLAYER_RACE (
    RACE_ID INTEGER PRIMARY KEY,
    RACE_NAME VARCHAR(32),
    RACE_STATS TEXT,
    START_DISCERNMENT INTEGER,
    START_PODS INTEGER,
    START_LIFE INTEGER,
    PER_LEVEL_LIFE INTEGER,
    STATS_BOOST VARCHAR(255),
    RACE_SPELLS VARCHAR(255),
    MAP_ID INTEGER,
    CELL_ID INTEGER,
    ASTRUB_MAP_ID INTEGER,
    ASTRUB_CELL_ID INTEGER,
    DEFAULT_CLOSE_COMBAT VARCHAR(255) NOT NULL DEFAULT "100,2,6,,0,0,1d5+1|100,5,9,,0,0,1d5+4|4|1|1|20|50|false|true|false|false|0|0|0|0|PaPa||18;19;3;1;41|0|true",
    DEFAULT_WEAPON_ABILITY INTEGER NOT NULL DEFAULT 90,
    WEAPONS_ABILITIES TEXT NOT NULL DEFAULT "{}"
);

-- SqlPlayerSpellRepository.java
DROP TABLE IF EXISTS PLAYER_SPELL;
CREATE TABLE PLAYER_SPELL (
    PLAYER_ID INTEGER,
    SPELL_ID INTEGER,
    CLASS_SPELL BOOLEAN,
    SPELL_LEVEL TINYINT,
    SPELL_POSITION TINYINT,
    PRIMARY KEY (PLAYER_ID, SPELL_ID)
);

-- SqlPlayerExperienceRepository.java
DROP TABLE IF EXISTS PLAYER_XP;
CREATE TABLE PLAYER_XP (
    PLAYER_LEVEL SMALLINT PRIMARY KEY,
    EXPERIENCE BIGINT
);

-- SqlSpellTemplateRepository.java
DROP TABLE IF EXISTS `SPELL`;
CREATE TABLE `SPELL` (
    `SPELL_ID` INTEGER PRIMARY KEY,
    `SPELL_NAME` VARCHAR(100),
    `SPELL_SPRITE` INTEGER,
    `SPELL_SPRITE_ARG` VARCHAR(20),
    `SPELL_LVL_1` TEXT,
    `SPELL_LVL_2` TEXT,
    `SPELL_LVL_3` TEXT,
    `SPELL_LVL_4` TEXT,
    `SPELL_LVL_5` TEXT,
    `SPELL_LVL_6` TEXT,
    `SPELL_TARGET` VARCHAR(32)
);

-- SqlSubAreaRepository.java
DROP TABLE IF EXISTS SUBAREA;
CREATE TABLE SUBAREA (
    SUBAREA_ID INTEGER PRIMARY KEY,
    AREA_ID INTEGER,
    SUBAREA_NAME VARCHAR(200),
    CONQUESTABLE INTEGER(1),
    ALIGNMENT INTEGER(1)
);
