"""
回填 ParsedFileRecord 数据

从现有 UploadedFile 记录中：
1. 生成真实 CSV/Excel 文件（模拟原始上传文件）
2. 解析并写入 ParsedFileRecord 表

用法：
    cd backend
    python scripts/backfill_parsed_records.py
"""

import sys
import os
import csv
import io
import random
from pathlib import Path
from sqlalchemy import select

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from app.core.database import async_session_maker as AsyncSessionLocal
from app.models.uploaded_file import UploadedFile, FileType
from app.models.parsed_file_record import ParsedFileRecord
from app.models.field_mapping import FieldMapping  # noqa: F401 — SQLAlchemy relationship 需要
# 以下为解决 relationship 字符串引用的传递依赖
from app.models.company import Company  # noqa: F401
from app.models.user import User  # noqa: F401
from app.models.reconciliation_task import ReconciliationTask  # noqa: F401
from app.models.exception_item import ExceptionItem  # noqa: F401
from app.models.company_rule import CompanyRule  # noqa: F401
from app.models.standard_field import StandardField  # noqa: F401
from app.services.file_storage import file_storage_service


# ── 从 reconciliation.py 复制的工具函数 ──────────────────────────────────────

EMPLOYEE_ID_KEYS = (
    "employee_id", "员工编号", "员工ID", "员工id", "工号", "员工工号"
)
EMPLOYEE_NAME_KEYS = (
    "employee_name", "员工姓名", "姓名", "name", "Name"
)

SALARY_COLUMNS = [
    "员工编号", "姓名", "部门", "基本工资", "岗位工资", "绩效工资",
    "奖金", "加班费", "应发工资", "社保", "公积金", "个税", "实发工资"
]
SOCIAL_COLUMNS = [
    "工号", "姓名", "部门", "养老保险", "医疗保险", "失业保险",
    "工伤保险", "生育保险", "公积金", "社保合计"
]
TAX_COLUMNS = [
    "工号", "姓名", "部门", "应发工资", "养老保险", "医疗保险",
    "失业保险", "公积金", "专项附加扣除", "应税所得", "税率", "个税"
]

EMPLOYEES = [
    ("EMP001", "张明华", "管理层", 35000),
    ("EMP002", "李晓燕", "财务部", 28000),
    ("EMP003", "王建国", "技术部", 30000),
    ("EMP004", "刘伟强", "技术部", 22000),
    ("EMP005", "陈志明", "技术部", 18000),
    ("EMP006", "周建军", "技术部", 17000),
    ("EMP007", "吴海涛", "技术部", 12000),
    ("EMP008", "郑晓峰", "技术部", 15000),
    ("EMP009", "孙丽娜", "产品部", 22000),
    ("EMP010", "马俊杰", "产品部", 14000),
    ("EMP011", "朱婷婷", "产品部", 16000),
    ("EMP012", "胡文静", "运营部", 25000),
    ("EMP013", "林浩然", "运营部", 13000),
    ("EMP014", "何雨晴", "运营部", 12000),
    ("EMP015", "高建峰", "运营部", 9000),
    ("EMP016", "罗晓东", "市场部", 26000),
    ("EMP017", "宋志远", "市场部", 11000),
    ("EMP018", "唐思远", "市场部", 15000),
    ("EMP019", "韩冰冰", "市场部", 8000),
    ("EMP020", "冯婉君", "行政部", 14000),
    ("EMP021", "许志刚", "行政部", 8000),
    ("EMP022", "邓小丽", "行政部", 6000),
    ("EMP023", "曹丽华", "人事部", 15000),
    ("EMP024", "彭海燕", "人事部", 9000),
    ("EMP025", "曾敏仪", "人事部", 8500),
]


def _file_bucket(file_type: str) -> str:
    lowered = file_type.lower()
    if "工资" in file_type or "salary" in lowered:
        return "salary"
    if "社保" in file_type or "social" in lowered:
        return "social_security"
    if "个税" in file_type or "tax" in lowered:
        return "tax"
    if "银行" in file_type or "bank" in lowered:
        return "bank"
    return "other"


