203 lines
9.5 KiB
SQL
203 lines
9.5 KiB
SQL
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE corporate_customer (
|
|
customer_id TEXT PRIMARY KEY,
|
|
customer_name TEXT NOT NULL,
|
|
unified_credit_code TEXT NOT NULL UNIQUE,
|
|
industry TEXT NOT NULL,
|
|
registered_capital REAL NOT NULL,
|
|
established_date TEXT NOT NULL,
|
|
province TEXT NOT NULL,
|
|
risk_level TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE lease_project (
|
|
project_id TEXT PRIMARY KEY,
|
|
project_name TEXT NOT NULL,
|
|
customer_id TEXT NOT NULL REFERENCES corporate_customer(customer_id),
|
|
business_type TEXT NOT NULL,
|
|
stage TEXT NOT NULL,
|
|
manager_name TEXT NOT NULL,
|
|
currency TEXT NOT NULL DEFAULT 'CNY',
|
|
created_at TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE financing_scheme (
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
version_no INTEGER NOT NULL,
|
|
facility_amount REAL NOT NULL,
|
|
facility_term_months INTEGER NOT NULL,
|
|
lease_rate REAL NOT NULL,
|
|
deposit_amount REAL NOT NULL,
|
|
repayment_frequency TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
effective_at TEXT NOT NULL,
|
|
PRIMARY KEY (project_id, version_no)
|
|
);
|
|
|
|
CREATE TABLE financial_statement (
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
period TEXT NOT NULL,
|
|
scope TEXT NOT NULL,
|
|
currency_unit TEXT NOT NULL DEFAULT '万元',
|
|
total_assets REAL,
|
|
total_liabilities REAL,
|
|
revenue REAL,
|
|
net_profit REAL,
|
|
operating_cashflow REAL,
|
|
audited INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TEXT NOT NULL,
|
|
PRIMARY KEY (project_id, period, scope)
|
|
);
|
|
|
|
CREATE TABLE lease_asset (
|
|
asset_id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
asset_name TEXT NOT NULL,
|
|
serial_no TEXT,
|
|
supplier TEXT,
|
|
quantity INTEGER NOT NULL,
|
|
original_value REAL,
|
|
appraised_value REAL,
|
|
ownership_status TEXT NOT NULL,
|
|
location TEXT,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE insurance_policy (
|
|
policy_id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
asset_id TEXT REFERENCES lease_asset(asset_id),
|
|
insurer TEXT NOT NULL,
|
|
policy_no TEXT NOT NULL,
|
|
insured_amount REAL NOT NULL,
|
|
effective_date TEXT NOT NULL,
|
|
expiry_date TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE guarantee_measure (
|
|
guarantee_id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
guarantee_type TEXT NOT NULL,
|
|
guarantor_name TEXT NOT NULL,
|
|
guarantee_amount REAL NOT NULL,
|
|
status TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE credit_snapshot (
|
|
snapshot_id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
report_date TEXT NOT NULL,
|
|
overdue_count INTEGER NOT NULL,
|
|
query_count_6m INTEGER NOT NULL,
|
|
adverse_balance REAL NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE legal_case (
|
|
case_id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
case_type TEXT NOT NULL,
|
|
case_status TEXT NOT NULL,
|
|
amount REAL NOT NULL,
|
|
filing_date TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE repayment_source (
|
|
source_id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
source_type TEXT NOT NULL,
|
|
payer_name TEXT NOT NULL,
|
|
annual_amount REAL,
|
|
evidence_status TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE project_document (
|
|
document_id TEXT PRIMARY KEY,
|
|
project_id TEXT NOT NULL REFERENCES lease_project(project_id),
|
|
document_type TEXT NOT NULL,
|
|
file_name TEXT NOT NULL,
|
|
document_date TEXT,
|
|
status TEXT NOT NULL,
|
|
storage_ref TEXT NOT NULL,
|
|
updated_at TEXT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX idx_financial_project ON financial_statement(project_id, period);
|
|
CREATE INDEX idx_asset_project ON lease_asset(project_id);
|
|
CREATE INDEX idx_credit_project ON credit_snapshot(project_id, report_date);
|
|
CREATE INDEX idx_legal_project ON legal_case(project_id);
|
|
CREATE INDEX idx_document_project ON project_document(project_id);
|
|
|
|
INSERT INTO corporate_customer VALUES
|
|
('CUS-A','兴华精密制造（演示）','91370000DEMO00001A','装备制造',12000,'2012-05-18','山东','低','2026-09-01T08:30:00+08:00'),
|
|
('CUS-B','恒远设备（演示）','91370000DEMO00002B','装备制造',8000,'2016-03-11','山东','中','2026-09-01T08:35:00+08:00'),
|
|
('CUS-C','新能服务（演示）','91370000DEMO00003C','新能源服务',5000,'2019-08-26','山东','中','2026-09-01T08:40:00+08:00');
|
|
|
|
INSERT INTO lease_project VALUES
|
|
('DEMO-A','高端数控设备售后回租','CUS-A','售后回租','报告准备','张敏','CNY','2026-07-02T09:10:00+08:00','2026-09-01T09:00:00+08:00'),
|
|
('DEMO-B','生产线设备直接租赁','CUS-B','直接租赁','补件中','张敏','CNY','2026-07-18T10:20:00+08:00','2026-09-01T09:05:00+08:00'),
|
|
('DEMO-C','分布式能源设备租赁','CUS-C','直接租赁','独立评审','李雯','CNY','2026-06-26T14:15:00+08:00','2026-09-01T09:10:00+08:00');
|
|
|
|
INSERT INTO financing_scheme VALUES
|
|
('DEMO-A',1,3000,36,0.052,300,'按季','生效','2026-08-20T10:00:00+08:00'),
|
|
('DEMO-B',1,4800,36,0.056,480,'按月','已替换','2026-08-12T10:00:00+08:00'),
|
|
('DEMO-B',2,5000,36,0.055,500,'按月','生效','2026-08-28T10:00:00+08:00'),
|
|
('DEMO-C',1,4000,36,0.058,400,'按季','生效','2026-08-16T10:00:00+08:00');
|
|
|
|
INSERT INTO financial_statement VALUES
|
|
('DEMO-A','2024','合并','万元',16500,6900,14200,720,610,1,'2025-04-20T09:00:00+08:00'),
|
|
('DEMO-A','2025','合并','万元',18000,7200,15800,860,680,1,'2026-04-22T09:00:00+08:00'),
|
|
('DEMO-B','2024','合并','万元',18600,11600,17100,540,420,1,'2025-04-25T09:00:00+08:00'),
|
|
('DEMO-B','2025','合并','万元',20000,13200,18200,310,NULL,1,'2026-04-26T09:00:00+08:00'),
|
|
('DEMO-C','2024','合并','万元',14800,7600,11600,610,590,1,'2025-04-18T09:00:00+08:00'),
|
|
('DEMO-C','2025','合并','万元',16000,8600,12700,680,650,1,'2026-04-19T09:00:00+08:00');
|
|
|
|
INSERT INTO lease_asset VALUES
|
|
('AST-A-01','DEMO-A','五轴加工中心','SN-A-001','北方数控设备有限公司',1,2500,2400,'权属已核验','济南','2026-08-29T11:00:00+08:00'),
|
|
('AST-A-02','DEMO-A','精密检测线','SN-A-002','华东检测科技有限公司',1,1900,1800,'权属已核验','济南','2026-08-29T11:05:00+08:00'),
|
|
('AST-B-01','DEMO-B','自动装配线','SN-B-001','齐鲁工业装备有限公司',1,3000,2900,'待补发票','青岛','2026-08-30T11:00:00+08:00'),
|
|
('AST-B-02','DEMO-B','智能仓储线','SN-B-002','齐鲁工业装备有限公司',1,2700,2500,'权属已核验','青岛','2026-08-30T11:05:00+08:00'),
|
|
('AST-B-03','DEMO-B','智能仓储线重复登记','SN-B-002','齐鲁工业装备有限公司',1,0,0,'重复待核验','青岛','2026-08-30T11:06:00+08:00'),
|
|
('AST-C-01','DEMO-C','工商业储能系统','SN-C-001','绿色能源设备有限公司',1,5500,5200,'权属已核验','烟台','2026-08-31T11:00:00+08:00');
|
|
|
|
INSERT INTO insurance_policy VALUES
|
|
('POL-A-01','DEMO-A','AST-A-01','示例财产保险公司','POL-DEMO-A-001',4000,'2026-08-01','2027-07-31','有效','2026-08-30T13:00:00+08:00'),
|
|
('POL-B-01','DEMO-B','AST-B-01','示例财产保险公司','POL-DEMO-B-001',4500,'2026-08-15','2027-08-14','有效','2026-08-30T13:05:00+08:00'),
|
|
('POL-C-01','DEMO-C','AST-C-01','示例财产保险公司','POL-DEMO-C-001',5000,'2026-07-20','2027-07-19','有效','2026-08-30T13:10:00+08:00');
|
|
|
|
INSERT INTO guarantee_measure VALUES
|
|
('GAR-A-01','DEMO-A','保证金','承租人',300,'有效','2026-08-30T14:00:00+08:00'),
|
|
('GAR-A-02','DEMO-A','连带责任保证','兴华控股（演示）',3000,'有效','2026-08-30T14:05:00+08:00'),
|
|
('GAR-B-01','DEMO-B','保证金','承租人',500,'有效','2026-08-30T14:10:00+08:00'),
|
|
('GAR-C-01','DEMO-C','保证金','承租人',400,'有效','2026-08-30T14:15:00+08:00'),
|
|
('GAR-C-02','DEMO-C','应收账款质押','新能项目公司（演示）',2600,'材料待补','2026-08-30T14:20:00+08:00');
|
|
|
|
INSERT INTO credit_snapshot VALUES
|
|
('CR-A-202608','DEMO-A','2026-08-31',0,3,0,'2026-09-01T08:00:00+08:00'),
|
|
('CR-B-202608','DEMO-B','2026-08-31',0,7,0,'2026-09-01T08:05:00+08:00'),
|
|
('CR-C-202608','DEMO-C','2026-08-31',1,11,35,'2026-09-01T08:10:00+08:00');
|
|
|
|
INSERT INTO legal_case VALUES
|
|
('CASE-B-01','DEMO-B','买卖合同纠纷','已结案',80,'2025-11-12','2026-08-28T15:00:00+08:00'),
|
|
('CASE-C-01','DEMO-C','执行案件','执行中',120,'2026-05-16','2026-08-28T15:05:00+08:00');
|
|
|
|
INSERT INTO repayment_source VALUES
|
|
('REPAY-A-01','DEMO-A','主营业务回款','核心客户组合',3200,'已提供合同及流水','2026-08-30T16:00:00+08:00'),
|
|
('REPAY-B-01','DEMO-B','主营业务回款','下游客户组合',2800,'缺少最新流水','2026-08-30T16:05:00+08:00'),
|
|
('REPAY-C-01','DEMO-C','特定项目回款','新能项目公司（演示）',2600,'缺少到账依据','2026-08-30T16:10:00+08:00');
|
|
|
|
INSERT INTO project_document VALUES
|
|
('DOC-A-01','DEMO-A','财务报表','2025年度审计报告.pdf','2026-04-22','已核验','demo://DEMO-A/financial-2025','2026-08-30T17:00:00+08:00'),
|
|
('DOC-A-02','DEMO-A','设备清单','租赁物清单.xlsx','2026-08-29','已核验','demo://DEMO-A/assets','2026-08-30T17:05:00+08:00'),
|
|
('DOC-B-01','DEMO-B','财务报表','2025年度审计报告.pdf','2026-04-26','已核验','demo://DEMO-B/financial-2025','2026-08-30T17:10:00+08:00'),
|
|
('DOC-B-02','DEMO-B','设备清单','设备清单_补充版.xlsx','2026-08-30','待核验','demo://DEMO-B/assets-v2','2026-08-30T17:15:00+08:00'),
|
|
('DOC-C-01','DEMO-C','回款依据','项目回款安排说明.docx','2026-08-28','待补充','demo://DEMO-C/repayment','2026-08-30T17:20:00+08:00');
|