query-payslip-default/scripts/db/seed_config.py

"""费率配置 seed：全国默认 + 北京 + 上海 + 广州 4 套。

幂等：已存在的 (config_key, region) 不会被覆盖，避免用户编辑后被重置。
首次 init_db 时插入；之后用户改的值会保留。
"""

from __future__ import annotations

import sqlite3
from typing import Any

# 7 级累计预扣税率表（2024 起，月度换算为累计）
# 级数：累计预扣预缴应纳税所得额上限 / 税率 / 速算扣除数
TAX_BRACKETS_JSON = """[
  {"level":1,"upper":36000,"rate":0.03,"quick_deduction":0},
  {"level":2,"upper":144000,"rate":0.10,"quick_deduction":2520},
  {"level":3,"upper":300000,"rate":0.20,"quick_deduction":16920},
  {"level":4,"upper":420000,"rate":0.25,"quick_deduction":31920},
  {"level":5,"upper":660000,"rate":0.30,"quick_deduction":52920},
  {"level":6,"upper":960000,"rate":0.35,"quick_deduction":85920},
  {"level":7,"upper":null,"rate":0.45,"quick_deduction":181920}
]"""

# 各地区费率配置
# config_key: (display_name, description, default_value, beijing_value, shanghai_value, guangzhou_value)
# None 表示该地区使用全国默认值
_CONFIG_ROWS: list[dict[str, Any]] = [
    {
        "config_key": "social_pension_rate",
        "display_name": "养老保险个人比例",
        "description": "基本养老保险个人缴费比例。",
        "values": {"default": 0.08, "beijing": 0.08, "shanghai": 0.08, "guangzhou": 0.08},
    },
    {
        "config_key": "social_medical_rate",
        "display_name": "医疗保险个人比例",
        "description": "基本医疗保险个人缴费比例。",
        "values": {"default": 0.02, "beijing": 0.02, "shanghai": 0.02, "guangzhou": 0.02},
    },
    {
        "config_key": "social_unemployment_rate",
        "display_name": "失业保险个人比例",
        "description": "失业保险个人缴费比例。",
        "values": {"default": 0.005, "beijing": 0.0005, "shanghai": 0.005, "guangzhou": 0.002},
    },
    {
        "config_key": "social_base_min",
        "display_name": "社保缴费下限",
        "description": "社保缴费基数下限；低于此值按下限计。",
        "values": {"default": 5000, "beijing": 6326, "shanghai": 7310, "guangzhou": 5500},
    },
    {
        "config_key": "social_base_max",
        "display_name": "社保缴费上限",
        "description": "社保缴费基数上限；高于此值按上限计。",
        "values": {"default": 30000, "beijing": 35283, "shanghai": 36549, "guangzhou": 27501},
    },
    {
        "config_key": "housing_fund_rate",
        "display_name": "公积金个人比例",
        "description": "住房公积金个人缴费比例。",
        "values": {"default": 0.07, "beijing": 0.12, "shanghai": 0.07, "guangzhou": 0.05},
    },
    {
        "config_key": "housing_fund_base_min",
        "display_name": "公积金缴费下限",
        "description": "公积金缴费基数下限。",
        "values": {"default": 2500, "beijing": 2420, "shanghai": 2590, "guangzhou": 2300},
    },
    {
        "config_key": "housing_fund_base_max",
        "display_name": "公积金缴费上限",
        "description": "公积金缴费基数上限。",
        "values": {"default": 30000, "beijing": 33891, "shanghai": 36549, "guangzhou": 36771},
    },
    {
        "config_key": "tax_threshold",
        "display_name": "个税起征点",
        "description": "个人所得税基本减除费用标准（5000 元/月）。",
        "values": {"default": 5000, "beijing": 5000, "shanghai": 5000, "guangzhou": 5000},
    },
    {
        "config_key": "tax_brackets_json",
        "display_name": "个税税率表",
        "description": "7 级累计预扣税率表（JSON 字符串）。",
        "values": {"default": TAX_BRACKETS_JSON, "beijing": None, "shanghai": None, "guangzhou": None},
    },
]

_REGIONS = ("default", "beijing", "shanghai", "guangzhou")


def _insert_if_absent(
    cursor: sqlite3.Cursor,
    *,
    config_key: str,
    region: str,
    config_value: float,
    display_name: str,
    description: str,
) -> None:
    """幂等插入：已存在则跳过，不覆盖用户编辑。"""
    cursor.execute(
        "SELECT 1 FROM payroll_config WHERE config_key=? AND region=?",
        (config_key, region),
    )
    if cursor.fetchone() is not None:
        return
    cursor.execute(
        """
        INSERT INTO payroll_config (config_key, region, config_value, display_name, description, effective_date)
        VALUES (?, ?, ?, ?, ?, NULL)
        """,
        (config_key, region, config_value, display_name, description),
    )


def seed_default_config(cursor: sqlite3.Cursor) -> None:
    """幂等 seed 4 套地区费率配置。

    北上广若某项为 None，则该地区该项使用全国默认值（不插入，查询时回退）。
    """
    for row in _CONFIG_ROWS:
        key = row["config_key"]
        display_name = row["display_name"]
        description = row["description"]
        values = row["values"]

        for region in _REGIONS:
            value = values.get(region)
            if value is None:
                # 该地区该项沿用全国默认（不单独插入）
                continue
            _insert_if_absent(
                cursor,
                config_key=key,
                region=region,
                config_value=value,
                display_name=display_name,
                description=description,
            )