-- 创建销售记录表
CREATE TABLE sales_record
(
    id                  BIGSERIAL PRIMARY KEY,            -- 主键ID
    sale_date           DATE           NOT NULL,          -- 销售日期
    customer_id         BIGINT,                           -- 客户id
    customer            VARCHAR(255)   NOT NULL,          -- 客户
    coal_id             VARCHAR(255)   NOT NULL,          -- 煤种 id
    coal                VARCHAR(255)   NOT NULL,          -- 煤种
    price_including_tax DECIMAL(10, 2) NOT NULL,          -- 单价（含税）
    inventory_quantity  DECIMAL(10, 0) NOT NULL,          -- 库存数量
    unit                VARCHAR(100)   NOT NULL,          -- 单位
    sale_quantity       DECIMAL(10, 2) NOT NULL,          -- 销售数量
    sale_price          DECIMAL(10, 2) NOT NULL,          -- 销售单价 (含税)
    total_amount        DECIMAL(10, 2) NOT NULL,          -- 销售总价 (含税)
    freight             DECIMAL(10, 2) NOT NULL,          -- 运费
    tax_coal            VARCHAR(36),                      -- 购销煤税率13%
    tax_trans           VARCHAR(36),                      -- 运输税率9%
    gross_profit        DECIMAL(10, 2),                   -- 毛利润
    net_profit          DECIMAL(10, 2),                   -- 净利润
    registrant_id       BIGINT,                           -- 登记人id
    registrant          VARCHAR(255)   NOT NULL,          -- 登记人
    registration_date   DATE           NOT NULL,          -- 登记日期

    create_time         TIMESTAMP WITHOUT TIME ZONE,          -- 上传时间，默认当前时间
    update_time         TIMESTAMP WITHOUT TIME ZONE,          -- 最后更新时间，默认当前时间
    create_by           VARCHAR(255),                     -- 上传人用户名
    update_by           VARCHAR(255),                     -- 最后修改人用户名
    deleted             INT            NOT NULL DEFAULT 0 -- 软删除标志：0=未删除，1=已删除
);

-- 表注释
COMMENT ON TABLE sales_record IS '销售记录表';

-- 添加字段注释
COMMENT ON COLUMN sales_record.id IS '主键ID';
COMMENT ON COLUMN sales_record.sale_date IS '销售日期';
COMMENT ON COLUMN sales_record.customer IS '客户';
COMMENT ON COLUMN sales_record.customer_id IS '客户id';
COMMENT ON COLUMN sales_record.coal IS '煤种';
COMMENT ON COLUMN sales_record.coal_id IS '煤种id';
COMMENT ON COLUMN sales_record.unit IS '单位';
COMMENT ON COLUMN sales_record.price_including_tax IS '单价（含税）';
COMMENT ON COLUMN sales_record.inventory_quantity IS '库存数量';
COMMENT ON COLUMN sales_record.sale_quantity IS '销售数量';
COMMENT ON COLUMN sales_record.sale_price IS '销售单价 (含税)';
COMMENT ON COLUMN sales_record.total_amount IS '销售总价 (含税)';
COMMENT ON COLUMN sales_record.tax_coal IS '购销煤税率13%';
COMMENT ON COLUMN sales_record.tax_trans IS '运输税率9%';
COMMENT ON COLUMN sales_record.gross_profit IS '毛利润';
COMMENT ON COLUMN sales_record.net_profit IS '净利润';
COMMENT ON COLUMN sales_record.registrant IS '登记人';
COMMENT ON COLUMN sales_record.registrant_id IS '登记人id';
COMMENT ON COLUMN sales_record.registration_date IS '登记日期';

COMMENT ON COLUMN sales_record.deleted IS '软删除标志，0=未删除，1=已删除';
COMMENT ON COLUMN sales_record.create_by IS '创建该记录的用户';
COMMENT ON COLUMN sales_record.create_time IS '记录创建时间';
COMMENT ON COLUMN sales_record.update_by IS '最后修改该记录的用户';
COMMENT ON COLUMN sales_record.update_time IS '记录最后更新时间';