-- =============================================
-- 资金服务平台 - 用款申请数据库初始化脚本
-- Database: fund_req
-- Version: 2.0
-- Created: 2026-02-17
-- Updated: 2026-03-02 (主键类型改为VARCHAR雪花ID)
-- =============================================

CREATE DATABASE IF NOT EXISTS fund_req DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE fund_req;

-- =============================================
-- 用款申请表 (fund_request)
-- =============================================
CREATE TABLE IF NOT EXISTS fund_request (
    id VARCHAR(32) NOT NULL COMMENT '主键ID（雪花算法）',
    tenant_id VARCHAR(32) NOT NULL COMMENT '租户ID',
    request_no VARCHAR(64) NOT NULL COMMENT '申请单号',
    title VARCHAR(200) COMMENT '申请标题',
    amount DECIMAL(18,2) NOT NULL COMMENT '申请金额',
    currency VARCHAR(16) DEFAULT 'CNY' COMMENT '币种',
    request_type INT COMMENT '用款类型: 1-日常报销, 2-项目付款, 3-预付款, 4-其他',
    payee_name VARCHAR(128) COMMENT '收款单位',
    payee_bank VARCHAR(128) COMMENT '收款银行',
    payee_account VARCHAR(64) COMMENT '收款账号',
    purpose VARCHAR(500) COMMENT '用途说明',
    project_id VARCHAR(32) COMMENT '项目ID',
    customer_id VARCHAR(32) COMMENT '客户ID',
    request_date DATETIME COMMENT '申请日期',
    expected_pay_date DATETIME COMMENT '期望付款日期',
    approval_status INT DEFAULT 0 COMMENT '审批状态: 0-待审批, 1-审批中, 2-审批通过, 3-审批拒绝, 4-已撤回',
    current_node INT COMMENT '当前审批节点',
    approver_id VARCHAR(32) COMMENT '审批人ID',
    approval_time DATETIME COMMENT '审批时间',
    approval_comment VARCHAR(500) COMMENT '审批意见',
    attachments VARCHAR(1000) COMMENT '附件URL',
    remark VARCHAR(500) COMMENT '备注',
    created_by VARCHAR(32) COMMENT '创建人',
    created_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_by VARCHAR(32) COMMENT '更新人',
    updated_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    deleted TINYINT NOT NULL DEFAULT 0 COMMENT '删除标记: 0-未删除, 1-已删除',
    PRIMARY KEY (id),
    UNIQUE KEY uk_tenant_no (tenant_id, request_no, deleted),
    KEY idx_tenant_id (tenant_id),
    KEY idx_project_id (project_id),
    KEY idx_customer_id (customer_id),
    KEY idx_approval_status (approval_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用款申请表';

INSERT INTO fund_request (id, tenant_id, request_no, title, amount, request_type, purpose, project_id, approval_status, created_by, created_time)
VALUES ('1', '1', 'REQ20260101001', '测试用款申请', 50000.00, 2, '测试用款申请', '1', 0, '1', NOW())
ON DUPLICATE KEY UPDATE request_no=request_no;

-- =============================================
-- 脚本执行完成
-- =============================================