DROP TABLE IF EXISTS archive_reference_data;
DROP TABLE IF EXISTS archive_null_data;
DROP TABLE IF EXISTS archive_text_data;
DROP TABLE IF EXISTS archive_name_data;
DROP TABLE IF EXISTS archive_enum_data;
DROP TABLE IF EXISTS archive_integer_data;
DROP TABLE IF EXISTS archive_double_data;
DROP TABLE IF EXISTS archive_datetime_data;
DROP TABLE IF EXISTS archive_date_data;
DROP TABLE IF EXISTS archive_name_overrides;
DROP TABLE IF EXISTS archive_desc_overrides;
DROP TABLE IF EXISTS archive_data_type;
DROP TABLE IF EXISTS archive_collection_type;
DROP TABLE IF EXISTS archive_query_template_def;
DROP TABLE IF EXISTS archive_files;
DROP TABLE IF EXISTS archive_entities;
DROP TABLE IF EXISTS archive_isa;
DROP TABLE IF EXISTS entity_version;
DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions (
  srid VARBINARY(255) PRIMARY KEY,
  username VARBINARY(255) NOT NULL,
  realm VARBINARY(255) NOT NULL,
  seconds BIGINT UNSIGNED NOT NULL,
  nanos INT(10) UNSIGNED NOT NULL
) ENGINE=InnoDB;

-- TODO remove ON DELETE CASCADE when feature is stable.
CREATE TABLE entity_version (
  entity_id INT UNSIGNED NOT NULL,
  hash VARBINARY(255) DEFAULT NULL,
  version VARBINARY(255) NOT NULL,
  _iversion INT UNSIGNED NOT NULL,
  _ipparent INT UNSIGNED NULL,
  srid VARBINARY(255) NOT NULL,
  PRIMARY KEY (`entity_id`, `_iversion`),
  FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`srid`) REFERENCES `transactions` (`srid`),
  UNIQUE (`entity_id`, `version`)
) ENGINE=InnoDB;

