"""高校人事请假考勤：数据管理展示元数据。"""

from __future__ import annotations

import json

from db.display_metadata import (
    DATETIME_UNIX_SECONDS,
    list_physical_column_names,
    seed_column_metadata_batch,
    upsert_table_metadata,
)
from db.schema import (
    ATTENDANCE_HOLIDAYS_TABLE,
    EMPLOYEES_TABLE,
    LEAVE_BALANCES_TABLE,
    LEAVE_POLICIES_TABLE,
    LEAVE_REQUESTS_TABLE,
)

IS_ACTIVE_OPTIONS = (
    '[{"value":"1","label":"启用"},'
    '{"value":"0","label":"停用"}]'
)

STATUS_OPTIONS = (
    '[{"value":"draft","label":"草稿"},'
    '{"value":"completed","label":"已完成"},'
    '{"value":"failed","label":"失败"}]'
)

DAY_BASIS_OPTIONS = (
    '[{"value":"workday","label":"工作日"},'
    '{"value":"natural","label":"自然日"}]'
)

POSITION_CATEGORY_OPTIONS = (
    '[{"value":"教师","label":"教师"},'
    '{"value":"行政","label":"行政"},'
    '{"value":"科研","label":"科研"}]'
)

HOLIDAY_TYPE_OPTIONS = (
    '[{"value":"法定假日","label":"法定假日"},'
    '{"value":"寒暑假","label":"寒暑假"},'
    '{"value":"校庆","label":"校庆"},'
    '{"value":"周末","label":"周末"}]'
)

APPROVAL_LEVEL_OPTIONS = (
    '[{"value":"系主任","label":"系主任"},'
    '{"value":"系主任+人事处","label":"系主任+人事处"},'
    '{"value":"系主任+人事处+校领导","label":"系主任+人事处+校领导"}]'
)

BALANCE_TYPE_OPTIONS = (
    '[{"value":"annual","label":"年假"},'
    '{"value":"sick","label":"病假"},'
    '{"value":"marriage","label":"婚假"},'
    '{"value":"maternity","label":"产假"},'
    '{"value":"bereavement","label":"丧假"}]'
)

REQUIRES_CERTIFICATE_OPTIONS = (
    '[{"value":"1","label":"是"},'
    '{"value":"0","label":"否"}]'
)

CAN_OFFSET_OPTIONS = (
    '[{"value":"1","label":"是"},'
    '{"value":"0","label":"否"}]'
)


def _ts(column: str, label: str, *, editable: int = 0) -> dict[str, object]:
    return {
        "column_name": column,
        "display_name": label,
        "visible": 1,
        "searchable": 0,
        "editable": editable,
        "display_type": DATETIME_UNIX_SECONDS,
    }


def _lookup(ref_table: str, value_column: str, label_column: str) -> str:
    return json.dumps(
        {
            "ref_table": ref_table,
            "value_column": value_column,
            "label_column": label_column,
        },
        ensure_ascii=False,
    )


def _seed_table(cursor, table_name: str, columns: tuple[dict[str, object], ...]) -> None:
    physical = list_physical_column_names(cursor, table_name)
    seed_column_metadata_batch(
        cursor,
        table_name=table_name,
        columns=columns,
        valid_column_names=physical,
    )


