CREATE TABLE IF NOT EXISTS "wms_item" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "code" varchar(20) NOT NULL,
    "name" varchar(60) NOT NULL,
    "unit" varchar(20) DEFAULT NULL,
    "category_id" bigint NOT NULL,
    "brand_id" bigint DEFAULT NULL,
    "remark" varchar(255) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id")
) COMMENT 'WMS 商品表';

CREATE TABLE IF NOT EXISTS "wms_item_sku" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "name" varchar(255) NOT NULL,
    "item_id" bigint NOT NULL,
    "bar_code" varchar(64) DEFAULT NULL,
    "code" varchar(64) DEFAULT NULL,
    "length" decimal(24, 6) DEFAULT NULL,
    "width" decimal(24, 6) DEFAULT NULL,
    "height" decimal(24, 6) DEFAULT NULL,
    "gross_weight" decimal(24, 6) DEFAULT NULL,
    "net_weight" decimal(24, 6) DEFAULT NULL,
    "cost_price" decimal(24, 6) DEFAULT NULL,
    "selling_price" decimal(24, 6) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id")
) COMMENT 'WMS 商品 SKU 表';

CREATE TABLE IF NOT EXISTS "wms_inventory" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "sku_id" bigint NOT NULL,
    "warehouse_id" bigint NOT NULL,
    "quantity" decimal(24, 6) NOT NULL DEFAULT '0',
    "remark" varchar(255) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id"),
    CONSTRAINT "uk_wms_inventory_sku_warehouse" UNIQUE ("sku_id", "warehouse_id", "tenant_id", "deleted")
) COMMENT 'WMS 库存表';

CREATE TABLE IF NOT EXISTS "wms_inventory_history" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "warehouse_id" bigint NOT NULL,
    "sku_id" bigint NOT NULL,
    "quantity" decimal(24, 6) NOT NULL DEFAULT '0',
    "before_quantity" decimal(24, 6) DEFAULT NULL,
    "after_quantity" decimal(24, 6) DEFAULT NULL,
    "batch_no" varchar(64) DEFAULT NULL,
    "production_date" timestamp DEFAULT NULL,
    "expiration_date" timestamp DEFAULT NULL,
    "amount" decimal(24, 6) DEFAULT NULL,
    "remark" varchar(255) DEFAULT NULL,
    "order_id" bigint DEFAULT NULL,
    "order_no" varchar(64) DEFAULT NULL,
    "order_type" tinyint DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id")
) COMMENT 'WMS 库存流水表';

CREATE TABLE IF NOT EXISTS "wms_receipt_order" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "no" varchar(64) NOT NULL,
    "type" int NOT NULL,
    "status" int NOT NULL DEFAULT '0',
    "biz_order_no" varchar(64) DEFAULT NULL,
    "merchant_id" bigint DEFAULT NULL,
    "remark" varchar(255) DEFAULT NULL,
    "warehouse_id" bigint NOT NULL,
    "total_quantity" decimal(20, 2) NOT NULL DEFAULT '0',
    "total_amount" decimal(10, 2) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id")
) COMMENT 'WMS 入库单表';

CREATE TABLE IF NOT EXISTS "wms_receipt_order_detail" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "remark" varchar(255) DEFAULT NULL,
    "order_id" bigint NOT NULL,
    "sku_id" bigint NOT NULL,
    "warehouse_id" bigint NOT NULL,
    "batch_no" varchar(64) DEFAULT NULL,
    "production_date" timestamp DEFAULT NULL,
    "expiration_date" timestamp DEFAULT NULL,
    "quantity" decimal(20, 2) NOT NULL DEFAULT '0',
    "amount" decimal(10, 2) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id")
) COMMENT 'WMS 入库单明细表';

CREATE TABLE IF NOT EXISTS "wms_shipment_order" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "no" varchar(64) NOT NULL,
    "type" int NOT NULL,
    "status" int NOT NULL DEFAULT '0',
    "biz_order_no" varchar(64) DEFAULT NULL,
    "merchant_id" bigint DEFAULT NULL,
    "remark" varchar(255) DEFAULT NULL,
    "warehouse_id" bigint NOT NULL,
    "total_quantity" decimal(20, 2) NOT NULL DEFAULT '0',
    "total_amount" decimal(10, 2) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id")
) COMMENT 'WMS 出库单表';

CREATE TABLE IF NOT EXISTS "wms_shipment_order_detail" (
    "id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "remark" varchar(255) DEFAULT NULL,
    "order_id" bigint NOT NULL,
    "sku_id" bigint NOT NULL,
    "warehouse_id" bigint NOT NULL,
    "batch_no" varchar(64) DEFAULT NULL,
    "production_date" timestamp DEFAULT NULL,
    "expiration_date" timestamp DEFAULT NULL,
    "quantity" decimal(20, 2) NOT NULL DEFAULT '0',
    "amount" decimal(10, 2) DEFAULT NULL,
    "creator" varchar(64) DEFAULT '',
    "create_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updater" varchar(64) DEFAULT '',
    "update_time" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "deleted" bit NOT NULL DEFAULT FALSE,
    "tenant_id" bigint NOT NULL DEFAULT '0',
    PRIMARY KEY ("id")
) COMMENT 'WMS 出库单明细表';