# Before MariaDB 10.2.1, 'text' column could not have a 'default' value.
# To not be forced to specify each text column on INSERT() statements, switch off strict checking:
SET sql_mode = "NO_ENGINE_SUBSTITUTION";

CREATE TABLE IF NOT EXISTS `Form`
(
    `id`                       INT(11)                                                                                    NOT NULL AUTO_INCREMENT,
    `name`                     VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `title`                    VARCHAR(1023)                                                                              NOT NULL DEFAULT '',
    `noteInternal`             TEXT                                                                                       NOT NULL,
    `tableName`                VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `primaryKey`               VARCHAR(255)                                                                               NOT NULL DEFAULT '',

    `permitNew`                ENUM ('sip', 'logged_in', 'logged_out', 'always', 'never')                                 NOT NULL DEFAULT 'sip',
    `permitEdit`               ENUM ('sip', 'logged_in', 'logged_out', 'always', 'never')                                 NOT NULL DEFAULT 'sip',
    `restMethod`               SET ('get', 'post', 'put', 'delete')                                                       NOT NULL DEFAULT '',
    `escapeTypeDefault`        VARCHAR(32)                                                                                NOT NULL DEFAULT 'c',
    `render`                   ENUM ('bootstrap', 'table', 'plain')                                                       NOT NULL DEFAULT 'bootstrap',
    `requiredParameterNew`     VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `requiredParameterEdit`    VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `dirtyMode`                ENUM ('exclusive', 'advisory', 'none')                                                     NOT NULL DEFAULT 'exclusive',
    `showButton`               SET ('new', 'delete', 'close', 'save')                                                     NOT NULL DEFAULT 'new,delete,close,save',
    `multiMode`                ENUM ('none', 'horizontal', 'vertical')                                                    NOT NULL DEFAULT 'none',
    `multiSql`                 TEXT                                                                                       NOT NULL,
    `multiDetailForm`          VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `multiDetailFormParameter` VARCHAR(255)                                                                               NOT NULL DEFAULT '',

    `forwardMode`              ENUM ('auto', 'close', 'no', 'url', 'url-skip-history', 'url-sip', 'url-sip-skip-history') NOT NULL DEFAULT 'auto',


    `forwardPage`              VARCHAR(511)                                                                               NOT NULL DEFAULT '',

    `labelAlign`               ENUM ('default', 'left', 'center', 'right')                                                NOT NULL DEFAULT 'default',
    `bsLabelColumns`           VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `bsInputColumns`           VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `bsNoteColumns`            VARCHAR(255)                                                                               NOT NULL DEFAULT '',

    `parameter`                TEXT                                                                                       NOT NULL,
    `parameterLanguageA`       TEXT                                                                                       NOT NULL,
    `parameterLanguageB`       TEXT                                                                                       NOT NULL,
    `parameterLanguageC`       TEXT                                                                                       NOT NULL,
    `parameterLanguageD`       TEXT                                                                                       NOT NULL,
    `recordLockTimeoutSeconds` INT(11)                                                                                    NOT NULL DEFAULT 900,

    `deleted`                  ENUM ('yes', 'no')                                                                         NOT NULL DEFAULT 'no',
    `fileStats`                VARCHAR(255)                                                                               NOT NULL DEFAULT '',
    `modified`                 DATETIME                                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`                  DATETIME                                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `name_deleted` (`name`, `deleted`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 1000;


CREATE TABLE IF NOT EXISTS `FormElement`
(
    `id`                 INT(11)                                                                    NOT NULL AUTO_INCREMENT,
    `formId`             INT(11)                                                                    NOT NULL,
    `feIdContainer`      INT(11)                                                                    NOT NULL DEFAULT '0',
    `dynamicUpdate`      ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'no',

    `enabled`            ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'yes',

    `name`               VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `label`              VARCHAR(1023)                                                              NOT NULL DEFAULT '',

    `mode`               ENUM ('show', 'required', 'readonly', 'hidden')                            NOT NULL DEFAULT 'show',
    `modeSql`            TEXT                                                                       NOT NULL,
    `class`              ENUM ('native', 'action', 'container')                                     NOT NULL DEFAULT 'native',
    `type`               ENUM ('checkbox', 'date', 'datetime', 'dateJQW', 'datetimeJQW', 'extra',
        'gridJQW', 'text', 'editor', 'annotate', 'time', 'note', 'password', 'radio', 'select',
        'subrecord', 'upload', 'imageCut', 'fieldset', 'pill', 'templateGroup', 'beforeLoad',
        'beforeSave', 'beforeInsert', 'beforeUpdate', 'beforeDelete', 'afterLoad', 'afterSave',
        'afterInsert', 'afterUpdate', 'afterDelete', 'sendMail', 'paste')                           NOT NULL DEFAULT 'text',
    `subrecordOption`    SET ('edit', 'delete', 'new')                                              NOT NULL DEFAULT '',
    `encode`             ENUM ('none', 'specialchar')                                               NOT NULL DEFAULT 'specialchar',
    `checkType`          ENUM ('auto', 'alnumx', 'digit', 'numerical', 'email', 'pattern', 'allbut',
        'all')                                                                                      NOT NULL DEFAULT 'auto',
    `checkPattern`       VARCHAR(255)                                                               NOT NULL DEFAULT '',

    `onChange`           VARCHAR(255)                                                               NOT NULL DEFAULT '',

    `ord`                INT(11)                                                                    NOT NULL DEFAULT '0',
    `tabindex`           INT(11)                                                                    NOT NULL DEFAULT '0',

    `size`               VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `maxLength`          VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `labelAlign`         ENUM ('default', 'left', 'center', 'right')                                NOT NULL DEFAULT 'default',
    `bsLabelColumns`     VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `bsInputColumns`     VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `bsNoteColumns`      VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `rowLabelInputNote`  SET ('row', 'label', '/label', 'input', '/input', 'note', '/note', '/row') NOT NULL DEFAULT 'row,label,/label,input,/input,note,/note,/row',
    `note`               TEXT                                                                       NOT NULL,
    `adminNote`          TEXT                                                                       NOT NULL,
    `tooltip`            VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `placeholder`        VARCHAR(2048)                                                              NOT NULL DEFAULT '',

    `value`              TEXT                                                                       NOT NULL,
    `sql1`               TEXT                                                                       NOT NULL,
    `parameter`          TEXT                                                                       NOT NULL,
    `parameterLanguageA` TEXT                                                                       NOT NULL,
    `parameterLanguageB` TEXT                                                                       NOT NULL,
    `parameterLanguageC` TEXT                                                                       NOT NULL,
    `parameterLanguageD` TEXT                                                                       NOT NULL,
    `clientJs`           TEXT                                                                       NOT NULL,

    `feGroup`            VARCHAR(255)                                                               NOT NULL DEFAULT '',
    `deleted`            ENUM ('yes', 'no')                                                         NOT NULL DEFAULT 'no',
    `modified`           DATETIME                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`            DATETIME                                                                   NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `formId` (`formId`),
    KEY `formId_class_enabled_deleted` (`formId`, `class`, `enabled`, `deleted`),
    KEY `feIdContainer` (`feIdContainer`),
    KEY `ord` (`ord`),
    KEY `feGroup` (`feGroup`)

)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;


