main.py

from datetime import datetime
from sqlite3 import IntegrityError
from typing import Optional
from uuid import uuid4

from fastapi import FastAPI, HTTPException, Header
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel, EmailStr
from sqlmodel import SQLModel, Field, create_engine, Session, select
from passlib.hash import bcrypt
from sqlalchemy.exc import OperationalError
from sqlmodel import SQLModel, Field, create_engine, Session, select

# --- DB 설정: 개발은 SQLite, 나중에 MySQL로 전환 가능 ---
DATABASE_URL = "sqlite:///./smartfactory.db"
# MySQL 전환 시:
#   pip install pymysql
#   DATABASE_URL = "mysql+pymysql://root:<PASSWORD>@localhost:3306/smartfactory?charset=utf8mb4"

engine = create_engine(
    DATABASE_URL,
    echo=False,
    connect_args={"check_same_thread": False, "timeout": 30},  # 🔹 잠금 대기
)

# --- 테이블 ---
class Department(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)

class Position(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)

class User(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    email: str = Field(index=True, unique=True)
    password_hash: str
    name: Optional[str] = None
    department_id: Optional[int] = Field(default=None, foreign_key="department.id")
    position_id: Optional[int] = Field(default=None, foreign_key="position.id")
    # ✅ 신규: 전화번호
    phone: Optional[str] = None
    role: str = Field(default="USER")
    created_at: datetime = Field(default_factory=datetime.now)
    updated_at: datetime = Field(default_factory=datetime.now)

class LoginToken(SQLModel, table=True):
    token: str = Field(primary_key=True)
    user_id: int = Field(foreign_key="user.id")
    created_at: datetime = Field(default_factory=datetime.now)
    
class IMURecord(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    code: str = Field(index=True, unique=True) # 외부 노출용 코드
    
    serial: Optional[str] = None                  # IMU 시리얼 넘버
    inspected_at: datetime = Field(default_factory=datetime.now, index=True)
    passed: bool = Field(default=False)               # 합격 여부

    inspector_id: int = Field(foreign_key="user.id")  # 검수자 FK -> User.id

    box_no: Optional[int] = None                      # 박스번호(도착 완료 시)
    destination: Optional[str] = None                 # 목적지(도착 완료 시)
    arrived: bool = Field(default=False)              # 도착 여부(Y)

    roll: Optional[float] = None
    pitch: Optional[float] = None
    yaw: Optional[float] = None

    created_at: datetime = Field(default_factory=datetime.now)
    updated_at: datetime = Field(default_factory=datetime.now)
# --- DTO ---
class RegisterReq(BaseModel):
    email: EmailStr
    password: str
    name: Optional[str] = None
    department_name: Optional[str] = None
    position_name: Optional[str] = None
    phone: Optional[str] = None           # ✅ 추가
    role: Optional[str] = "USER"

class LoginReq(BaseModel):
    email: EmailStr
    password: str

class Summary(BaseModel):
    email: str
    name: Optional[str] = None
    department: Optional[str] = "-"
    position: Optional[str] = "-"
    phone: Optional[str] = None           # ✅ 추가
    role: str = "USER"

class LoginResp(BaseModel):
    token: str
    summary: Summary
class ProfileUpdateReq(BaseModel):
    name: Optional[str] = None
    department_name: Optional[str] = None
    position_name: Optional[str] = None
    phone: Optional[str] = None
    
class IMUCreate(BaseModel):
    serial: Optional[str] = None
    inspected_at: Optional[datetime] = None
    passed: bool

    box_no: Optional[int] = None
    destination: Optional[str] = None
    arrived: Optional[bool] = False

    roll: Optional[float] = None
    pitch: Optional[float] = None
    yaw: Optional[float] = None

class IMUOut(BaseModel):
    code: str
    serial: Optional[str] 
    inspected_at: datetime
    passed: bool

    inspector_id: int
    inspector_name: Optional[str]

    box_no: Optional[int] 
    destination: Optional[str] 
    arrived: bool

    roll: Optional[float] = None
    pitch: Optional[float] = None
    yaw: Optional[float] = None
    
# --- 앱 ---
app = FastAPI(title="SmartFactory Mini API")
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"], allow_credentials=True,
    allow_methods=["*"], allow_headers=["*"],
)

@app.on_event("startup")
def on_startup():
    SQLModel.metadata.create_all(engine)

    # 🔹 잠금/동시 읽기 개선
    with engine.begin() as conn:
        conn.exec_driver_sql("PRAGMA journal_mode=WAL")
        conn.exec_driver_sql("PRAGMA busy_timeout=5000")

        # 🔹 마이그레이션 플래그 테이블
        conn.exec_driver_sql("""
        CREATE TABLE IF NOT EXISTS schema_meta (
            key TEXT PRIMARY KEY,
            value TEXT
        )
        """)

        # 🔹 이미 끝났으면 재실행 금지
        done = conn.exec_driver_sql(
            "SELECT value FROM schema_meta WHERE key='imu_code_migrated'"
        ).fetchone()
        if done:
            return

        # 🔹 컬럼 없으면 추가
        cols = [row[1] for row in conn.exec_driver_sql("PRAGMA table_info('imurecord')")]
        if 'code' not in cols:
            conn.exec_driver_sql("ALTER TABLE imurecord ADD COLUMN code TEXT")
            conn.exec_driver_sql(
                "CREATE UNIQUE INDEX IF NOT EXISTS idx_imurecord_code ON imurecord(code)"
            )

    # 🔹 백필(잠금 줄이려고 DDL과 분리)
    from sqlmodel import Session, select
    with Session(engine) as s:
        rows = s.exec(select(IMURecord).order_by(IMURecord.inspected_at.asc())).all()
        changed = False
        for i, r in enumerate(rows, start=1):
            if not r.code or not str(r.code).strip():
                r.code = f"{i:03d}"
                changed = True
        if changed:
            s.commit()

    # 🔹 완료 마킹(재실행 방지)
    with engine.begin() as conn:
        conn.exec_driver_sql(
            "INSERT OR REPLACE INTO schema_meta(key, value) VALUES('imu_code_migrated','1')"
        )

# --- 헬퍼 ---
def _get_or_create_by_name(session: Session, model, name: str):
    row = session.exec(select(model).where(model.name == name)).first()
    if not row:
        row = model(name=name)
        session.add(row)
        session.commit()
        session.refresh(row)
    return row

def _to_summary(s: Session, user: User) -> Summary:
    dept_name = s.exec(
        select(Department.name).where(Department.id == user.department_id)
    ).first()
    pos_name = s.exec(
        select(Position.name).where(Position.id == user.position_id)
    ).first()

    # 혹시라도 Row/tuple이면 첫 원소만
    if isinstance(dept_name, (tuple, list)): dept_name = dept_name[0]
    if isinstance(dept_name, object) and getattr(dept_name, "_mapping", None):
        dept_name = dept_name._mapping.get("name")
    if isinstance(pos_name, (tuple, list)): pos_name = pos_name[0]
    if isinstance(pos_name, object) and getattr(pos_name, "_mapping", None):
        pos_name = pos_name._mapping.get("name")

    return Summary(
        email=user.email,
        name=user.name,
        department=dept_name or "-",
        position=pos_name or "-",
        role=user.role,
        phone=user.phone,
    )


def _user_by_token(session: Session, tok: str) -> Optional[User]:
    t = session.get(LoginToken, tok)
    if not t:
        return None
    return session.get(User, t.user_id)

def _to_imu_out(session: Session, r: IMURecord) -> IMUOut:
    u = session.get(User, r.inspector_id)
    return IMUOut(
        code=r.code,
        serial=r.serial,
        inspected_at=r.inspected_at,
        passed=r.passed,
        inspector_id=r.inspector_id,
        inspector_name=(u.name if u else None),
        box_no=r.box_no,
        destination=r.destination,
        arrived=r.arrived,
        roll=r.roll,
        pitch=r.pitch,
        yaw=r.yaw,
    )

from sqlalchemy import text

def _next_imu_code(session: Session) -> str:
    # 숫자로만 된 code 중 최댓값을 정수로 얻음 (NULL/비숫자 무시)
    row = session.exec(
        text("SELECT COALESCE(MAX(CAST(code AS INTEGER)), 0) AS m "
             "FROM imurecord WHERE code GLOB '[0-9]*'")
    ).first()
    if isinstance(row, (tuple, list)):
        max_n = row[0]
    elif hasattr(row, "_mapping"):
        max_n = row._mapping.get("m") or list(row._mapping.values())[0]
    else:
        max_n = row

    n = int(max_n or 0) + 1
    width = 3 if n < 1000 else len(str(n))
    return str(n).zfill(width)


# --- 엔드포인트 ---
@app.get("/health")
def health():
    return {"ok": True}

@app.post("/auth/register", status_code=201)
def register(body: RegisterReq):
    with Session(engine) as s:
        if s.exec(select(User).where(User.email == body.email)).first():
            raise HTTPException(409, "Email already exists")
        dept = _get_or_create_by_name(s, Department, body.department_name) if body.department_name else None
        pos  = _get_or_create_by_name(s, Position,   body.position_name)   if body.position_name else None
        u = User(
            email=body.email,
            password_hash=bcrypt.hash(body.password),
            name=body.name,
            department_id=dept.id if dept else None,
            position_id=pos.id if pos else None,
            phone=body.phone,           # ✅ 저장
            role=body.role or "USER",
        )
        s.add(u); s.commit(); s.refresh(u)
        return {"id": u.id}


@app.post("/auth/login", response_model=LoginResp)
def login(body: LoginReq):
    with Session(engine) as s:
        # 1) PK만 먼저 뽑기 (Row가 와도 스칼라 하나만 다루면 안전)
        uid = s.exec(
            select(User.id).where(User.email == body.email)
        ).first()

        # uid가 ('3',) 같은 튜플/Row일 수 있으니 안전하게 스칼라화
        if isinstance(uid, (tuple, list)):
            uid = uid[0]
        elif hasattr(uid, "_mapping"):  # Row
            uid = uid._mapping.get("id") or list(uid._mapping.values())[0]

        if uid is None:
            raise HTTPException(401, "Bad credentials")

        # 2) PK로 ORM 인스턴스를 보장
        u = s.get(User, uid)
        if not u or not bcrypt.verify(body.password, u.password_hash):
            raise HTTPException(401, "Bad credentials")

        tok = LoginToken(token=str(uuid4()), user_id=u.id)
        s.add(tok)
        s.commit()
        return LoginResp(token=tok.token, summary=_to_summary(s, u))
    
@app.post("/auth/logout", status_code=204)
def logout(x_auth_token: str = Header(None, alias="X-Auth-Token")):
    if not x_auth_token:
        raise HTTPException(400, "X-Auth-Token header required")
    with Session(engine) as s:
        t = s.get(LoginToken, x_auth_token)
        if t:
            s.delete(t); s.commit()
    return

@app.get("/me/summary", response_model=Summary)
def me_summary(x_auth_token: str = Header(None, alias="X-Auth-Token")):
    if not x_auth_token:
        raise HTTPException(400, "X-Auth-Token header required")
    with Session(engine) as s:
        t = s.get(LoginToken, x_auth_token)
        if not t:
            raise HTTPException(401, "Unauthorized")
        u = s.get(User, t.user_id)
        if not u:
            raise HTTPException(404, "User not found")
        return _to_summary(s, u)


@app.patch("/me/profile", response_model=Summary)
def patch_profile(body:ProfileUpdateReq, x_auth_token: str = Header(None, alias="X-Auth-Token")):
    if not x_auth_token:
        raise HTTPException(400, "X-Auth-Token header required")
    with Session(engine) as s:
        t = s.get(LoginToken, x_auth_token)
        if not t:
            raise HTTPException(401, "Unauthorized")
        u = s.get(User, t.user_id)
        if not u:
            raise HTTPException(404, "User not found")
        
        if body.name is not None:
            u.name = body.name
        if body.department_name is not None:
            dept = _get_or_create_by_name(s, Department, body.department_name)
            u.department_id = dept.id
        if body.position_name is not None:
            pos = _get_or_create_by_name(s, Position, body.position_name)
            u.position_id = pos.id
        if body.phone is not None:
            u.phone = body.phone
            
    u.updated_at = datetime.now()
    s.add(u); s.commit(); s.refresh(u)
    return _to_summary(s, u)

# === IMU ===

@app.get("/imu/list")
def imu_list(
    x_auth_token: Optional[str] = Header(None, alias="X-Auth-Token"),
    limit: int = 200,
    offset: int = 0,
    order: str = "desc",   # 'asc' | 'desc' (inspected_at)
):
    # (조직 정책에 따라 공개여부 결정. 여기선 로그인 필요로 가정)
    if not x_auth_token:
        raise HTTPException(401, "Unauthorized")

    with Session(engine) as s:
        # 권한 체크(필요하면 역할별 제한 추가 가능)
        me = _user_by_token(s, x_auth_token)
        if not me:
            raise HTTPException(401, "Unauthorized")

        q = select(IMURecord)
        if order.lower() == "asc":
            q = q.order_by(IMURecord.inspected_at.asc())
        else:
            q = q.order_by(IMURecord.inspected_at.desc())

        rows = s.exec(q.offset(offset).limit(limit)).all()

        items = [_to_imu_out(s, r).dict() for r in rows]
        # 통계(합격/불합격/총합) - 리스트 화면의 도넛 게이지용
        total = len(items)
        passed_cnt = sum(1 for it in items if it["passed"])
        fail_cnt = total - passed_cnt

        return {
            "items": items,
            "total": total,
            "passed": passed_cnt,
            "failed": fail_cnt,
        }
@app.post("/imu", response_model=IMUOut, status_code=201)
def imu_create(body: IMUCreate, x_auth_token: str = Header(None, alias="X-Auth-Token")):
    if not x_auth_token:
        raise HTTPException(401, "Unauthorized")

    with Session(engine) as s:
        # 검사자 확인(필수)
        me = _user_by_token(s, x_auth_token)
        if not me:
            raise HTTPException(401, "Unauthorized")

        # code 충돌 대비 재시도
        for _ in range(5):
            try:
                rec = IMURecord(
                    code=_next_imu_code(s),
                    serial=body.serial,
                    inspected_at=body.inspected_at or datetime.utcnow(),
                    passed=body.passed,
                    inspector_id=me.id,
                    box_no=body.box_no,
                    destination=body.destination,
                    arrived=bool(body.arrived),
                    roll=body.roll, pitch=body.pitch, yaw=body.yaw,
                )
                s.add(rec)
                s.commit()
                s.refresh(rec)
                break
            except IntegrityError:
                s.rollback()
        else:
            raise HTTPException(409, "IMU 코드 생성 충돌이 발생했습니다. 다시 시도해주세요.")

        inspector_name = s.exec(select(User.name).where(User.id == rec.inspector_id)).first()
        if isinstance(inspector_name, (tuple, list)):
            inspector_name = inspector_name[0]
        elif hasattr(inspector_name, "_mapping"):
            inspector_name = inspector_name._mapping.get("name")

        return IMUOut(
            code=rec.code,
            serial=rec.serial,
            inspected_at=rec.inspected_at,
            passed=rec.passed,
            inspector_id=rec.inspector_id,
            inspector_name=inspector_name,
            box_no=rec.box_no,
            destination=rec.destination,
            arrived=rec.arrived,
            roll=rec.roll, pitch=rec.pitch, yaw=rec.yaw,
        )