-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `pso`
--

-- --------------------------------------------------------

--
-- Table structure for table `account_data`
--

CREATE TABLE `account_data` (
  `account_id` int(11) NOT NULL,
  `username` varchar(18) NOT NULL,
  `password` varchar(33) NOT NULL,
  `email` varchar(255) NOT NULL,
  `regtime` int(10) UNSIGNED NOT NULL,
  `isbanned` tinyint(2) NOT NULL DEFAULT 0,
  `teamid` int(11) NOT NULL DEFAULT -1,
  `privlevel` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `dressflag` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `bans`
--

CREATE TABLE `bans` (
  `ban_id` int(11) NOT NULL,
  `reason` varchar(256) DEFAULT NULL,
  `startdate` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `enddate` int(10) UNSIGNED NOT NULL DEFAULT 4294967295,
  `setby` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Triggers `bans`
--
DELIMITER $$
CREATE TRIGGER `ban_start` BEFORE INSERT ON `bans` FOR EACH ROW set new.startdate = UNIX_TIMESTAMP()
$$
DELIMITER ;

-- --------------------------------------------------------

--
-- Table structure for table `character_backup`
--

CREATE TABLE `character_backup` (
  `guildcard` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `data` blob NOT NULL,
  `size` smallint(6) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `character_data`
--

CREATE TABLE `character_data` (
  `guildcard` int(11) NOT NULL,
  `slot` tinyint(4) NOT NULL,
  `data` blob NOT NULL,
  `size` smallint(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `dreamcast_clients`
--

CREATE TABLE `dreamcast_clients` (
  `guildcard` int(11) NOT NULL,
  `serial_number` char(8) NOT NULL,
  `access_key` char(8) NOT NULL,
  `dc_id` char(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `dreamcast_nte_clients`
--

CREATE TABLE `dreamcast_nte_clients` (
  `guildcard` int(11) NOT NULL,
  `serial_number` char(16) NOT NULL,
  `access_key` char(16) NOT NULL,
  `dc_id` char(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `friendlist`
--

CREATE TABLE `friendlist` (
  `owner` int(11) NOT NULL,
  `friend` int(11) NOT NULL,
  `nickname` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `gamecube_clients`
--

CREATE TABLE `gamecube_clients` (
  `guildcard` int(11) NOT NULL,
  `serial_number` char(8) NOT NULL,
  `access_key` char(12) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `guildcards`
--

CREATE TABLE `guildcards` (
  `guildcard` int(11) NOT NULL,
  `account_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `guildcard_bans`
--

CREATE TABLE `guildcard_bans` (
  `ban_id` int(11) NOT NULL,
  `guildcard` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ip_bans`
--

CREATE TABLE `ip_bans` (
  `ban_id` int(11) NOT NULL,
  `addr` int(10) UNSIGNED NOT NULL,
  `mask` int(10) UNSIGNED NOT NULL DEFAULT 4294967295
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `online_clients`
--

CREATE TABLE `online_clients` (
  `guildcard` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
  `ship_id` int(10) UNSIGNED NOT NULL,
  `block` int(10) UNSIGNED NOT NULL,
  `lobby` varchar(32) NOT NULL DEFAULT '1',
  `lobby_id` int(10) UNSIGNED DEFAULT NULL,
  `dlobby_id` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `online_ships`
--

CREATE TABLE `online_ships` (
  `name` varchar(12) NOT NULL,
  `players` int(11) NOT NULL DEFAULT 0,
  `ip` int(10) UNSIGNED NOT NULL,
  `port` smallint(5) UNSIGNED NOT NULL,
  `int_ip` int(10) UNSIGNED NOT NULL,
  `ship_id` int(10) UNSIGNED NOT NULL,
  `gm_only` tinyint(1) NOT NULL DEFAULT 0,
  `games` int(11) NOT NULL DEFAULT 0,
  `menu_code` int(11) NOT NULL DEFAULT 0,
  `flags` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `ship_number` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `ship_ip6_high` bigint(20) UNSIGNED DEFAULT NULL,
  `ship_ip6_low` bigint(20) UNSIGNED DEFAULT NULL,
  `protocol_ver` int(10) UNSIGNED NOT NULL,
  `privileges` int(31) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `passwd_resets`
--

CREATE TABLE `passwd_resets` (
  `account_id` int(11) NOT NULL,
  `uuid` char(36) NOT NULL,
  `req_time` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `pc_clients`
--

CREATE TABLE `pc_clients` (
  `guildcard` int(11) NOT NULL,
  `serial_number` char(8) NOT NULL,
  `access_key` char(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ship_data`
--

CREATE TABLE `ship_data` (
  `idx` int(11) NOT NULL,
  `main_menu` tinyint(3) UNSIGNED NOT NULL DEFAULT 0,
  `ship_number` int(10) UNSIGNED NOT NULL,
  `memo` varchar(256) DEFAULT NULL,
  `sha1_fingerprint` binary(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ship_metadata`
--

CREATE TABLE `ship_metadata` (
  `ship_id` int(32) NOT NULL,
  `metadata_id` int(32) NOT NULL,
  `value` int(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `account_data`
--
ALTER TABLE `account_data`
  ADD PRIMARY KEY (`account_id`);

--
-- Indexes for table `bans`
--
ALTER TABLE `bans`
  ADD PRIMARY KEY (`ban_id`),
  ADD KEY `setby` (`setby`);

--
-- Indexes for table `character_backup`
--
ALTER TABLE `character_backup`
  ADD PRIMARY KEY (`guildcard`,`name`);

--
-- Indexes for table `character_data`
--
ALTER TABLE `character_data`
  ADD KEY `guildcard` (`guildcard`);

--
-- Indexes for table `dreamcast_clients`
--
ALTER TABLE `dreamcast_clients`
  ADD PRIMARY KEY (`guildcard`);

--
-- Indexes for table `dreamcast_nte_clients`
--
ALTER TABLE `dreamcast_nte_clients`
  ADD PRIMARY KEY (`guildcard`);

--
-- Indexes for table `friendlist`
--
ALTER TABLE `friendlist`
  ADD KEY `owner` (`owner`),
  ADD KEY `friend` (`friend`);

--
-- Indexes for table `gamecube_clients`
--
ALTER TABLE `gamecube_clients`
  ADD PRIMARY KEY (`guildcard`);

--
-- Indexes for table `guildcards`
--
ALTER TABLE `guildcards`
  ADD PRIMARY KEY (`guildcard`),
  ADD KEY `account_id` (`account_id`);

--
-- Indexes for table `guildcard_bans`
--
ALTER TABLE `guildcard_bans`
  ADD PRIMARY KEY (`ban_id`),
  ADD KEY `guildcard` (`guildcard`);

--
-- Indexes for table `ip_bans`
--
ALTER TABLE `ip_bans`
  ADD PRIMARY KEY (`ban_id`);

--
-- Indexes for table `online_clients`
--
ALTER TABLE `online_clients`
  ADD PRIMARY KEY (`guildcard`),
  ADD KEY `ship_id` (`ship_id`),
  ADD KEY `lobby_id` (`lobby_id`);

--
-- Indexes for table `online_ships`
--
ALTER TABLE `online_ships`
  ADD PRIMARY KEY (`ship_id`);

--
-- Indexes for table `passwd_resets`
--
ALTER TABLE `passwd_resets`
  ADD PRIMARY KEY (`account_id`);

--
-- Indexes for table `pc_clients`
--
ALTER TABLE `pc_clients`
  ADD PRIMARY KEY (`guildcard`);

--
-- Indexes for table `ship_data`
--
ALTER TABLE `ship_data`
  ADD PRIMARY KEY (`idx`);

--
-- Indexes for table `ship_metadata`
--
ALTER TABLE `ship_metadata`
  ADD PRIMARY KEY (`ship_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `account_data`
--
ALTER TABLE `account_data`
  MODIFY `account_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `bans`
--
ALTER TABLE `bans`
  MODIFY `ban_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `guildcards`
--
ALTER TABLE `guildcards`
  MODIFY `guildcard` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ship_data`
--
ALTER TABLE `ship_data`
  MODIFY `idx` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bans`
--
ALTER TABLE `bans`
  ADD CONSTRAINT `bans_ibfk_1` FOREIGN KEY (`setby`) REFERENCES `account_data` (`account_id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `character_backup`
--
ALTER TABLE `character_backup`
  ADD CONSTRAINT `character_backup_ibfk_1` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `character_data`
--
ALTER TABLE `character_data`
  ADD CONSTRAINT `character_data_ibfk_1` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `dreamcast_clients`
--
ALTER TABLE `dreamcast_clients`
  ADD CONSTRAINT `dreamcast_clients_ibfk_1` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `dreamcast_nte_clients`
--
ALTER TABLE `dreamcast_nte_clients`
  ADD CONSTRAINT `dreamcast_nte_clients_ibfk_1` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `friendlist`
--
ALTER TABLE `friendlist`
  ADD CONSTRAINT `friendlist_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `friendlist_ibfk_2` FOREIGN KEY (`friend`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `gamecube_clients`
--
ALTER TABLE `gamecube_clients`
  ADD CONSTRAINT `gamecube_clients_ibfk_1` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `guildcards`
--
ALTER TABLE `guildcards`
  ADD CONSTRAINT `guildcards_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account_data` (`account_id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `guildcard_bans`
--
ALTER TABLE `guildcard_bans`
  ADD CONSTRAINT `guildcard_bans_ibfk_1` FOREIGN KEY (`ban_id`) REFERENCES `bans` (`ban_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `guildcard_bans_ibfk_2` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `ip_bans`
--
ALTER TABLE `ip_bans`
  ADD CONSTRAINT `ip_bans_ibfk_1` FOREIGN KEY (`ban_id`) REFERENCES `bans` (`ban_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `online_clients`
--
ALTER TABLE `online_clients`
  ADD CONSTRAINT `online_clients_ibfk_1` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `online_clients_ibfk_2` FOREIGN KEY (`ship_id`) REFERENCES `online_ships` (`ship_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `passwd_resets`
--
ALTER TABLE `passwd_resets`
  ADD CONSTRAINT `passwd_resets_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account_data` (`account_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `pc_clients`
--
ALTER TABLE `pc_clients`
  ADD CONSTRAINT `pc_clients_ibfk_1` FOREIGN KEY (`guildcard`) REFERENCES `guildcards` (`guildcard`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