CREATE TABLE IF NOT EXISTS `Dirty`
(
    `id`                   INT(11)                                NOT NULL AUTO_INCREMENT,
    `sip`                  VARCHAR(255)                           NOT NULL,
    `tableName`            VARCHAR(255)                           NOT NULL,
    `recordId`             INT(11)                                NOT NULL,
    `expire`               DATETIME                               NOT NULL,
    `recordHashMd5`        CHAR(32)                               NOT NULL,
    `tabUniqId`            CHAR(32)                               NOT NULL,
    `feUser`               VARCHAR(255)                           NOT NULL,
    `qfqUserSessionCookie` VARCHAR(255)                           NOT NULL,
    `dirtyMode`            ENUM ('exclusive', 'advisory', 'none') NOT NULL DEFAULT 'exclusive',
    `remoteAddress`        VARCHAR(45)                            NOT NULL,
    `modified`             DATETIME                               NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`              DATETIME                               NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    KEY `sip` (`sip`),
    KEY `tableName` (`tableName`),
    KEY `recordId` (`recordId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;


# MailLog
CREATE TABLE IF NOT EXISTS `MailLog`
(
    `id`       INT(11)       NOT NULL AUTO_INCREMENT,
    `grId`     INT(11)       NOT NULL DEFAULT '0',
    `xId`      INT(11)       NOT NULL DEFAULT '0',
    `xId2`     INT(11)       NOT NULL DEFAULT '0',
    `xId3`     INT(11)       NOT NULL DEFAULT '0',
    `receiver` TEXT          NOT NULL,
    `cc`       TEXT          NOT NULL,
    `bcc`      TEXT          NOT NULL,
    `sender`   VARCHAR(255)  NOT NULL DEFAULT '',
    `subject`  VARCHAR(255)  NOT NULL DEFAULT '',
    `body`     TEXT          NOT NULL,
    `header`   VARCHAR(2048) NOT NULL DEFAULT '',
    `attach`   VARCHAR(4096) NOT NULL DEFAULT '',
    `src`      VARCHAR(255)  NOT NULL DEFAULT '',
    `modified` DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;

CREATE TABLE IF NOT EXISTS `FormSubmitLog`
(
    `id`        INT(11)      NOT NULL AUTO_INCREMENT,
    `formData`  TEXT         NOT NULL,
    `sipData`   TEXT         NOT NULL,
    `clientIp`  VARCHAR(64)  NOT NULL,
    `feUser`    VARCHAR(64)  NOT NULL,
    `userAgent` TEXT         NOT NULL,
    `formId`    INT(11)      NOT NULL,
    `formName`  VARCHAR(255) NOT NULL,
    `recordId`  INT(11)      NOT NULL,
    `pageId`    INT          NOT NULL,
    `sessionId` VARCHAR(32)  NOT NULL,
    `created`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX (`feUser`),
    INDEX (`formId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;


CREATE TABLE IF NOT EXISTS `Clipboard`
(
    `id`          INT(11)      NOT NULL AUTO_INCREMENT,
    `cookie`      VARCHAR(255) NOT NULL DEFAULT '',
    `formIdPaste` INT(11)      NOT NULL DEFAULT '0',
    `idSrc`       INT(11)      NOT NULL DEFAULT '0',
    `xId`         INT(11)      NOT NULL DEFAULT '0',
    `modified`    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;


# AutoCRON
CREATE TABLE IF NOT EXISTS `Cron`
(
    `id`            INT(11)                      NOT NULL AUTO_INCREMENT,
    `grId`          INT(11)                      NOT NULL,
    `xId`           INT(11)                      NOT NULL,
    `type`          ENUM ('mail', 'website')     NOT NULL DEFAULT 'website',
    `lastRun`       DATETIME                     NOT NULL DEFAULT '0000-00-00 00:00:00',
    `lastStatus`    TEXT                         NOT NULL,
    `nextRun`       DATETIME                     NOT NULL DEFAULT '0000-00-00 00:00:00',
    `frequency`     VARCHAR(32)                  NOT NULL,
    `inProgress`    DATETIME                     NOT NULL DEFAULT '0000-00-00 00:00:00',
    `status`        ENUM ('enable', 'disable')   NOT NULL DEFAULT 'enable',
    `sql1`          TEXT                         NOT NULL,
    `content`       TEXT                         NOT NULL,
    `comment`       TEXT                         NOT NULL,
    `outputFile`    VARCHAR(255)                 NOT NULL,
    `outputMode`    ENUM ('overwrite', 'append') NOT NULL DEFAULT 'append',
    `outputPattern` VARCHAR(255)                 NOT NULL,
    `autoGenerated` ENUM ('yes', 'no')           NOT NULL DEFAULT 'no',
    `modified`      DATETIME                     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`       DATETIME                     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    AUTO_INCREMENT = 0
    DEFAULT CHARSET = utf8;


CREATE TABLE IF NOT EXISTS `Split`
(
    `id`           INT(11)      NOT NULL AUTO_INCREMENT,
    `tableName`    VARCHAR(255) NOT NULL,
    `xId`          INT(11)      NOT NULL,
    `pathFileName` VARCHAR(255) NOT NULL,
    `modified`     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)
)
    ENGINE = InnoDB
    AUTO_INCREMENT = 0
    DEFAULT CHARSET = utf8;

CREATE TABLE IF NOT EXISTS `Setting`
(
    `id`       INT(11)                  NOT NULL AUTO_INCREMENT,
    `type`     ENUM ('', 'tablesorter') NOT NULL,
    `readonly` ENUM ('yes', 'no')       NOT NULL DEFAULT 'no' COMMENT 'Settings can''t be modified.',
    `name`     VARCHAR(64)              NOT NULL,
    `public`   TINYINT(1)               NOT NULL,
    `feUser`   VARCHAR(32)              NOT NULL COMMENT 'In case there is no logged in user, take QFQ cookie.',
    `tableId`  VARCHAR(64)              NOT NULL,
    `view`     TEXT                     NOT NULL,
    `modified` DATETIME                 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created`  DATETIME                 NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `name` (`name`),
    KEY `typeFeUserUidTableIdPublic` (`type`, `feUser`, `tableId`, `public`) USING BTREE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;


