-- ------------------------------------------------------------------------------
--     This file is part of Spheres.
--     Copyright (C) 2019  Jesusalva

--     This library is free software; you can redistribute it and/or
--     modify it under the terms of the GNU Lesser General Public
--     License as published by the Free Software Foundation; either
--     version 2.1 of the License, or (at your option) any later version.

--     This library is distributed in the hope that it will be useful,
--     but WITHOUT ANY WARRANTY; without even the implied warranty of
--     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
--     Lesser General Public License for more details.

--     You should have received a copy of the GNU Lesser General Public
--     License along with this library; if not, write to the Free Software
--     Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
-- ------------------------------------------------------------------------------

-- Table structure for database
CREATE TABLE IF NOT EXISTS `login` (
    `userid` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `userpw` VARCHAR(32) NOT NULL DEFAULT '',
    `email` VARCHAR(255) NOT NULL DEFAULT '',
    --`guild_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `lastlogin` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`userid`),
    KEY `name` (`userid`)
) ENGINE=MyISAM AUTO_INCREMENT=2000000;

CREATE TABLE IF NOT EXISTS `player` (
    `userid` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` TINYINT NOT NULL DEFAULT 0,
    `quest` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `gp` INT UNSIGNED NOT NULL DEFAULT 0,
    `crystals` INT UNSIGNED NOT NULL DEFAULT 0,
    `exp` INT UNSIGNED NOT NULL DEFAULT 0,
    `level` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `ap` SMALLINT UNSIGNED NOT NULL DEFAULT 8,
    `max_ap` SMALLINT NOT NULL DEFAULT 8,
    `aptime` INT NOT NULL DEFAULT 0,
    `max_inv` SMALLINT UNSIGNED NOT NULL DEFAULT 25,
    `max_sum` TINYINT NOT NULL DEFAULT 0,
    `lastlogin` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`userid`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `world` (
    `userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `world` VARCHAR(32) NOT NULL DEFAULT '',
    `status` INT(11) NOT NULL DEFAULT 0,
    PRIMARY KEY (`userid`,`world`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `party` (
    `userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `party_id` TINYINT(2) NOT NULL DEFAULT 0,

    `member1_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `member1_ix` INT(11) NOT NULL DEFAULT -1,

    `member2_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `member2_ix` INT(11) NOT NULL DEFAULT -1,

    `member3_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `member3_ix` INT(11) NOT NULL DEFAULT -1,

    `member4_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `member4_ix` INT(11) NOT NULL DEFAULT -1,

    PRIMARY KEY (`userid`,`party_id`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `inventory` (
    `userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `index` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    `unit_id` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `unit_lv` INT(11) UNSIGNED NOT NULL DEFAULT 1,
    `unit_xp` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`userid`,`index`)
) ENGINE=MyISAM;


-- .:: CURRENCY ::.
-- medals: Bounty currency (WIP)
-- miniap: Mini AP Elixirs (WIP)
-- maxiap: AP Elixirs (WIP)
-- ticketX: ticket for tavern ID X (WIP)
-- Standard_D3_v2
-- Add more stuff as desired.
CREATE TABLE IF NOT EXISTS `currency` (
    `userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `medals` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `miniap` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `maxiap` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    `ticket3` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `ticket4` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `ticket5` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `ticket6` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `ticket7` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`userid`)
) ENGINE=MyISAM;

-- ---------------------------------- The event progress control table
-- .:: BOUNTY BOSS EVENTS ::.
-- bounty_medals: Medals accumulated
-- bounty_eventid: Entry from bounty.json to use (for reset and name etc.)

-- .:: SPONSOR EVENTS ::.
-- sponsor_gift: How many extra crystals to receive
-- sponsor_left: How many times you'll receive sponsor_gift
-- sponsor_last: Like login_date (DEPRECATED)

-- .:: RAID EVENTS ::.
-- Raids might need raid.json, defining:
-- Boss name, item names, shop worth, etc.
-- Keep in mind raid boss must be kept on a global array (GA)
-- raid_itemX: Amount of item X you have, where X is index 1~4.
-- raid_level: Maximum level you've reached on the raid. (auto-update by GA)
-- raid_eventid: Which entry from raid.json to use. Also determines data reset

-- .:: LOGIN EVENTS ::.
-- login_memo: Tracks total event login count
-- login_eventid: The event ID to which login_memo refers to

-- .:: GIFTING EVENTS ::.
-- You receive X normal gifts (special class, like crystals) from quests/daily
-- gifting_normal: Amount of normal gifts
-- gifting_premium: Amount of platinum gifts
-- gifting_recv_normal: Amount of RECEIVED normal gifts
-- gifting_recv_premium: Amount of RECEIVED platinum gifts

-- WIP: https://grandsphere.fandom.com/wiki/Summon_Wars_(Clokka)//
-- That's mostly server infrastructure and less heavy on SQL


CREATE TABLE IF NOT EXISTS `event` (
    `userid` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    `bounty_medals` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `bounty_eventid` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    `sponsor_gift` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `sponsor_left` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `sponsor_last` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    `raid_item1` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `raid_item2` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `raid_item3` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `raid_item4` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `raid_level` INT(11) UNSIGNED NOT NULL DEFAULT 1,
    `raid_eventid` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    `login_main` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `login_memo` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `login_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    `gifting_normal` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `gifting_premium` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `gifting_recv_normal` INT(11) UNSIGNED NOT NULL DEFAULT 0,
    `gifting_recv_premium` INT(11) UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (`userid`)
) ENGINE=MyISAM;