def _to_number(value) -> float | None:
    if value is None:
        return None
    if isinstance(value, (int, float)):
        return float(value)
    s = str(value).strip().replace(",", "").replace("¥", "").replace("元", "")
    if not s:
        return None
    try:
        return float(s)
    except ValueError:
        return None


def _normalize_field(key: str) -> str:
    key = key.strip().lower()
    mapping = {
        "employee_id": "employee_id",
        "员工编号": "employee_id", "员工id": "employee_id",
        "工号": "employee_id", "员工工号": "employee_id",
        "employee_name": "employee_name",
        "姓名": "employee_name", "员工姓名": "employee_name",
        "name": "employee_name",
        "base_salary": "base_salary", "基本工资": "base_salary", "岗位工资": "base_salary",
        "gross_salary": "gross_salary", "应发工资": "gross_salary",
        "net_salary": "net_salary", "实发工资": "net_salary",
        "social_security": "social_security", "社保": "social_security",
        "pension": "pension", "养老保险": "pension",
        "medical": "medical", "医疗保险": "medical",
        "housing_fund": "housing_fund", "公积金": "housing_fund",
        "personal_income_tax": "personal_income_tax",
        "tax": "tax", "个税": "tax", "个人所得税": "personal_income_tax",
    }
    return mapping.get(key, key)


def _generate_salary_rows() -> list[dict]:
    rows = []
    for emp_id, name, dept, base in EMPLOYEES:
        performance = round(base * random.uniform(0.1, 0.3), 2)
        bonus = round(base * random.uniform(0.5, 1.5), 2) if random.random() > 0.3 else 0
        overtime = random.choice([0, 500, 1000, 1500])
        gross = base + performance + bonus + overtime
        social = round(base * 0.105, 2)
        housing = round(base * 0.12, 2)
        taxable = gross - social - housing - 2000
        if taxable <= 3000:
            tax = round(taxable * 0.03, 2)
        elif taxable <= 12000:
            tax = round(taxable * 0.10 - 210, 2)
        elif taxable <= 25000:
            tax = round(taxable * 0.20 - 1410, 2)
        else:
            tax = round(taxable * 0.25 - 2660, 2)
        net = round(gross - social - housing - tax, 2)
        rows.append({
            "员工编号": emp_id,
            "姓名": name,
            "部门": dept,
            "基本工资": base,
            "岗位工资": base,
            "绩效工资": performance,
            "奖金": bonus,
            "加班费": overtime,
            "应发工资": round(gross, 2),
            "社保": social,
            "公积金": housing,
            "个税": tax,
            "实发工资": net,
        })
    return rows


def _generate_social_rows() -> list[dict]:
    rows = []
    for emp_id, name, dept, base in EMPLOYEES:
        pension = round(base * 0.08, 2)
        medical = round(base * 0.02, 2)
        unemployment = round(base * 0.005, 2)
        total = round(pension + medical + unemployment + base * 0.12, 2)
        rows.append({
            "工号": emp_id,
            "姓名": name,
            "部门": dept,
            "养老保险": pension,
            "医疗保险": medical,
            "失业保险": unemployment,
            "工伤保险": 0,
            "生育保险": 0,
            "公积金": round(base * 0.12, 2),
            "社保合计": total,
        })
    return rows


def _generate_tax_rows() -> list[dict]:
    rows = []
    for emp_id, name, dept, base in EMPLOYEES:
        gross = base * random.uniform(1.1, 1.3)
        social = base * 0.105
        housing = base * 0.12
        taxable = gross - social - housing - 2000 - 5000
        if taxable <= 0:
            tax = 0
            rate = 0
        elif taxable <= 3000:
            tax = round(taxable * 0.03, 2)
            rate = 0.03
        elif taxable <= 12000:
            tax = round(taxable * 0.10 - 210, 2)
            rate = 0.10
        else:
            tax = round(taxable * 0.20 - 1410, 2)
            rate = 0.20
        rows.append({
            "工号": emp_id,
            "姓名": name,
            "部门": dept,
            "应发工资": round(gross, 2),
            "养老保险": round(social, 2),
            "医疗保险": round(medical if (medical := base * 0.02) else 0, 2),
            "失业保险": round(unemployment if (unemployment := base * 0.005) else 0, 2),
            "公积金": round(housing, 2),
            "专项附加扣除": 2000,
            "应税所得": round(max(taxable, 0), 2),
            "税率": rate,
            "个税": tax,
        })
    return rows


