-- DeployRule table (formerly CopyPasteRule)
CREATE TABLE IF NOT EXISTS `DeployRule`
(
    `id`            int(11)         NOT NULL AUTO_INCREMENT,
    `name`          varchar(100)    UNIQUE NOT NULL,
    `definition`    text            NOT NULL DEFAULT '',
    `description`   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;

-- DeployRef table - tracks deployed records between databases
CREATE TABLE IF NOT EXISTS `DeployRef`
(
    `id`            int(11)         NOT NULL AUTO_INCREMENT,
    `tableName`     varchar(100)    NOT NULL,
    `srcId`         int(11)         NOT NULL COMMENT 'Source record ID from export',
    `targetId`      int(11)         NOT NULL COMMENT 'Target record ID from import',
    `content`       text            DEFAULT NULL COMMENT 'JSON data of record from export (for skipModified mode)',
    `ruleId`        int(11)         DEFAULT NULL COMMENT 'DeployRule ID used for deployment',
    `potentiallyDeleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Flag: srcId not in last export, possibly deleted on source',
    `modified`      datetime        NOT NULL COMMENT 'Modified timestamp of imported record',
    `created`       datetime        NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`),
    UNIQUE KEY `tableNameSrcIdRuleId` (`tableName`, `srcId`, `ruleId`),
    KEY `tableNameTargetId` (`tableName`, `targetId`),
    KEY `ruleId` (`ruleId`)
    )
    ENGINE = InnoDB
    DEFAULT CHARSET = utf8
    AUTO_INCREMENT = 0;