

-- Dumping database structure for mg_genesis
CREATE DATABASE IF NOT EXISTS `mg_genesis` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `mg_genesis`;

-- Dumping structure for table mg_genesis.syshost
CREATE TABLE IF NOT EXISTS `syshost` (
  `SYSHOST_ID` int(11) NOT NULL AUTO_INCREMENT,
  `SYSHOST_DESCR` varchar(255) NOT NULL,
  `SYSHOST_CODE` varchar(4) DEFAULT NULL,
  `SYSHOST_IP` varchar(50) DEFAULT NULL,
  `SYSHOST_AFM` varchar(45) NOT NULL,
  `SYSHOST_TENANTID` int(11) DEFAULT NULL,
  `SYSHOST_CLIENT_ID` varchar(255) DEFAULT NULL,
  `SYSHOST_CLIENT_SECRET` varchar(255) DEFAULT NULL,
  `SYSHOST_LONGITUDE` double DEFAULT '0',
  `SYSHOST_LATITUDE` double DEFAULT '0',
  `SYSHOST_STATE` int(11) DEFAULT '0' COMMENT 'Κατάσταση (0.Undefined, 1.test, 2.prod)',
  `SYSHOST_APPS` varchar(255) DEFAULT NULL COMMENT 'EXR,BEX,PRO,DPE',
  PRIMARY KEY (`SYSHOST_ID`),
  UNIQUE KEY `syshost_descr_UNIQUE` (`SYSHOST_DESCR`),
  UNIQUE KEY `syshost_afm_UNIQUE` (`SYSHOST_AFM`),
  UNIQUE KEY `syshost_code_UNIQUE` (`SYSHOST_CODE`),
  KEY `UK_SYSHOST` (`SYSHOST_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Genesis Hosts';

-- Dumping data for table mg_genesis.syshost: ~1 rows (approximately)
INSERT INTO `syshost` (`SYSHOST_ID`, `SYSHOST_DESCR`, `SYSHOST_CODE`, `SYSHOST_IP`, `SYSHOST_AFM`, `SYSHOST_TENANTID`, `SYSHOST_CLIENT_ID`, `SYSHOST_CLIENT_SECRET`, `SYSHOST_LONGITUDE`, `SYSHOST_LATITUDE`, `SYSHOST_STATE`, `SYSHOST_APPS`) VALUES
	(0, 'Οργανισμός', '0000', NULL, '', NULL, NULL, NULL, NULL, NULL, '0', NULL);

-- Dumping structure for Ο€Ξ―Ξ½Ξ±ΞΊΞ±Ο‚ mg_genesis.syslog
CREATE TABLE IF NOT EXISTS `syslog` (
  `syslog_id` int(11) NOT NULL AUTO_INCREMENT,
  `syslog_host_code` varchar(4) DEFAULT NULL,
  `syslog_session_id` varchar(50) DEFAULT NULL,
  `syslog_app` varchar(50) DEFAULT NULL,
  `syslog_cls` varchar(255) DEFAULT NULL,
  `syslog_datetime` datetime DEFAULT NULL,
  `syslog_action` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`syslog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- Data exporting was unselected.
-- Dumping structure for table mg_genesis.sysusr
CREATE TABLE IF NOT EXISTS `sysusr` (
  `SYSUSR_ID` int(11) NOT NULL AUTO_INCREMENT,
  `SYSUSR_CURRENT_HOST_CODE` varchar(4) DEFAULT NULL,
  `SYSUSR_NAME` varchar(255) NOT NULL,
  `SYSUSR_PWD` varchar(255) NOT NULL,
  `SYSUSR_FIRST_NAME` varchar(255) DEFAULT NULL,
  `SYSUSR_LAST_NAME` varchar(255) DEFAULT NULL,
  `SYSUSR_ACTIVE` varchar(1) DEFAULT '1',
  `SYSUSR_STATE` varchar(1) DEFAULT '0',
  PRIMARY KEY (`SYSUSR_ID`),
  KEY `FK_SYSUSR_SYSHOST_idx` (`SYSUSR_CURRENT_HOST_CODE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Χρήστες';

-- Data exporting was unselected.
-- Dumping structure for πίνακας mg_genesis.sysusrd
CREATE TABLE IF NOT EXISTS `sysusrd` (
  `SYSUSRD_ID` int(11) NOT NULL AUTO_INCREMENT,
  `SYSUSRD_SYSUSR_ID` int(11) DEFAULT NULL,
  `SYSUSRD_SYSHOST_CODE` varchar(50) DEFAULT NULL,
  `SYSUSRD_INSDATE` date NOT NULL,
  PRIMARY KEY (`SYSUSRD_ID`),
  KEY `SYSUSRD_SYSUSR_FK` (`SYSUSRD_SYSUSR_ID`),
  KEY `SYSUSRD_SYSHOST_FK` (`SYSUSRD_SYSHOST_CODE`),
  CONSTRAINT `SYSUSRD_SYSHOST_FK` FOREIGN KEY (`SYSUSRD_SYSHOST_CODE`) REFERENCES `syshost` (`SYSHOST_CODE`),
  CONSTRAINT `SYSUSRD_SYSUSR_FK` FOREIGN KEY (`SYSUSRD_SYSUSR_ID`) REFERENCES `sysusr` (`SYSUSR_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Data exporting was unselected.
-- Dumping structure for trigger mg_genesis.syshost_BEFORE_INSERT
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `syshost_BEFORE_INSERT` BEFORE INSERT ON `syshost` FOR EACH ROW BEGIN
 DECLARE next_id INT;

 if NEW.syshost_code is null then
  SET next_id = (SELECT AUTO_INCREMENT 
                          FROM information_schema.TABLES 
                          WHERE TABLE_SCHEMA=DATABASE() AND 
                                TABLE_NAME='syshost');
                                
  SET NEW.syshost_code = LPAD(next_id, 4,'0');
 end if;
end//
DELIMITER ;

-- Dumping structure for trigger mg_genesis.syslog_before_insert
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
DELIMITER //
CREATE TRIGGER `syslog_before_insert` BEFORE INSERT ON `syslog` FOR EACH ROW BEGIN
	SET NEW.syslog_datetime = sysdate();
END//
DELIMITER ;

