-- SQL to create table of production rules (simplified - no revisions)

CREATE TABLE llx_prod_rules (
    rowid          INT AUTO_INCREMENT PRIMARY KEY,

    from_ref       VARCHAR(64) NOT NULL,
    operation      VARCHAR(64) NOT NULL,
    to_ref         VARCHAR(64) NOT NULL,

    qty_per_from   DECIMAL(10,4) NOT NULL DEFAULT 1,
    workstation    VARCHAR(64) DEFAULT NULL,
    step_order     INT NOT NULL DEFAULT 10,

    is_final       TINYINT(1) NOT NULL DEFAULT 0,
    is_raw         TINYINT(1) NOT NULL DEFAULT 0,
    active         TINYINT(1) NOT NULL DEFAULT 1,

    note           VARCHAR(255) DEFAULT NULL,

    tms            TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    datec          DATETIME NULL,

    fk_user_creat  INTEGER NULL,
    fk_user_modif  INTEGER NULL,

    entity         INT NOT NULL DEFAULT 1,

    -- Unique rule per user
    UNIQUE KEY uk_rule (
        from_ref,
        operation,
        to_ref,
        step_order,
        entity,
        fk_user_creat
    ),

    INDEX idx_prod_rules_from (from_ref),
    INDEX idx_prod_rules_to (to_ref),
    INDEX idx_prod_rules_operation (operation),
    INDEX idx_prod_rules_fk_user_creat (fk_user_creat),
) ENGINE=innodb;
