SET storage_engine=INNODB;

CREATE TABLE lbcoremodulepreferences (
       module VARCHAR(100) NOT NULL
     , preferences TEXT NOT NULL
     , PRIMARY KEY (module)
);

CREATE TABLE lbcoreusergroups (
       usergroupid BIGINT(20) NOT NULL AUTO_INCREMENT
     , usergroupname VARCHAR(100) NOT NULL
     , PRIMARY KEY (usergroupid)
);

CREATE TABLE lbcoremodules (
       modulename VARCHAR(256) NOT NULL
     , modulesuite TINYTEXT
     , currentversion VARCHAR(20) NOT NULL
     , description TEXT
     , isinstalled INT(1)
     , isactive INT(1)
     , helpfile TINYTEXT
     , thumbfile TINYTEXT
     , developer TINYTEXT
     , developerurl TINYTEXT
     , controlpanelfunction TINYTEXT
     , PRIMARY KEY (modulename)
);

CREATE TABLE lbcorelocationgroups (
       locationgroupid BIGINT(20) NOT NULL AUTO_INCREMENT
     , locationgroupname TINYTEXT NOT NULL
     , PRIMARY KEY (locationgroupid)
);

CREATE TABLE lbcoreusers (
       userid VARCHAR(100) NOT NULL
     , password VARCHAR(100) NOT NULL
     , usergroupid BIGINT(20) NOT NULL
     , email TINYTEXT
     , active INT(11) DEFAULT 1
     , lastoperation TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     , openid VARCHAR(256)
     , PRIMARY KEY (userid)
);
CREATE UNIQUE INDEX IX_lbcoreusers_2 ON lbcoreusers (openid ASC);

CREATE TABLE lbcorelocations (
       locationid BIGINT(20) NOT NULL AUTO_INCREMENT
     , name VARCHAR(100) NOT NULL
     , parentid BIGINT(20)
     , theme VARCHAR(100) NOT NULL DEFAULT 'default'
     , locationgroupid BIGINT(20)
     , visibleinmenus BIGINT(1) DEFAULT 1
     , locationsortorder BIGINT(20)
     , PRIMARY KEY (locationid)
);

CREATE TABLE lbcorepermissions (
       permissionid BIGINT(20) NOT NULL AUTO_INCREMENT
     , action VARCHAR(100)
     , module VARCHAR(100)
     , locationid VARCHAR(100)
     , user VARCHAR(100)
     , sortid BIGINT(20) DEFAULT 0
     , permission BIGINT(20) NOT NULL DEFAULT 0
     , locationgroupid BIGINT(20)
     , usergroupid BIGINT(20)
     , PRIMARY KEY (permissionid)
);

CREATE TABLE lbcoremoduleactions (
       actionname VARCHAR(256) NOT NULL
     , modulename TINYTEXT NOT NULL
     , ispublic INT(1)
     , actiondescription TEXT
     , actionthumbfile TINYTEXT
     , PRIMARY KEY (actionname)
);

CREATE TABLE lbcoreuserusergroups (
       userusergroupid BIGINT(20) NOT NULL AUTO_INCREMENT
     , userid VARCHAR(100) NOT NULL
     , usergroupid BIGINT(20) NOT NULL
     , PRIMARY KEY (userusergroupid)
);

CREATE TABLE lbcoreblockinstances (
       blockid BIGINT(20) NOT NULL AUTO_INCREMENT
     , instancename VARCHAR(100) NOT NULL
     , sortid BIGINT(20) DEFAULT 0
     , module VARCHAR(100)
     , action VARCHAR(100)
     , arguments TEXT
     , position VARCHAR(100) NOT NULL
     , locationid BIGINT(20)
     , locationgroupid BIGINT(20)
     , PRIMARY KEY (blockid)
);

ALTER TABLE lbcoreusers
  ADD CONSTRAINT FK_lbcoreusers_1
      FOREIGN KEY (usergroupid)
      REFERENCES lbcoreusergroups (usergroupid)
   ON DELETE SET NULL;

ALTER TABLE lbcorelocations
  ADD CONSTRAINT FK_lbcorelocations_1
      FOREIGN KEY (locationgroupid)
      REFERENCES lbcorelocationgroups (locationgroupid)
   ON DELETE SET NULL;

ALTER TABLE lbcorepermissions
  ADD CONSTRAINT FK_lbcorepermissions_2
      FOREIGN KEY (locationgroupid)
      REFERENCES lbcorelocationgroups (locationgroupid)
   ON DELETE CASCADE;

ALTER TABLE lbcorepermissions
  ADD CONSTRAINT FK_lbcorepermissions_1
      FOREIGN KEY (usergroupid)
      REFERENCES lbcoreusergroups (usergroupid)
   ON DELETE CASCADE;

ALTER TABLE lbcoremoduleactions
  ADD CONSTRAINT FK_lbcoremoduleactions_1
      FOREIGN KEY (modulename)
      REFERENCES lbcoremodules (modulename);

ALTER TABLE lbcoreuserusergroups
  ADD CONSTRAINT FK_TABLE_10_1
      FOREIGN KEY (userid)
      REFERENCES lbcoreusers (userid)
   ON DELETE CASCADE;

ALTER TABLE lbcoreuserusergroups
  ADD CONSTRAINT FK_TABLE_10_2
      FOREIGN KEY (usergroupid)
      REFERENCES lbcoreusergroups (usergroupid)
   ON DELETE CASCADE;

ALTER TABLE lbcoreblockinstances
  ADD CONSTRAINT FK_lbcoreblockinstances_1
      FOREIGN KEY (locationgroupid)
      REFERENCES lbcorelocationgroups (locationgroupid)
   ON DELETE CASCADE;

