yumoqing
a72637b2a3
- Updated core modules: __init__, ai_config, ai_core, attachment, contract, enhanced_contract - Updated mysql.ddl.sql - Updated UI files: contract_detail, contract_edit, contract_list - Added new files: api/*.dspy, contract_list.dspy
152 lines
8.9 KiB
SQL
152 lines
8.9 KiB
SQL
-- Table from contract.json
|
||
CREATE TABLE IF NOT EXISTS `contract` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '合同ID，主键',
|
||
`contract_number` VARCHAR(50) NOT NULL COMMENT '合同编号，唯一标识',
|
||
`title` VARCHAR(200) NOT NULL COMMENT '合同标题',
|
||
`party_a` VARCHAR(100) NOT NULL COMMENT '甲方（我方）',
|
||
`party_b` VARCHAR(100) NOT NULL COMMENT '乙方（对方）',
|
||
`contract_type` VARCHAR(32) NOT NULL COMMENT '合同类型，引用appcodes表的id',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'draft' COMMENT '合同状态：draft-草稿, active-生效, expired-过期, terminated-终止',
|
||
`amount` DECIMAL(15,2) COMMENT '合同金额',
|
||
`start_date` DATE NOT NULL COMMENT '合同开始日期',
|
||
`end_date` DATE NOT NULL COMMENT '合同结束日期',
|
||
`sign_date` DATE COMMENT '签署日期',
|
||
`owner_id` VARCHAR(32) NOT NULL COMMENT '合同负责人，引用users表的id',
|
||
`org_id` VARCHAR(32) NOT NULL COMMENT '所属组织，引用organization表的id',
|
||
`ai_compliance_result` TEXT COMMENT 'AI合规检查结果',
|
||
`ai_key_dates` TEXT COMMENT 'AI提取的关键时点JSON数据',
|
||
`payment_terms` TEXT COMMENT '付款节点规则，如\'30%预付款+50%进度款+20%尾款\'',
|
||
`credit_period` INT COMMENT '账期天数',
|
||
`penalty_clause` TEXT COMMENT '违约金相关条款',
|
||
`opportunity_id` VARCHAR(32) COMMENT '关联的商机ID',
|
||
`customer_id` VARCHAR(32) COMMENT '关联的客户ID',
|
||
`tax_rate` DECIMAL(5,4) DEFAULT '0.1300' COMMENT '税率，默认13%',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '创建时间',
|
||
`updated_at` TIMESTAMP NOT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合同表';
|
||
|
||
CREATE UNIQUE INDEX `idx_contract_number` ON `contract` (`contract_number`);
|
||
CREATE INDEX `idx_contract_org` ON `contract` (`org_id`);
|
||
CREATE INDEX `idx_contract_status` ON `contract` (`status`);
|
||
|
||
-- Table from contract_ai_config.json
|
||
CREATE TABLE IF NOT EXISTS `contract_ai_config` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT 'AI配置ID',
|
||
`ai_service_url` VARCHAR(500) NOT NULL COMMENT 'AI服务URL地址',
|
||
`api_key` VARCHAR(255) NOT NULL COMMENT 'API密钥',
|
||
`org_id` VARCHAR(32) NOT NULL COMMENT '所属组织',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '创建时间',
|
||
`updated_at` TIMESTAMP NOT NULL COMMENT '更新时间',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='AI配置表';
|
||
|
||
CREATE UNIQUE INDEX `idx_ai_config_org` ON `contract_ai_config` (`org_id`);
|
||
|
||
-- Table from contract_attachment.json
|
||
CREATE TABLE IF NOT EXISTS `contract_attachment` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '附件ID，主键',
|
||
`contract_id` VARCHAR(32) NOT NULL COMMENT '关联的合同ID，引用contract表的id',
|
||
`file_name` VARCHAR(255) NOT NULL COMMENT '文件名',
|
||
`file_path` VARCHAR(500) NOT NULL COMMENT '文件存储路径',
|
||
`file_size` INT NOT NULL COMMENT '文件大小（字节）',
|
||
`file_type` VARCHAR(50) NOT NULL COMMENT '文件类型（MIME类型）',
|
||
`version` INT NOT NULL DEFAULT '1' COMMENT '文件版本号',
|
||
`description` VARCHAR(200) COMMENT '附件描述',
|
||
`uploaded_by` VARCHAR(32) NOT NULL COMMENT '上传人，引用users表的id',
|
||
`org_id` VARCHAR(32) NOT NULL COMMENT '所属组织',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '上传时间',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合同附件表';
|
||
|
||
CREATE INDEX `idx_attachment_contract` ON `contract_attachment` (`contract_id`);
|
||
CREATE INDEX `idx_attachment_org` ON `contract_attachment` (`org_id`);
|
||
CREATE INDEX `idx_attachment_version` ON `contract_attachment` (`file_name`, `version`);
|
||
|
||
-- Table from contract_milestones.json
|
||
CREATE TABLE IF NOT EXISTS `contract_milestones` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`contract_id` VARCHAR(32) NOT NULL COMMENT '关联的合同ID',
|
||
`milestone_name` VARCHAR(100) NOT NULL COMMENT '里程碑名称，如\'预付款到账\'、\'产品交付\'、\'验收完成\'',
|
||
`milestone_type` VARCHAR(20) NOT NULL COMMENT '里程碑类型：payment=付款, delivery=交付, acceptance=验收',
|
||
`planned_date` DATE NOT NULL COMMENT '计划完成日期',
|
||
`actual_date` DATE COMMENT '实际完成日期',
|
||
`amount` DECIMAL(15,2) COMMENT '该里程碑关联的金额',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态：pending=待处理, completed=已完成, overdue=已逾期',
|
||
`description` TEXT COMMENT '里程碑详细描述',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '创建时间',
|
||
`updated_at` TIMESTAMP NOT NULL COMMENT '最后更新时间',
|
||
`reminder_sent` VARCHAR(1) NOT NULL DEFAULT '0' COMMENT '逾期提醒是否已发送：1=是, 0=否',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合同里程碑管理表';
|
||
|
||
CREATE INDEX `idx_milestones_contract` ON `contract_milestones` (`contract_id`);
|
||
CREATE INDEX `idx_milestones_status` ON `contract_milestones` (`status`);
|
||
CREATE INDEX `idx_milestones_planned` ON `contract_milestones` (`planned_date`);
|
||
|
||
-- Table from contract_versions.json
|
||
CREATE TABLE IF NOT EXISTS `contract_versions` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`contract_id` VARCHAR(32) NOT NULL COMMENT '关联的合同ID',
|
||
`version_number` VARCHAR(20) NOT NULL COMMENT '版本号，如v1.0, v1.1等',
|
||
`content` TEXT NOT NULL COMMENT '合同完整内容或差异内容',
|
||
`diff_content` TEXT COMMENT '与上一版本的差异内容（HTML格式）',
|
||
`modified_by` VARCHAR(32) NOT NULL COMMENT '修改人用户ID',
|
||
`modified_reason` TEXT COMMENT '版本修改原因',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '版本创建时间',
|
||
`is_current` VARCHAR(1) NOT NULL DEFAULT '0' COMMENT '是否为当前生效版本：1=是, 0=否',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='合同版本管理表';
|
||
|
||
CREATE INDEX `idx_contract_versions_contract` ON `contract_versions` (`contract_id`);
|
||
CREATE UNIQUE INDEX `idx_contract_versions_version` ON `contract_versions` (`contract_id`, `version_number`);
|
||
CREATE INDEX `idx_contract_versions_current` ON `contract_versions` (`contract_id`, `is_current`);
|
||
|
||
-- Table from order_payments.json
|
||
CREATE TABLE IF NOT EXISTS `order_payments` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`order_id` VARCHAR(32) NOT NULL COMMENT '关联的订单ID',
|
||
`payment_amount` DECIMAL(15,2) NOT NULL COMMENT '本次付款金额',
|
||
`payment_date` DATE NOT NULL COMMENT '付款日期',
|
||
`payment_method` VARCHAR(50) COMMENT '付款方式',
|
||
`payment_reference` VARCHAR(100) COMMENT '付款凭证号或参考号',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'pending' COMMENT '状态：pending=待确认, confirmed=已确认, rejected=已拒绝',
|
||
`notes` TEXT COMMENT '付款备注',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '创建时间',
|
||
`confirmed_at` TIMESTAMP COMMENT '付款确认时间',
|
||
`confirmed_by` VARCHAR(32) COMMENT '付款确认人ID',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单付款记录表';
|
||
|
||
CREATE INDEX `idx_payments_order` ON `order_payments` (`order_id`);
|
||
CREATE INDEX `idx_payments_status` ON `order_payments` (`status`);
|
||
CREATE INDEX `idx_payments_date` ON `order_payments` (`payment_date`);
|
||
|
||
-- Table from orders.json
|
||
CREATE TABLE IF NOT EXISTS `orders` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`order_number` VARCHAR(50) NOT NULL COMMENT '订单编号，唯一标识',
|
||
`contract_id` VARCHAR(32) NOT NULL COMMENT '关联的合同ID',
|
||
`customer_id` VARCHAR(32) NOT NULL COMMENT '客户ID',
|
||
`order_type` VARCHAR(20) NOT NULL COMMENT '订单类型：advance=预付款, progress=进度款, final=尾款, other=其他',
|
||
`delivery_batch` VARCHAR(100) COMMENT '交付批次，如\'Q3季度服务交付\'',
|
||
`acceptance_deadline` DATE COMMENT '验收截止日期',
|
||
`amount` DECIMAL(15,2) NOT NULL COMMENT '该订单的金额',
|
||
`paid_amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '已支付金额',
|
||
`tax_rate` DECIMAL(5,4) NOT NULL DEFAULT '0.1300' COMMENT '税率',
|
||
`credit_period` INT COMMENT '账期天数要求',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT '状态：active=活跃, completed=完成, cancelled=取消',
|
||
`description` TEXT COMMENT '订单详细描述',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '创建时间',
|
||
`updated_at` TIMESTAMP NOT NULL COMMENT '最后更新时间',
|
||
`owner_id` VARCHAR(32) NOT NULL COMMENT '订单负责人ID',
|
||
`org_id` VARCHAR(32) NOT NULL COMMENT '所属组织ID',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';
|
||
|
||
CREATE UNIQUE INDEX `idx_orders_number` ON `orders` (`order_number`);
|
||
CREATE INDEX `idx_orders_contract` ON `orders` (`contract_id`);
|
||
CREATE INDEX `idx_orders_customer` ON `orders` (`customer_id`);
|
||
CREATE INDEX `idx_orders_status` ON `orders` (`status`);
|
||
CREATE INDEX `idx_orders_owner` ON `orders` (`owner_id`);
|