"""高校人事请假考勤：业务表 DDL 与表名常量。"""

from __future__ import annotations

EMPLOYEES_TABLE = "employees"
LEAVE_POLICIES_TABLE = "leave_policies"
ATTENDANCE_HOLIDAYS_TABLE = "attendance_holidays"
LEAVE_REQUESTS_TABLE = "leave_requests"
LEAVE_BALANCES_TABLE = "leave_balances"

BUSINESS_TABLES: tuple[str, ...] = (
    EMPLOYEES_TABLE,
    LEAVE_POLICIES_TABLE,
    ATTENDANCE_HOLIDAYS_TABLE,
    LEAVE_REQUESTS_TABLE,
    LEAVE_BALANCES_TABLE,
)

EMPLOYEES_DDL = """
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    employee_no TEXT,
    department TEXT,
    position TEXT,
    position_category TEXT,
    join_date TEXT,
    monthly_salary REAL NOT NULL DEFAULT 0,
    annual_leave_remaining REAL NOT NULL DEFAULT 0,
    is_active INTEGER NOT NULL DEFAULT 1,
    notes TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

LEAVE_POLICIES_DDL = """
CREATE TABLE IF NOT EXISTS leave_policies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    leave_type TEXT NOT NULL,
    max_days REAL,
    day_basis TEXT NOT NULL DEFAULT 'workday',
    paid_days REAL NOT NULL DEFAULT 0,
    partial_pay_ratio REAL NOT NULL DEFAULT 1.0,
    unpaid_after_days REAL,
    requires_certificate INTEGER NOT NULL DEFAULT 0,
    can_offset_annual INTEGER NOT NULL DEFAULT 0,
    approval_threshold_dept INTEGER NOT NULL DEFAULT 3,
    approval_threshold_hr INTEGER NOT NULL DEFAULT 10,
    notes TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

ATTENDANCE_HOLIDAYS_DDL = """
CREATE TABLE IF NOT EXISTS attendance_holidays (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    holiday_date TEXT NOT NULL,
    holiday_name TEXT,
    holiday_type TEXT NOT NULL DEFAULT '法定假日',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

LEAVE_REQUESTS_DDL = """
CREATE TABLE IF NOT EXISTS leave_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER NOT NULL,
    leave_type TEXT NOT NULL,
    start_date TEXT NOT NULL,
    end_date TEXT NOT NULL,
    has_certificate INTEGER NOT NULL DEFAULT 0,
    certificate_type TEXT,
    natural_days INTEGER,
    working_days INTEGER,
    monthly_salary REAL,
    working_days_in_month INTEGER,
    daily_salary REAL,
    deduction_amount REAL,
    annual_leave_used REAL,
    remaining_annual_leave REAL,
    approval_level TEXT,
    status TEXT NOT NULL DEFAULT 'draft',
    params_json TEXT,
    summary TEXT,
    error_message TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

LEAVE_BALANCES_DDL = """
CREATE TABLE IF NOT EXISTS leave_balances (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER NOT NULL,
    balance_type TEXT NOT NULL,
    total_days REAL,
    used_days REAL NOT NULL DEFAULT 0,
    remaining_days REAL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
)
"""

TABLE_DDL: tuple[tuple[str, str], ...] = (
    (EMPLOYEES_TABLE, EMPLOYEES_DDL),
    (LEAVE_POLICIES_TABLE, LEAVE_POLICIES_DDL),
    (ATTENDANCE_HOLIDAYS_TABLE, ATTENDANCE_HOLIDAYS_DDL),
    (LEAVE_REQUESTS_TABLE, LEAVE_REQUESTS_DDL),
    (LEAVE_BALANCES_TABLE, LEAVE_BALANCES_DDL),
)