Base de datos - RochGOTH

/*
TABLA COMUN - USUARIOS Y DATOS
*/
CREATE DATABASE `gothmcn_common` /*!40100 DEFAULT CHARACTER SET latin1 */;

CREATE TABLE `accounts` (
  `uuid` varchar(40) NOT NULL,
  `name` varchar(16) NOT NULL,
  `rank` int(11) NOT NULL DEFAULT '0',
  `registerDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lastconectionDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `totaltimeconnected` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`uuid`),
  KEY `name` (`name`),
  KEY `rank` (`rank`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Registro de cuentas';

CREATE TABLE `accountconfig` (
  `uuid` varchar(40) NOT NULL,
  `chatcolor` int(11) DEFAULT '0',
  `invamigos` int(11) DEFAULT '0',
  `msgtoggle` int(11) DEFAULT '0',
  `hide` int(11) DEFAULT '0',
  PRIMARY KEY (`uuid`),
  CONSTRAINT `account_config` FOREIGN KEY (`uuid`) REFERENCES `accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `configvars` (
  `varname` varchar(50) NOT NULL,
  `varvalue` text,
  PRIMARY KEY (`varname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `friends` (
  `uuid` varchar(40) NOT NULL,
  `amigosuuid` text,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `account_friend` FOREIGN KEY (`uuid`) REFERENCES `accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `nickeds` (
  `uuid` varchar(40) NOT NULL,
  `nick` varchar(20) NOT NULL DEFAULT 'nul',
  `skin` varchar(20) NOT NULL DEFAULT 'Notch',
  PRIMARY KEY (`uuid`),
  CONSTRAINT `account_nicked` FOREIGN KEY (`uuid`) REFERENCES `accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `userstatus` (
  `uuid` varchar(40) NOT NULL,
  `server_name` varchar(25) NOT NULL,
  `lastupdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`uuid`),
  CONSTRAINT `uuid_account` FOREIGN KEY (`uuid`) REFERENCES `accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
TABLA SANCIONES - REGISTROS Y SANCIONES
*/
CREATE DATABASE `gothmcn_sanctions` /*!40100 DEFAULT CHARACTER SET latin1 */;

CREATE TABLE `bans` (
  `id` int(11) NOT NULL,
  `idLog` int(11) NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `staff` varchar(40) NOT NULL,
  `reason` text NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expire` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bans_idlog_idx` (`idLog`),
  KEY `account_staffban` (`staff`),
  KEY `account_userban` (`uuid`),
  CONSTRAINT `account_staffban` FOREIGN KEY (`staff`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `account_userban` FOREIGN KEY (`uuid`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `bans_idlog` FOREIGN KEY (`idLog`) REFERENCES `logsanctions` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ip_ban` (
  `ip` varbinary(16) NOT NULL,
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `logsanctions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `uuidTo` varchar(40) DEFAULT NULL,
  `uuidFrom` varchar(40) DEFAULT NULL,
  `reason` text,
  `sanction` enum('BAN','UNBAN','TEMPBAN','UNTEMPBAN','KICK','MUTE') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `account_sto` (`uuidTo`),
  KEY `account_sfrom` (`uuidFrom`),
  CONSTRAINT `account_sfrom` FOREIGN KEY (`uuidFrom`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `account_sto` FOREIGN KEY (`uuidTo`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

CREATE TABLE `mutes` (
  `id` int(11) NOT NULL,
  `idLog` int(11) NOT NULL,
  `uuid` varchar(40) NOT NULL,
  `staff` varchar(40) NOT NULL,
  `reason` text NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expire` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `bans_idlog_idx` (`idLog`),
  KEY `account_staffmute` (`staff`),
  KEY `account_usermuted` (`uuid`),
  CONSTRAINT `account_staffmute` FOREIGN KEY (`staff`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `account_usermuted` FOREIGN KEY (`uuid`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `mutes_idlog` FOREIGN KEY (`id`) REFERENCES `logsanctions` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uuidFrom` varchar(40) NOT NULL,
  `uuidTo` varchar(40) NOT NULL,
  `serverFrom` text NOT NULL,
  `serverTo` text NOT NULL,
  `reason` text NOT NULL,
  `dateCreated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `account_reportTo` (`uuidTo`),
  KEY `account_reportFrom` (`uuidFrom`),
  CONSTRAINT `account_reportFrom` FOREIGN KEY (`uuidFrom`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `account_reportTo` FOREIGN KEY (`uuidTo`) REFERENCES `gothmcn_common`.`accounts` (`uuid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;