"""SQLite 连接与幂等初始化。 模板默认只建数据管理元数据表。业务表由复制后的技能自行 CREATE，并同步展示元数据。 本技能建两张业务表：payroll_config（费率配置，可编辑）与 payslip_snapshots（解析历史，只读）。 """ from __future__ import annotations import sqlite3 from db.display_metadata import ( init_display_metadata, seed_column_metadata_batch, upsert_table_metadata, ) from db.seed_config import seed_default_config from util.runtime_paths import get_db_path def get_conn() -> sqlite3.Connection: return sqlite3.connect(get_db_path()) _PAYROLL_CONFIG_DDL = """ CREATE TABLE IF NOT EXISTS payroll_config ( config_key TEXT NOT NULL, region TEXT NOT NULL DEFAULT 'default', config_value REAL NOT NULL, display_name TEXT NOT NULL, description TEXT, effective_date INTEGER, created_at INTEGER NOT NULL DEFAULT (unixepoch()), updated_at INTEGER NOT NULL DEFAULT (unixepoch()), PRIMARY KEY (config_key, region) ) """ _PAYROLL_CONFIG_UPDATED_AT_TRIGGER = """ CREATE TRIGGER IF NOT EXISTS payroll_config_set_updated_at AFTER UPDATE ON payroll_config FOR EACH ROW BEGIN UPDATE payroll_config SET updated_at = unixepoch() WHERE config_key = NEW.config_key AND region = NEW.region; END """ _PAYSLIP_SNAPSHOTS_DDL = """ CREATE TABLE IF NOT EXISTS payslip_snapshots ( id INTEGER PRIMARY KEY AUTOINCREMENT, snapshot_date INTEGER NOT NULL, period TEXT, base_salary REAL, gross_total REAL NOT NULL, deduction_total REAL NOT NULL, net_salary REAL NOT NULL, reported_net REAL, check_match INTEGER, check_diff REAL, detail_json TEXT, raw_payload TEXT, created_at INTEGER NOT NULL DEFAULT (unixepoch()) ) """ _PAYROLL_CONFIG_COLUMNS: tuple[dict[str, object], ...] = ( { "column_name": "config_key", "display_name": "配置项", "description": "费率配置键，如 social_pension_rate / tax_threshold。", "visible": 1, "searchable": 1, "editable": 0, }, { "column_name": "region", "display_name": "地区", "description": "费率适用地区：default（全国默认）/ beijing / shanghai / guangzhou。", "visible": 1, "searchable": 1, "editable": 0, "options_json": '[{"value":"default","label":"全国默认"},{"value":"beijing","label":"北京"},{"value":"shanghai","label":"上海"},{"value":"guangzhou","label":"广州"}]', }, { "column_name": "config_value", "display_name": "数值", "description": "费率数值（比例为小数，如 0.08 表示 8%）。", "visible": 1, "searchable": 1, "editable": 1, "display_type": "number", }, { "column_name": "display_name", "display_name": "中文名", "description": "配置项的中文展示名。", "visible": 1, "searchable": 1, "editable": 1, }, { "column_name": "description", "display_name": "说明", "description": "配置项的详细说明。", "visible": 1, "searchable": 0, "editable": 1, "display_type": "textarea", }, { "column_name": "effective_date", "display_name": "生效日期", "description": "该费率的生效日期（Unix 秒级时间戳）。", "visible": 1, "searchable": 1, "editable": 1, "display_type": "datetime_unix_seconds", }, { "column_name": "created_at", "display_name": "创建时间", "description": "记录创建时间（Unix 秒级时间戳，自动生成）。", "visible": 1, "searchable": 0, "editable": 0, "display_type": "datetime_unix_seconds", }, { "column_name": "updated_at", "display_name": "更新时间", "description": "记录最后更新时间（Unix 秒级时间戳，自动维护）。", "visible": 1, "searchable": 0, "editable": 0, "display_type": "datetime_unix_seconds", }, ) _PAYSLIP_SNAPSHOTS_COLUMNS: tuple[dict[str, object], ...] = ( {"column_name": "id", "display_name": "编号", "description": "主键", "visible": 1, "searchable": 1, "editable": 0}, { "column_name": "snapshot_date", "display_name": "解析时间", "description": "本次解析执行的时间（Unix 秒级时间戳）。", "visible": 1, "searchable": 1, "editable": 0, "display_type": "datetime_unix_seconds", }, { "column_name": "period", "display_name": "工资期间", "description": "工资所属期间，如 2026-08。", "visible": 1, "searchable": 1, "editable": 0, }, { "column_name": "base_salary", "display_name": "基本工资", "description": "本次解析的基本工资。", "visible": 1, "searchable": 1, "editable": 0, "display_type": "number", }, { "column_name": "gross_total", "display_name": "应发合计", "description": "本次核算的应发合计。", "visible": 1, "searchable": 1, "editable": 0, "display_type": "number", }, { "column_name": "deduction_total", "display_name": "扣除合计", "description": "本次核算的扣除合计。", "visible": 1, "searchable": 1, "editable": 0, "display_type": "number", }, { "column_name": "net_salary", "display_name": "实发工资", "description": "本次核算的实发工资（应发-扣除）。", "visible": 1, "searchable": 1, "editable": 0, "display_type": "number", }, { "column_name": "reported_net", "display_name": "用户自报实发", "description": "用户给出的实发金额，用于核对。", "visible": 1, "searchable": 1, "editable": 0, "display_type": "number", }, { "column_name": "check_match", "display_name": "核对一致", "description": "1=用户自报与计算一致，0=不一致，NULL=未提供自报。", "visible": 1, "searchable": 1, "editable": 0, }, { "column_name": "check_diff", "display_name": "差异金额", "description": "用户自报 - 计算实发。正数表示用户多报，负数表示少报。", "visible": 1, "searchable": 1, "editable": 0, "display_type": "number", }, { "column_name": "detail_json", "display_name": "完整明细", "description": "本次核算的完整明细（JSON 字符串），含各项应发/扣除/估算。", "visible": 1, "searchable": 0, "editable": 0, "display_type": "textarea", }, { "column_name": "raw_payload", "display_name": "原始文本", "description": "用户粘贴的原始工资条文本（隐私保护，默认隐藏）。", "visible": 0, "searchable": 0, "editable": 0, "display_type": "textarea", }, { "column_name": "created_at", "display_name": "创建时间", "description": "记录入库时间（Unix 秒级时间戳，自动生成）。", "visible": 1, "searchable": 0, "editable": 0, "display_type": "datetime_unix_seconds", }, ) def _init_payroll_config_metadata(cursor) -> None: """建 payroll_config 表 + 展示元数据。""" cursor.execute(_PAYROLL_CONFIG_DDL) cursor.execute(_PAYROLL_CONFIG_UPDATED_AT_TRIGGER) upsert_table_metadata( cursor, table_name="payroll_config", display_name="费率配置", description="社保/公积金/个税等费率配置，按地区区分。管理员可编辑数值，影响估算结果。", grid_help="按地区查看费率；双击数值可编辑（仅管理员）。地区 default 为全国默认。", visible=1, readonly=0, ) seed_column_metadata_batch( cursor, table_name="payroll_config", columns=_PAYROLL_CONFIG_COLUMNS, valid_column_names=[str(c["column_name"]) for c in _PAYROLL_CONFIG_COLUMNS], ) def _init_payslip_snapshots_metadata(cursor) -> None: """建 payslip_snapshots 表 + 展示元数据（只读）。""" cursor.execute(_PAYSLIP_SNAPSHOTS_DDL) upsert_table_metadata( cursor, table_name="payslip_snapshots", display_name="解析历史", description="本人工资条解析历史记录。每次解析自动写入，可回顾/对比多月。", grid_help="按工资期间或时间排序查看历史；raw_payload 默认隐藏。", visible=1, readonly=1, default_sort_json='[{"column":"snapshot_date","direction":"desc"},{"column":"id","direction":"desc"}]', default_visible_columns_json='["snapshot_date","period","base_salary","gross_total","deduction_total","net_salary","check_match"]', ) seed_column_metadata_batch( cursor, table_name="payslip_snapshots", columns=_PAYSLIP_SNAPSHOTS_COLUMNS, valid_column_names=[str(c["column_name"]) for c in _PAYSLIP_SNAPSHOTS_COLUMNS], ) def init_db() -> None: conn = get_conn() try: cur = conn.cursor() init_display_metadata(cur) _init_payroll_config_metadata(cur) _init_payslip_snapshots_metadata(cur) seed_default_config(cur) conn.commit() finally: conn.close()