# QFQ tables: Filename has to start with 'qfq' and end with '.sql'
#
# QFQ Default tables
#
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 DEFAULT '',
    `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','issue')                                          NOT NULL DEFAULT 'new,delete,close,save',
    `multiMode`                ENUM ('none','horizontal','vertical')                                                NOT NULL DEFAULT 'none',
    `multiSql`                 TEXT                                                                                 NOT NULL DEFAULT '',
    `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 DEFAULT '',
    `parameterLanguageA`       TEXT                                                                                 NOT NULL DEFAULT '',
    `parameterLanguageB`       TEXT                                                                                 NOT NULL DEFAULT '',
    `parameterLanguageC`       TEXT                                                                                 NOT NULL DEFAULT '',
    `parameterLanguageD`       TEXT                                                                                 NOT NULL DEFAULT '',
    `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 DEFAULT '0',
    `feIdContainer`      INT(11)                                                                      NOT NULL DEFAULT '0',
    `dynamicUpdate`      ENUM ('yes', 'no')                                                           NOT NULL DEFAULT 'no',
    `encryption`         ENUM ('yes', 'no')                                                           NOT NULL DEFAULT 'no',
    `encryptionMethod`   ENUM ('Default', 'AES-128','AES-256')                                        NOT NULL DEFAULT 'Default',
    `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 DEFAULT '',
    `class`              ENUM ('native', 'action', 'container')                                       NOT NULL DEFAULT 'native',
    `type`               ENUM ('checkbox', 'date', 'datetime', 'extra',
        'text', 'editor', 'annotate', 'time', 'note', 'password', 'radio', 'select', 'subrecord',
        'upload', 'chat', 'aiChat', 'imageCut', 'inputCollectionJson', 'fieldset', 'pill', 'templateGroup',
        'beforeLoad', 'beforeSave', 'beforeInsert', 'beforeUpdate', 'beforeDelete', 'afterLoad',
        'afterSave', 'afterInsert', 'afterUpdate', 'afterDelete', 'sendMail', 'paste', 'colorPicker') NOT NULL DEFAULT 'text',
    `subrecordOption`    SET ('edit', 'delete', 'new')                                                NOT NULL DEFAULT '',
    `encode`             ENUM ('none', 'specialchar', 'single tick')                                  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 DEFAULT '',
    `adminNote`          TEXT                                                                         NOT NULL DEFAULT '',
    `tooltip`            VARCHAR(255)                                                                 NOT NULL DEFAULT '',
    `placeholder`        VARCHAR(2048)                                                                NOT NULL DEFAULT '',

    `value`              TEXT                                                                         NOT NULL DEFAULT '',
    `sql1`               TEXT                                                                         NOT NULL DEFAULT '',
    `parameter`          TEXT                                                                         NOT NULL DEFAULT '',
    `parameterLanguageA` TEXT                                                                         NOT NULL DEFAULT '',
    `parameterLanguageB` TEXT                                                                         NOT NULL DEFAULT '',
    `parameterLanguageC` TEXT                                                                         NOT NULL DEFAULT '',
    `parameterLanguageD` TEXT                                                                         NOT NULL DEFAULT '',
    `clientJs`           TEXT                                                                         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`)
)
    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 DEFAULT '',
    `tableName`            VARCHAR(255)                         NOT NULL DEFAULT '',
    `recordId`             INT(11)                              NOT NULL DEFAULT 0,
    `expire`               DATETIME                             NOT NULL DEFAULT '0000-00-00 00:00:00',
    `recordHashMd5`        char(32)                             NOT NULL DEFAULT '',
    `tabUniqId`            char(32)                             NOT NULL DEFAULT '',
    `feUser`               VARCHAR(255)                         NOT NULL DEFAULT '',
    `qfqUserSessionCookie` VARCHAR(255)                         NOT NULL DEFAULT '',
    `dirtyMode`            ENUM ('exclusive','advisory','none') NOT NULL DEFAULT 'exclusive',
    `remoteAddress`        VARCHAR(45)                          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 `sip` (`sip`),
    KEY `tableName` (`tableName`),
    KEY `recordId` (`recordId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;

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 DEFAULT '',
    `cc`       TEXT          NOT NULL DEFAULT '',
    `bcc`      TEXT          NOT NULL DEFAULT '',
    `sender`   VARCHAR(255)  NOT NULL DEFAULT '',
    `subject`  VARCHAR(255)  NOT NULL DEFAULT '',
    `body`     TEXT          NOT NULL DEFAULT '',
    `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 DEFAULT '',
    `sipData`   TEXT         NOT NULL DEFAULT '',
    `clientIp`  VARCHAR(64)  NOT NULL DEFAULT '',
    `feUser`    VARCHAR(64)  NOT NULL DEFAULT '',
    `userAgent` TEXT         NOT NULL DEFAULT '',
    `formId`    INT(11)      NOT NULL DEFAULT 0,
    `formName`  VARCHAR(255) NOT NULL DEFAULT '',
    `recordId`  INT(11)      NOT NULL DEFAULT 0,
    `pageId`    INT(11)      NOT NULL DEFAULT 0,
    `sessionId` VARCHAR(32)  NOT NULL DEFAULT '',
    `created`   DATETIME     NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    KEY `feUser` (`feUser`),
    KEY `clientIp` (`clientIp`),
    KEY `formId` (`formId`),
    KEY `formName` (`formName`),
    KEY `recordId` (`recordId`),
    KEY `pageId` (`pageId`),
    KEY `createdFeUserFormId` (`created`, `feUser`, `formId`)
)
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;