def seed_leave_display_metadata(cursor) -> None:
    upsert_table_metadata(
        cursor,
        table_name=EMPLOYEES_TABLE,
        display_name="教职工",
        description="教职工基本信息：部门、岗位、入职日期、月薪标准、年假余额",
        grid_help="可用「导入教职工」从 JSON 批量写入；停用教职工不参与请假核算。",
        readonly=0,
    )
    _seed_table(
        cursor,
        EMPLOYEES_TABLE,
        (
            {"column_name": "id", "display_name": "编号", "editable": 0, "searchable": 1},
            {"column_name": "name", "display_name": "姓名", "editable": 1, "searchable": 1},
            {"column_name": "employee_no", "display_name": "工号", "editable": 1, "searchable": 1},
            {"column_name": "department", "display_name": "部门", "editable": 1, "searchable": 1},
            {"column_name": "position", "display_name": "岗位", "editable": 1, "searchable": 1},
            {
                "column_name": "position_category",
                "display_name": "岗位类别",
                "editable": 1,
                "searchable": 1,
                "display_type": "enum",
                "options_json": POSITION_CATEGORY_OPTIONS,
            },
            {"column_name": "join_date", "display_name": "入职日期", "editable": 1, "searchable": 1},
            {
                "column_name": "monthly_salary",
                "display_name": "月薪标准",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "annual_leave_remaining",
                "display_name": "年假余额",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "is_active",
                "display_name": "启用",
                "editable": 1,
                "searchable": 1,
                "display_type": "enum",
                "options_json": IS_ACTIVE_OPTIONS,
            },
            {"column_name": "notes", "display_name": "备注", "editable": 1, "searchable": 1},
            _ts("created_at", "创建时间"),
            _ts("updated_at", "更新时间"),
        ),
    )

    upsert_table_metadata(
        cursor,
        table_name=LEAVE_POLICIES_TABLE,
        display_name="请假政策",
        description="各类假别的扣薪规则、审批阈值与证件要求",
        grid_help="按学校政策维护各类假别参数；核算时按 leave_type 匹配。",
        readonly=0,
    )
    _seed_table(
        cursor,
        LEAVE_POLICIES_TABLE,
        (
            {"column_name": "id", "display_name": "编号", "editable": 0, "searchable": 1},
            {"column_name": "leave_type", "display_name": "请假类型", "editable": 1, "searchable": 1},
            {
                "column_name": "max_days",
                "display_name": "最大可休天数",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "day_basis",
                "display_name": "计薪日基准",
                "editable": 1,
                "searchable": 1,
                "display_type": "enum",
                "options_json": DAY_BASIS_OPTIONS,
            },
            {
                "column_name": "paid_days",
                "display_name": "带薪天数",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "partial_pay_ratio",
                "display_name": "部分扣薪比例",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "unpaid_after_days",
                "display_name": "无薪起始天数",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "requires_certificate",
                "display_name": "需证件",
                "editable": 1,
                "searchable": 1,
                "display_type": "enum",
                "options_json": REQUIRES_CERTIFICATE_OPTIONS,
            },
            {
                "column_name": "can_offset_annual",
                "display_name": "可抵年假",
                "editable": 1,
                "searchable": 1,
                "display_type": "enum",
                "options_json": CAN_OFFSET_OPTIONS,
            },
            {
                "column_name": "approval_threshold_dept",
                "display_name": "系主任阈值",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "approval_threshold_hr",
                "display_name": "人事处阈值",
                "editable": 1,
                "searchable": 0,
                "display_type": "number",
            },
            {"column_name": "notes", "display_name": "备注", "editable": 1, "searchable": 1},
            _ts("created_at", "创建时间"),
            _ts("updated_at", "更新时间"),
        ),
    )

    upsert_table_metadata(
        cursor,
        table_name=ATTENDANCE_HOLIDAYS_TABLE,
        display_name="考勤日历",
        description="法定节假日、寒暑假、校庆等非工作日",
        grid_help="核算工作日时排除此处登记的日期。",
        readonly=0,
    )
    _seed_table(
        cursor,
        ATTENDANCE_HOLIDAYS_TABLE,
        (
            {"column_name": "id", "display_name": "编号", "editable": 0, "searchable": 1},
            {"column_name": "holiday_date", "display_name": "日期", "editable": 1, "searchable": 1},
            {"column_name": "holiday_name", "display_name": "名称", "editable": 1, "searchable": 1},
            {
                "column_name": "holiday_type",
                "display_name": "类型",
                "editable": 1,
                "searchable": 1,
                "display_type": "enum",
                "options_json": HOLIDAY_TYPE_OPTIONS,
            },
            _ts("created_at", "创建时间"),
            _ts("updated_at", "更新时间"),
        ),
    )

    upsert_table_metadata(
        cursor,
        table_name=LEAVE_REQUESTS_TABLE,
        display_name="请假记录",
        description="请假申请与核算结果（系统写入）",
        grid_help="在教职工表执行「请假核算」后查看扣薪明细与审批层级。",
        readonly=1,
    )
    _seed_table(
        cursor,
        LEAVE_REQUESTS_TABLE,
        (
            {"column_name": "id", "display_name": "编号", "editable": 0, "searchable": 1},
            {
                "column_name": "employee_id",
                "display_name": "教职工",
                "editable": 0,
                "searchable": 1,
                "display_type": "lookup",
                "options_json": _lookup(EMPLOYEES_TABLE, "id", "name"),
            },
            {"column_name": "leave_type", "display_name": "请假类型", "editable": 0, "searchable": 1},
            {"column_name": "start_date", "display_name": "开始日期", "editable": 0, "searchable": 1},
            {"column_name": "end_date", "display_name": "结束日期", "editable": 0, "searchable": 1},
            {
                "column_name": "has_certificate",
                "display_name": "附证件",
                "editable": 0,
                "searchable": 1,
                "display_type": "enum",
                "options_json": REQUIRES_CERTIFICATE_OPTIONS,
            },
            {"column_name": "certificate_type", "display_name": "证件类型", "editable": 0, "searchable": 1},
            {
                "column_name": "natural_days",
                "display_name": "自然日数",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "working_days",
                "display_name": "工作日数",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "monthly_salary",
                "display_name": "月薪",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "working_days_in_month",
                "display_name": "当月应出勤",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "daily_salary",
                "display_name": "日薪",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "deduction_amount",
                "display_name": "应扣工资",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "annual_leave_used",
                "display_name": "已抵年假",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "remaining_annual_leave",
                "display_name": "剩余年假",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "approval_level",
                "display_name": "审批层级",
                "editable": 0,
                "searchable": 1,
                "display_type": "enum",
                "options_json": APPROVAL_LEVEL_OPTIONS,
            },
            {
                "column_name": "status",
                "display_name": "状态",
                "editable": 0,
                "searchable": 1,
                "display_type": "enum",
                "options_json": STATUS_OPTIONS,
            },
            {"column_name": "params_json", "display_name": "参数(JSON)", "editable": 0, "searchable": 0},
            {"column_name": "summary", "display_name": "摘要", "editable": 0, "searchable": 1},
            {"column_name": "error_message", "display_name": "错误信息", "editable": 0, "searchable": 1},
            _ts("created_at", "创建时间"),
            _ts("updated_at", "更新时间"),
        ),
    )

    upsert_table_metadata(
        cursor,
        table_name=LEAVE_BALANCES_TABLE,
        display_name="假期余额",
        description="教职工各类假期余额（系统更新）",
        grid_help="核算后自动维护已用天数与剩余天数。",
        readonly=1,
    )
    _seed_table(
        cursor,
        LEAVE_BALANCES_TABLE,
        (
            {"column_name": "id", "display_name": "编号", "editable": 0, "searchable": 1},
            {
                "column_name": "employee_id",
                "display_name": "教职工",
                "editable": 0,
                "searchable": 1,
                "display_type": "lookup",
                "options_json": _lookup(EMPLOYEES_TABLE, "id", "name"),
            },
            {
                "column_name": "balance_type",
                "display_name": "假期类型",
                "editable": 0,
                "searchable": 1,
                "display_type": "enum",
                "options_json": BALANCE_TYPE_OPTIONS,
            },
            {
                "column_name": "total_days",
                "display_name": "总额度",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "used_days",
                "display_name": "已用",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            {
                "column_name": "remaining_days",
                "display_name": "剩余",
                "editable": 0,
                "searchable": 0,
                "display_type": "number",
            },
            _ts("created_at", "创建时间"),
            _ts("updated_at", "更新时间"),
        ),
    )