# ============================================================
# world ↔ SQLite 同步层（moduleId: db-sync, 可重生 ✅）
# 职责：
#   1. world_to_db：把 world 主数据键写入当前项目（替换式，主数据事实源=DB）
#   2. db_to_world：把当前项目主数据投影回 world dict（引擎零改动）
#   3. 项目管理：get/set_active_project（康尼、演示厂……多项目隔离）
# 排产运行态（版本/工单/冲突/日志/检查点）不入库，仍留 world.json。
# ============================================================
from __future__ import annotations

import json
from datetime import datetime
from typing import Any

from sqlalchemy import delete, select

from server.db.database import get_session
from server.auth.context import get_identity

# world 主数据键（列表型）——固定轨 + 柔性轨
MASTER_LIST_KEYS: tuple[str, ...] = (
    # 固定轨主数据
    "factories", "workshops", "lines", "workstations", "equipment",
    "operations", "routings", "routingSteps", "materials", "boms", "bomItems",
    "lineProducts", "workstationOperations", "teams", "shifts", "shiftCalendar",
    "maintenance", "changeoverMatrix",
    "calendarTemplates", "calendarHolidays", "masterdataVersions",
    # 订单（销售/预测属于可维护数据；生产/工单是排产产物不入库）
    "salesOrders", "forecastOrders",
    # 柔性轨主数据
    "flexZones", "flexOperations", "flexEquipment", "flexMolds",
    "flexMaterials", "flexBom", "flexRoutings", "flexTeams", "flexCalendar",
    "flexOrders",
    "flexCalendarOverrides", "flexFactoryResources", "flexPersonnel", "flexWip",
    "flexMaintenance", "flexSandboxOrders", "flexPartners", "flexScenarios", "intakeSources",
)

# world 主数据键（字典型）→ project_settings
MASTER_DICT_KEYS: tuple[str, ...] = ("scheduleParams", "constraintProfile", "flexParams", "planningContext")

DEFAULT_PROJECT = "demo"


# ---------------- 项目管理 ----------------
def _ensure_project(session, code: str, name: str = "", industry: str = "machining"):
    from server.db.models import Project
    identity = get_identity()
    proj = session.execute(select(Project).where(
        Project.tenant_uuid == identity.tenant_uuid,
        Project.code == code,
        Project.deleted == 0,
    )).scalar_one_or_none()
    if proj is None:
        proj = Project(
            tenant_uuid=identity.tenant_uuid,
            owner_user_id=identity.user_id or None,
            creator_id=identity.user_id or None,
            code=code,
            name=name or code,
            industry=industry,
            active=False,
            created_at=datetime.now().strftime("%Y-%m-%d %H:%M"),
        )
        session.add(proj)
        session.flush()
    return proj


def _workspace_state(session):
    from server.db.models import UserWorkspace
    identity = get_identity()
    state = session.execute(select(UserWorkspace).where(
        UserWorkspace.tenant_uuid == identity.tenant_uuid,
        UserWorkspace.user_id == identity.user_id,
        UserWorkspace.deleted == 0,
    )).scalar_one_or_none()
    if state is None:
        state = UserWorkspace(
            tenant_uuid=identity.tenant_uuid,
            user_id=identity.user_id,
            creator_id=identity.user_id or None,
            active_project_id=DEFAULT_PROJECT,
            created_at=datetime.now().strftime("%Y-%m-%d %H:%M"),
        )
        session.add(state)
        session.flush()
    return state


def get_active_project() -> dict[str, Any]:
    """返回激活项目（无则创建并激活 demo）。"""
    from server.db.models import Project
    identity = get_identity()
    with get_session() as session:
        state = _workspace_state(session)
        code = state.active_project_id
        if code in ("", "__personal__"):
            code = DEFAULT_PROJECT
        proj = session.execute(select(Project).where(
            Project.tenant_uuid == identity.tenant_uuid,
            Project.code == code,
            Project.deleted == 0,
        )).scalar_one_or_none()
        if proj is None:
            proj = _ensure_project(session, DEFAULT_PROJECT, "演示项目")
            proj.active = True
            state.active_project_id = proj.code
        session.commit()
        return {"id": proj.id, "code": proj.code, "name": proj.name, "industry": proj.industry}


def set_active_project(code: str, name: str = "", industry: str = "machining") -> dict[str, Any]:
    """切换/创建激活项目（导入现场数据时把康尼建成独立项目）。"""
    from server.db.models import Project
    identity = get_identity()
    with get_session() as session:
        proj = _ensure_project(session, code, name, industry)
        for p in session.execute(select(Project).where(
            Project.tenant_uuid == identity.tenant_uuid,
            Project.deleted == 0,
        )).scalars():
            p.active = (p.id == proj.id)
        _workspace_state(session).active_project_id = code
        session.commit()
        return {"id": proj.id, "code": proj.code, "name": proj.name, "industry": proj.industry}


def list_projects() -> list[dict[str, Any]]:
    from server.db.models import Project
    identity = get_identity()
    with get_session() as session:
        active = _workspace_state(session).active_project_id
        rows = session.execute(select(Project).where(
            Project.tenant_uuid == identity.tenant_uuid,
            Project.deleted == 0,
        ).order_by(Project.id)).scalars().all()
        return [{"id": p.id, "code": p.code, "name": p.name,
                 "industry": p.industry, "active": p.code == active} for p in rows]


