-- DocControl module - install.sql

CREATE TABLE IF NOT EXISTS llx_doccontrol_category (
    rowid   INT NOT NULL AUTO_INCREMENT,
    label   VARCHAR(255) NOT NULL,
    entity  INT NOT NULL DEFAULT 1,
    PRIMARY KEY (rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS llx_doccontrol_document (
    rowid            INT NOT NULL AUTO_INCREMENT,
    ref              VARCHAR(50)  NOT NULL,
    title            VARCHAR(255) NOT NULL,
    category_id      INT          DEFAULT NULL,
    description      TEXT,
    current_version  VARCHAR(20)  DEFAULT '0.1',
    status           VARCHAR(20)  NOT NULL DEFAULT 'Draft',
    review_date      DATE         DEFAULT NULL,
    entity           INT          NOT NULL DEFAULT 1,
    fk_user_creat    INT          DEFAULT NULL,
    fk_user_modif    INT          DEFAULT NULL,
    tms              TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rowid),
    UNIQUE KEY uniq_ref (ref, entity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS llx_doccontrol_document_version (
    rowid           INT NOT NULL AUTO_INCREMENT,
    fk_document     INT NOT NULL,
    version_label   VARCHAR(20)  NOT NULL,
    file_path       VARCHAR(255) DEFAULT NULL,
    change_summary  TEXT,
    status          VARCHAR(20)  NOT NULL DEFAULT 'Draft',
    approved_by     INT          DEFAULT NULL,
    approved_at     DATETIME     DEFAULT NULL,
    effective_date  DATE         DEFAULT NULL,
    fk_user_creat   INT          DEFAULT NULL,
    tms             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS llx_doccontrol_ack (
    rowid           INT NOT NULL AUTO_INCREMENT,
    fk_document     INT NOT NULL,
    fk_version      INT NOT NULL,
    fk_user         INT NOT NULL,
    acknowledged_at DATETIME     DEFAULT NULL,
    status          VARCHAR(20)  NOT NULL DEFAULT 'Pending',
    PRIMARY KEY (rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS llx_doccontrol_link (
    rowid       INT NOT NULL AUTO_INCREMENT,
    fk_document INT NOT NULL,
    link_type   VARCHAR(50) NOT NULL,
    link_id     INT NOT NULL,
    PRIMARY KEY (rowid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
