CREATE TABLE IF NOT EXISTS sys_order_header (
    id bigint NOT NULL,
    createby bigint NOT NULL,
    createtime datetime NOT NULL,
    modifyby bigint NOT NULL,
    modifytime datetime NOT NULL,
    code varchar(32) NOT NULL COMMENT 'order code',
    name nvarchar(80) NOT NULL COMMENT 'customer''s order name',
    short_name char(16) NULL COMMENT 'short name',
    sort int NOT NULL COMMENT 'sort',
    priority smallint NULL COMMENT 'priority',
    amount decimal(18,2) NOT NULL COMMENT 'amount',
    discount numeric(10,2) NULL COMMENT 'discount',
    eventdate date NULL COMMENT 'event date',
    lastsynctime timestamp NOT NULL COMMENT 'last sync time',
    isenabled tinyint(1) NOT NULL COMMENT 'enabled',
    isdeleted tinyint(1) NOT NULL COMMENT 'soft delete',
    rowversion bigint NOT NULL COMMENT 'row version',
    CONSTRAINT uk_order_header_code UNIQUE (code),
    UNIQUE KEY uk_order_header_name_sort (name, sort),
    UNIQUE KEY uk_order_header_name_rowversion (name, rowversion),
    KEY idx_order_header_sort (sort),
    PRIMARY KEY (id)
);

CREATE TABLE cts_product_catalog (
    id bigint PRIMARY KEY,
    createby bigint NOT NULL,
    createtime datetime NOT NULL,
    product_code varchar(24) NOT NULL COMMENT 'product code',
    product_name varchar(120) NOT NULL COMMENT 'product name',
    categoryid bigint NULL COMMENT 'category id',
    stock integer NOT NULL COMMENT 'stock',
    level tinyint NULL COMMENT 'level',
    archived bit NOT NULL COMMENT 'archived',
    note text NULL COMMENT 'note',
    summary mediumtext NULL COMMENT 'summary',
    detail longtext NULL COMMENT 'detail',
    UNIQUE INDEX uk_product_catalog_code (product_code),
    INDEX idx_product_catalog_name (product_name)
);

CREATE TABLE main.base_region (
    id bigint NOT NULL,
    region_code varchar(12) NOT NULL COMMENT 'region code',
    region_name nchar(40) NOT NULL COMMENT 'region name',
    region_alias varchar(20) NULL COMMENT 'region alias',
    parentid bigint NULL COMMENT 'parent id',
    rate decimal(5,2) NULL COMMENT 'rate',
    isenabled bit NULL COMMENT 'enabled',
    CONSTRAINT uk_base_region_code UNIQUE (region_code),
    INDEX idx_base_region_parentid (parentid),
    PRIMARY KEY (id)
);

CREATE TABLE sys_order_product_relation (
    id bigint NOT NULL,
    orderid bigint NOT NULL COMMENT 'order id',
    productid bigint NOT NULL COMMENT 'product id',
    relation_type varchar(20) NOT NULL COMMENT 'relation type',
    relation_code varchar(30) NULL COMMENT 'relation code',
    note varchar(100) NULL COMMENT 'note',
    isdeleted tinyint(1) NOT NULL COMMENT 'soft delete',
    rowversion bigint NOT NULL COMMENT 'row version',
    UNIQUE KEY uk_order_product_relation_pair (orderid, productid),
    INDEX idx_order_product_relation_type (relation_type),
    CONSTRAINT fk_order_product_relation_order FOREIGN KEY (orderid) REFERENCES sys_order_header(id),
    CONSTRAINT fk_order_product_relation_product FOREIGN KEY (productid) REFERENCES cts_product_catalog(id),
    PRIMARY KEY (id)
);