# ---------------- world → DB ----------------
def world_to_db(world: dict[str, Any], project_code: str | None = None) -> dict[str, int]:
    """把 world 主数据替换式写入项目（返回各表行数）。"""
    from server.db.models import MasterRecord, ProjectSetting
    identity = get_identity()
    proj = get_active_project() if project_code is None else set_active_project(project_code)
    counts: dict[str, int] = {}
    with get_session() as session:
        session.execute(delete(MasterRecord).where(
            MasterRecord.tenant_uuid == identity.tenant_uuid,
            MasterRecord.project_id == proj["id"],
        ))
        session.execute(delete(ProjectSetting).where(
            ProjectSetting.tenant_uuid == identity.tenant_uuid,
            ProjectSetting.project_id == proj["id"],
        ))
        for key in MASTER_LIST_KEYS:
            rows = world.get(key) or []
            if not isinstance(rows, list):
                continue
            for i, rec in enumerate(rows):
                if not isinstance(rec, dict):
                    continue
                session.add(MasterRecord(
                    tenant_uuid=identity.tenant_uuid,
                    creator_id=identity.user_id or None,
                    project_id=proj["id"], table_key=key, seq=i,
                    rec_id=str(rec.get("id") or rec.get("code") or i),
                    code=str(rec.get("code") or rec.get("orderNo") or ""),
                    name=str(rec.get("name") or rec.get("productName") or ""),
                    payload=rec,
                ))
            counts[key] = len(rows)
        for key in MASTER_DICT_KEYS:
            val = world.get(key)
            if isinstance(val, dict) and val:
                session.add(ProjectSetting(
                    tenant_uuid=identity.tenant_uuid,
                    creator_id=identity.user_id or None,
                    project_id=proj["id"], key=key, payload=val,
                ))
                counts[key] = 1
        session.commit()
    return counts


# ---------------- DB → world 投影 ----------------
def db_has_master(project_code: str | None = None) -> bool:
    """当前（或指定）项目是否已有主数据。"""
    from server.db.models import MasterRecord, Project
    identity = get_identity()
    with get_session() as session:
        if project_code:
            proj = session.execute(select(Project).where(
                Project.tenant_uuid == identity.tenant_uuid,
                Project.code == project_code,
                Project.deleted == 0,
            )).scalar_one_or_none()
            if proj is None:
                return False
            pid = proj.id
        else:
            pid = get_active_project()["id"]
        row = session.execute(
            select(MasterRecord.id).where(
                MasterRecord.tenant_uuid == identity.tenant_uuid,
                MasterRecord.project_id == pid,
                MasterRecord.deleted == 0,
            ).limit(1)
        ).first()
        return row is not None


def db_to_world(world: dict[str, Any], project_code: str | None = None) -> dict[str, int]:
    """把项目主数据投影进 world dict（覆盖主数据键，保留运行态键）。"""
    from server.db.models import MasterRecord, ProjectSetting, Project
    identity = get_identity()
    with get_session() as session:
        if project_code:
            proj_row = session.execute(select(Project).where(
                Project.tenant_uuid == identity.tenant_uuid,
                Project.code == project_code,
                Project.deleted == 0,
            )).scalar_one_or_none()
            if proj_row is None:
                return {}
            pid = proj_row.id
        else:
            pid = get_active_project()["id"]
        counts: dict[str, int] = {}
        rows = session.execute(
            select(MasterRecord).where(
                MasterRecord.tenant_uuid == identity.tenant_uuid,
                MasterRecord.project_id == pid,
                MasterRecord.deleted == 0,
            ).order_by(MasterRecord.table_key, MasterRecord.seq)
        ).scalars().all()
        by_key: dict[str, list] = {}
        for r in rows:
            by_key.setdefault(r.table_key, []).append(r.payload)
        for key in MASTER_LIST_KEYS:
            # Empty is authoritative too: switching projects must not retain the
            # previous project's calendar, people, WIP, or editable master rows.
            world[key] = by_key.get(key, [])
            if key in by_key:
                counts[key] = len(world[key])
        for key in MASTER_DICT_KEYS:
            world[key] = {}
        for s in session.execute(select(ProjectSetting).where(
            ProjectSetting.tenant_uuid == identity.tenant_uuid,
            ProjectSetting.project_id == pid,
            ProjectSetting.deleted == 0,
        )).scalars():
            if s.key in MASTER_DICT_KEYS and isinstance(s.payload, dict):
                world[s.key] = s.payload
                counts[s.key] = 1
        return counts


def master_fingerprint(world: dict[str, Any]) -> str:
    """主数据指纹（store.save 判断是否需要回写 DB）。"""
    import hashlib
    h = hashlib.sha256()
    for key in MASTER_LIST_KEYS + MASTER_DICT_KEYS:
        val = world.get(key)
        if val:
            h.update(key.encode())
            h.update(json.dumps(val, ensure_ascii=False, sort_keys=True, default=str).encode())
    return h.hexdigest()