payment-platform/java/sql/012_invoice_tables.sql

-- 行业发票平台 — 全部表 DDL
-- 反向开票/报废产品收购 模块

-- 1. 交易订单
CREATE TABLE IF NOT EXISTS public.pay_invoice_order (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,
    enterprise_id   VARCHAR(64) NOT NULL,

    order_no                VARCHAR(64) NOT NULL,
    alipay_trade_no         VARCHAR(64),
    order_time              TIMESTAMPTZ,
    payment_time            TIMESTAMPTZ,
    natural_person_name     VARCHAR(64),
    natural_person_phone    VARCHAR(20),
    collection_account_type VARCHAR(20),
    collection_account      VARCHAR(64),
    tax_amount              DECIMAL(18,2) DEFAULT 0,
    trade_status            VARCHAR(20),
    invoice_no              VARCHAR(64),
    invoice_pre_tax_amount  DECIMAL(18,2),
    invoice_tax_amount      DECIMAL(18,2),
    red_invoice_no          VARCHAR(64),
    clerk_name              VARCHAR(64),
    attachments             TEXT,
    product_code            VARCHAR(64),
    order_total_amount      DECIMAL(18,2),
    goods_amount            DECIMAL(18,2),
    total_tax_paid          DECIMAL(18,2),
    personal_income_tax     DECIMAL(18,2),
    value_added_tax         DECIMAL(18,2),
    urban_maintenance_tax   DECIMAL(18,2),
    education_surcharge     DECIMAL(18,2),
    local_education_surcharge DECIMAL(18,2)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_invoice_order_no ON public.pay_invoice_order(tenant_id, order_no);
CREATE INDEX IF NOT EXISTS idx_invoice_order_trade_status ON public.pay_invoice_order(tenant_id, trade_status);
CREATE INDEX IF NOT EXISTS idx_invoice_order_time ON public.pay_invoice_order(tenant_id, order_time);
COMMENT ON TABLE public.pay_invoice_order IS '行业发票-交易订单';

-- 2. 订单商品明细
CREATE TABLE IF NOT EXISTS public.pay_invoice_order_item (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,
    enterprise_id   VARCHAR(64) NOT NULL,

    order_id        BIGINT NOT NULL,
    seq_no          INT DEFAULT 1,
    goods_name      VARCHAR(128),
    unit_price      DECIMAL(18,4),
    quantity        DECIMAL(18,4),
    amount          DECIMAL(18,2)
);
CREATE INDEX IF NOT EXISTS idx_invoice_order_item_order ON public.pay_invoice_order_item(order_id);
COMMENT ON TABLE public.pay_invoice_order_item IS '行业发票-订单商品明细';

-- 3. 订单税额明细
CREATE TABLE IF NOT EXISTS public.pay_invoice_tax_detail (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,
    enterprise_id   VARCHAR(64) NOT NULL,

    order_id        BIGINT NOT NULL,
    tax_type        VARCHAR(32),
    tax_name        VARCHAR(64),
    tax_amount      DECIMAL(18,2)
);
COMMENT ON TABLE public.pay_invoice_tax_detail IS '行业发票-订单税额明细';

-- 4. 供应商
CREATE TABLE IF NOT EXISTS public.pay_invoice_supplier (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,
    enterprise_id   VARCHAR(64) NOT NULL,

    name            VARCHAR(64) NOT NULL,
    account_type    VARCHAR(20) NOT NULL,
    account_no      VARCHAR(100) NOT NULL,
    phone           VARCHAR(20),
    confirm_status  VARCHAR(20) DEFAULT 'PENDING'
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_invoice_supplier_account ON public.pay_invoice_supplier(tenant_id, enterprise_id, account_type, account_no);
COMMENT ON TABLE public.pay_invoice_supplier IS '行业发票-供应商';

-- 5. 员工
CREATE TABLE IF NOT EXISTS public.pay_invoice_employee (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,

    name                    VARCHAR(64) NOT NULL,
    phone                   VARCHAR(20),
    id_card                 VARCHAR(20),
    role                    VARCHAR(20) NOT NULL DEFAULT 'CLERK',
    allow_select_supplier   BOOLEAN DEFAULT FALSE,
    sys_user_id             BIGINT
);
COMMENT ON TABLE public.pay_invoice_employee IS '行业发票-员工';

-- 6. 常用商品
CREATE TABLE IF NOT EXISTS public.pay_invoice_goods (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,

    category_id     BIGINT NOT NULL,
    name            VARCHAR(128) NOT NULL,
    unit            VARCHAR(32),
    spec            VARCHAR(64),
    enterprise_id   VARCHAR(64)
);
CREATE INDEX IF NOT EXISTS idx_invoice_goods_cat ON public.pay_invoice_goods(tenant_id, category_id);
COMMENT ON TABLE public.pay_invoice_goods IS '行业发票-常用商品';

-- 7. 商品分类
CREATE TABLE IF NOT EXISTS public.pay_invoice_goods_category (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,

    parent_id       BIGINT DEFAULT 0,
    name            VARCHAR(128) NOT NULL,
    sort_order      INT DEFAULT 0,
    product_code    VARCHAR(64)
);
COMMENT ON TABLE public.pay_invoice_goods_category IS '行业发票-商品分类';

-- 8. 企业配置
CREATE TABLE IF NOT EXISTS public.pay_invoice_company_config (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,
    enterprise_id   VARCHAR(64) NOT NULL,

    company_name            VARCHAR(128),
    tax_no                  VARCHAR(32),
    bank_name               VARCHAR(128),
    bank_account            VARCHAR(64),
    address                 VARCHAR(256),
    phone                   VARCHAR(32),
    tax_region              VARCHAR(128),
    issuer_name             VARCHAR(64),
    issuer_id_card          VARCHAR(20),

    contact_name            VARCHAR(64),
    contact_phone           VARCHAR(20),

    monthly_quota           DECIMAL(18,2) DEFAULT 0,
    available_quota         DECIMAL(18,2) DEFAULT 0,
    downloaded_quota        DECIMAL(18,2) DEFAULT 0,
    used_quota              DECIMAL(18,2) DEFAULT 0,

    tax_method              VARCHAR(32) DEFAULT 'SIMPLIFIED',
    default_invoice_type    VARCHAR(20) DEFAULT 'ORDINARY',
    default_tax_rate        VARCHAR(10) DEFAULT '1%',

    require_audit_before_pay    BOOLEAN DEFAULT FALSE,
    invite_to_supplier          BOOLEAN DEFAULT FALSE,
    show_payee_name             BOOLEAN DEFAULT FALSE,
    employee_quota_enabled      BOOLEAN DEFAULT FALSE
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_invoice_company_config_ent ON public.pay_invoice_company_config(tenant_id, enterprise_id);
COMMENT ON TABLE public.pay_invoice_company_config IS '行业发票-企业配置';

-- 9. 任务记录
CREATE TABLE IF NOT EXISTS public.pay_invoice_task (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,
    enterprise_id   VARCHAR(64) NOT NULL,

    start_time      TIMESTAMPTZ,
    finish_time     TIMESTAMPTZ,
    product         VARCHAR(64),
    task_type       VARCHAR(32),
    task_status     VARCHAR(20),
    file_url        VARCHAR(256),
    total_count     INT,
    success_count   INT,
    fail_count      INT,
    error_msg       TEXT
);
CREATE INDEX IF NOT EXISTS idx_invoice_task_type ON public.pay_invoice_task(tenant_id, task_type);
CREATE INDEX IF NOT EXISTS idx_invoice_task_time ON public.pay_invoice_task(tenant_id, start_time);
COMMENT ON TABLE public.pay_invoice_task IS '行业发票-任务记录';

-- 10. 转账账户
CREATE TABLE IF NOT EXISTS public.pay_invoice_transfer_account (
    id              BIGINT PRIMARY KEY,
    status          VARCHAR(20),
    description     TEXT,
    created_time    TIMESTAMPTZ NOT NULL,
    updated_time    TIMESTAMPTZ NOT NULL,
    tenant_id       BIGINT NOT NULL,
    enterprise_id   VARCHAR(64) NOT NULL,

    total_amount        DECIMAL(18,2) DEFAULT 0,
    pending_amount      DECIMAL(18,2) DEFAULT 0,
    bank_account_name   VARCHAR(128),
    bank_account_no     VARCHAR(64),
    bank_name           VARCHAR(128),
    bank_branch         VARCHAR(128),
    bank_location       VARCHAR(64),
    bank_code           VARCHAR(32)
);
COMMENT ON TABLE public.pay_invoice_transfer_account IS '行业发票-转账账户';