CREATE DATABASE IF NOT EXISTS `mandanten`;
USE `mandanten`;

-- aktive_lizenzen_v4
-- ****************************************
CREATE TABLE mandanten.aktive_lizenzen_v4
(
  LizenzCode INT(11) NOT NULL DEFAULT 0,
  UID VARCHAR(32) NOT NULL DEFAULT '****************',
  aMandant VARCHAR(100) NOT NULL DEFAULT '',
  UserID INT(11) NOT NULL DEFAULT 0,
  aUser VARCHAR(100) NOT NULL DEFAULT '',
  Programm INT(11) NOT NULL DEFAULT 0,
  dateAktiviert DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00',
  dateAlive DATETIME NOT NULL DEFAULT '0001-01-01 00:00:00',
  MandConnID INT(11) NOT NULL DEFAULT 0,
  aSprache VARCHAR(255) DEFAULT '',
  aBezeichnung VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (LizenzCode,UID,aMandant,UserID,MandConnID),
  KEY I_Programm (Programm)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

-- lizenzen_v4
-- ****************************************
CREATE TABLE lizenzen_v4
(
  LizenzCode INT(11) NOT NULL DEFAULT 0,
  Programm INT(11) NOT NULL DEFAULT 0,
  Lizenzen SMALLINT(6) NOT NULL DEFAULT 0,
  aBezeichnung VARCHAR(255) DEFAULT '',
  PRIMARY KEY (LizenzCode,Programm),
  UNIQUE KEY I_Programm (Programm)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

INSERT INTO lizenzen_v4 (LizenzCode,Programm,Lizenzen,aBezeichnung) 
VALUES
  (0,0,50,'Null'),
  (524289,1,10,'Basis'),
  (524289,2,10,'Basis'),
  (524289,3,10,'Basis'),
  (524289,4,10,'Basis'),
  (524289,5,10,'Basis');

-- seckeytable_v4
-- ****************************************
CREATE TABLE seckeytable_v4
(
  Key1 VARCHAR(255) NOT NULL,
  Key2 INT(11) NOT NULL,
  dateValidTo DATE DEFAULT NULL,
  blobLicense BLOB NOT NULL,
  aName VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY  (Key1,Key2)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

-- mandanten
-- ****************************************
CREATE TABLE mandanten (
  MandID int(11) NOT NULL AUTO_INCREMENT,
  aName varchar(100) NOT NULL DEFAULT '',
  aDbName varchar(100) NOT NULL DEFAULT '',
  aHostname_IP varchar(100) NOT NULL DEFAULT '',
  Port int(11) NOT NULL DEFAULT 0,
  aBeschreibung varchar(200) NOT NULL DEFAULT '',
  eInterface varchar(32) NOT NULL DEFAULT 'MySQL_Direct',
  aZephirVersion varchar(15) NOT NULL DEFAULT '',
  aClientVersion varchar(15) NOT NULL DEFAULT '',
  aUpdatePfad varchar(255) NOT NULL DEFAULT '',
  bUpdates tinyint(4) NOT NULL DEFAULT 1,
  bSperre tinyint(4) NOT NULL DEFAULT 0,
  aServicesVersion varchar(15) NOT NULL DEFAULT '',
  aServicesPfad varchar(255) NOT NULL DEFAULT '',
  ePatchStadium TINYINT NOT NULL DEFAULT 4,
  aWartungsname VARCHAR(255) NOT NULL DEFAULT '',
  PRIMARY KEY (MandID),
  UNIQUE KEY Index_aName (aName),
  UNIQUE KEY Index_connection (aHostname_IP, `Port`, eInterface, aDbName),
  KEY Index_aDbName (aDbName)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- USER 'client'
-- ****************************************
DROP USER IF EXISTS 'client'@'%';
FLUSH PRIVILEGES;

CREATE USER 'client'@'%';
GRANT SELECT,INSERT,UPDATE,DELETE ON mandanten.aktive_lizenzen_v4 TO 'client'@'%' IDENTIFIED BY PASSWORD '*52A6395C0732F846DAC791C03CEA08DE2C1151E8';
GRANT SELECT ON mandanten.lizenzen_v4 TO 'client'@'%';
GRANT SELECT ON mandanten.mandanten TO 'client'@'%';
GRANT SELECT ON mandanten.seckeytable_v4 TO 'client'@'%';
FLUSH PRIVILEGES;