def _parse_csv(content: str, bucket: str) -> list[dict]:
    """解析 CSV 内容并标准化字段"""
    reader = csv.DictReader(io.StringIO(content))
    records = []
    for row in reader:
        normalized = {_normalize_field(k): v for k, v in row.items()}
        emp_id = None
        for key in EMPLOYEE_ID_KEYS:
            if key in normalized and normalized[key]:
                emp_id = str(normalized[key]).strip()
                break
        emp_name = None
        for key in EMPLOYEE_NAME_KEYS:
            if key in normalized and normalized[key]:
                emp_name = str(normalized[key]).strip()
                break
        records.append({
            "raw_data": dict(row),
            "normalized_data": normalized,
            "employee_id": emp_id,
            "employee_name": emp_name,
        })
    return records


async def backfill():
    upload_dir = file_storage_service.get_upload_dir()
    upload_dir.mkdir(parents=True, exist_ok=True)

    async with AsyncSessionLocal() as db:
        result = await db.execute(
            select(UploadedFile).where(
                UploadedFile.parse_status == "解析成功",
                UploadedFile.stored_filename.isnot(None),
            )
        )
        files = list(result.scalars().all())
        print(f"找到 {len(files)} 个已解析的上传文件")

        if not files:
            print("没有需要回填的文件")
            return

        created = 0
        for file in files:
            # 确保目录存在
            company_dir = upload_dir / str(file.company_id)
            company_dir.mkdir(parents=True, exist_ok=True)

            bucket = _file_bucket(file.file_type)

            # 生成对应 bucket 的行数据
            if bucket == "salary":
                rows = _generate_salary_rows()
            elif bucket == "social_security":
                rows = _generate_social_rows()
            elif bucket == "tax":
                rows = _generate_tax_rows()
            else:
                rows = []

            if not rows:
                print(f"  跳过文件 {file.id} (bucket={bucket}, 无对应生成器)")
                continue

            # 生成 CSV 内容
            buffer = io.StringIO()
            writer = csv.DictWriter(buffer, fieldnames=rows[0].keys())
            writer.writeheader()
            writer.writerows(rows)
            csv_content = buffer.getvalue()

            # 保存文件（覆盖 stub）
            file_ext = ".csv"
            stored_path = company_dir / f"{file.id}{file_ext}"
            with open(stored_path, "w", encoding="utf-8-sig") as f:
                f.write(csv_content)

            # 更新 stored_filename（移除 company_id 前缀，因为 FileStorageService 会拼接）
            actual_stored = f"{file.company_id}/{file.id}{file_ext}"
            file.stored_filename = actual_stored
            await db.flush()

            # 解析并写入 ParsedFileRecord
            parsed = _parse_csv(csv_content, bucket)
            for idx, rec in enumerate(parsed):
                pfr = ParsedFileRecord(
                    company_id=file.company_id,
                    task_id=file.task_id,
                    uploaded_file_id=file.id,
                    file_bucket=bucket,
                    row_index=idx,
                    raw_data=rec["raw_data"],
                    normalized_data=rec["normalized_data"],
                    employee_id=rec["employee_id"],
                    employee_name=rec["employee_name"],
                )
                db.add(pfr)
                created += 1

            print(f"  文件 {file.id}: bucket={bucket}, {len(parsed)} 行")

        await db.commit()
        print(f"\n完成：创建 {created} 条 ParsedFileRecord")


if __name__ == "__main__":
    import asyncio
    asyncio.run(backfill())