CREATE TABLE archive_isa (
  child INT UNSIGNED NOT NULL,
  child_iversion INT UNSIGNED NOT NULL,
  parent INT UNSIGNED NOT NULL,
  FOREIGN KEY (`parent`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`child`, `child_iversion`) REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_reference_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value INT UNSIGNED NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX','REPLACEMENT')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`value`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_null_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_text_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value TEXT NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_name_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value VARCHAR(255) NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    KEY (`value`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_enum_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value VARBINARY(255) NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_integer_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value BIGINT NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    unit_sig BIGINT DEFAULT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_double_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value DOUBLE NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    unit_sig BIGINT DEFAULT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_datetime_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value BIGINT NOT NULL,
    value_ns INT(10) UNSIGNED DEFAULT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_date_data (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    value INT(11) NOT NULL,
    status ENUM('OBLIGATORY','RECOMMENDED','SUGGESTED','FIX')
        NOT NULL,
    pidx INT(10) UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_name_overrides (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_desc_overrides (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    description TEXT NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_data_type (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    datatype INT UNSIGNED NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`datatype`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_collection_type (
    domain_id INT UNSIGNED NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    property_id INT UNSIGNED NOT NULL,
    collection VARCHAR(255) NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    UNIQUE KEY (`domain_id`, `entity_id`, `property_id`, `_iversion`),
    KEY (`domain_id`, `entity_id`, `_iversion`),
    KEY (`domain_id`, `_iversion`),
    FOREIGN KEY (`domain_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`entity_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property_id`) REFERENCES `entities` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_query_template_def (
    id INT UNSIGNED NOT NULL,
    definition MEDIUMTEXT NOT NULL,
    _iversion INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`, `_iversion`),
    FOREIGN KEY (`id`, `_iversion`)
        REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_files (
    file_id INT UNSIGNED NOT NULL,
    `path` TEXT NOT NULL,
    size BIGINT UNSIGNED NOT NULL,
    hash BINARY(64) DEFAULT NULL,
    _iversion INT UNSIGNED NOT NULL,
    PRIMARY KEY (`file_id`, `_iversion`),
    FOREIGN KEY (`file_id`, `_iversion`)
        REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE archive_entities (
    id INT UNSIGNED NOT NULL,
    name VARCHAR(255) DEFAULT NULL,
    description TEXT DEFAULT NULL,
    role ENUM('RECORDTYPE','RECORD','FILE','DOMAIN',
        'PROPERTY','DATATYPE','ROLE','QUERYTEMPLATE') NOT NULL,
    acl INT(10) UNSIGNED DEFAULT NULL,
    _iversion INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`, `_iversion`),
    FOREIGN KEY (`id`, `_iversion`)
        REFERENCES `entity_version` (`entity_id`, `_iversion`) ON DELETE CASCADE,
    FOREIGN KEY (`acl`) REFERENCES `entity_acl` (`id`)
) ENGINE=InnoDB;


delimiter //

DROP PROCEDURE IF EXISTS set_transaction //
CREATE PROCEDURE set_transaction(
    srid VARBINARY(255),
    username VARCHAR(255),
    realm VARCHAR(255),
    seconds BIGINT UNSIGNED,
    nanos INT(10) UNSIGNED)
BEGIN

    SET @SRID = srid;
    INSERT INTO transactions (srid, username, realm, seconds, nanos)
        VALUES (srid, username, realm, seconds, nanos);
END //


DROP PROCEDURE IF EXISTS setFileProperties //
CREATE PROCEDURE setFileProperties (
    in EntityID INT UNSIGNED,
    in FilePath TEXT,
    in FileSize BIGINT UNSIGNED,
    in FileHash VARCHAR(255)
)
BEGIN
    DECLARE IVersion INT UNSIGNED DEFAULT NULL;
    IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
        SELECT max(e._iversion) INTO IVersion
            FROM entity_version AS e
            WHERE e.entity_id = EntityID;

        INSERT INTO archive_files (file_id, path, size, hash,
                _iversion)
            SELECT file_id, path, size, hash, IVersion AS _iversion
            FROM files
            WHERE file_id = EntityID;
    END IF;

    DELETE FROM files WHERE file_id = EntityID;

    IF FilePath IS NOT NULL THEN
        INSERT INTO files (file_id, path, size, hash)
            VALUES (EntityID, FilePath, FileSize, unhex(FileHash));
    END IF;

END //


DROP PROCEDURE IF EXISTS retrieveQueryTemplateDef //
CREATE PROCEDURE retrieveQueryTemplateDef (
    in EntityID INT UNSIGNED,
    in Version VARBINARY(255))
retrieveQueryTemplateDefBody: BEGIN

    DECLARE IVersion INT UNSIGNED DEFAULT NULL;
    DECLARE IsHead BOOLEAN DEFAULT TRUE;

    IF is_feature_config("ENTITY_VERSIONING", "ENABLED") THEN
        IF Version IS NOT NULL THEN
            SELECT get_head_version(EntityID) = Version INTO IsHead;
        END IF;

        IF IsHead IS FALSE THEN
            SELECT e._iversion INTO IVersion
                FROM entity_version as e
                WHERE e.entity_id = EntityID
                AND e.version = Version;

            IF IVersion IS NULL THEN
                -- RETURN EARLY - Version does not exist.
                LEAVE retrieveQueryTemplateDefBody;
            END IF;

            SELECT definition
            FROM archive_query_template_def
            WHERE id = EntityID
            AND _iversion = IVersion;

            LEAVE retrieveQueryTemplateDefBody;
        END IF;
    END IF;

    SELECT definition
    FROM query_template_def
    WHERE id = EntityID;

END //


delimiter ;

INSERT INTO feature_config (_key, _value) VALUES ("ENTITY_VERSIONING", "ENABLED");