"""展览预约 CRUD（biz_exhibition_appointment）"""
from __future__ import annotations

import math
from datetime import datetime
from typing import Any, Dict, List, Optional, Tuple

from sqlalchemy import and_, func, or_
from sqlalchemy.orm import Session

from app.core.utils import generate_uuid
from app.models.models import BizExhibitionAppointment


def _format_visit_date(val: Any) -> str:
    """格式化 visit_date 为 YYYY-MM-DD HH:mm:ss 字符串"""
    if val is None:
        return ""
    if isinstance(val, datetime):
        return val.strftime("%Y-%m-%d %H:%M:%S")
    return str(val)


def _row_to_dict(u: BizExhibitionAppointment) -> Dict[str, Any]:
    return {
        "id": u.id,
        "appointmentNo": u.appointment_no,
        "visitOrg": u.visit_org,
        "contactName": u.contact_name,
        "visitDate": _format_visit_date(u.visit_date),
        "visitorCount": u.visitor_count,
        "receptionUserId": u.reception_user_id,
        "tenantNo": u.tenant_no,
        "orderNum": u.order_num if u.order_num is not None else 0,
        "remark": u.remark or "",
        "createTime": u.create_time.strftime("%Y-%m-%d %H:%M:%S") if u.create_time else "",
        "updateTime": u.update_time.strftime("%Y-%m-%d %H:%M:%S") if u.update_time else "",
    }


def list_exhibition_appointments(
    db: Session,
    *,
    page: int = 1,
    page_size: int = 10,
    keyword: Optional[str] = None,
    tenant_no: Optional[str] = None,
) -> Tuple[List[Dict[str, Any]], int]:
    def _filtered():
        q0 = db.query(BizExhibitionAppointment).filter(BizExhibitionAppointment.del_flag == 0)
        if tenant_no:
            q0 = q0.filter(BizExhibitionAppointment.tenant_no == tenant_no)
        kw = (keyword or "").strip()
        if kw:
            like = f"%{kw}%"
            q0 = q0.filter(
                or_(
                    BizExhibitionAppointment.appointment_no.like(like),
                    BizExhibitionAppointment.visit_org.like(like),
                    BizExhibitionAppointment.contact_name.like(like),
                )
            )
        return q0

    total = _filtered().order_by(None).with_entities(func.count(BizExhibitionAppointment.id)).scalar() or 0
    page = max(1, page)
    page_size = min(max(1, page_size), 100)
    rows = (
        _filtered()
        .order_by(BizExhibitionAppointment.create_time.desc())
        .offset((page - 1) * page_size)
        .limit(page_size)
        .all()
    )
    return [_row_to_dict(x) for x in rows], int(total)


def get_exhibition_appointment(db: Session, appointment_id: str) -> Optional[BizExhibitionAppointment]:
    return (
        db.query(BizExhibitionAppointment)
        .filter(and_(BizExhibitionAppointment.id == appointment_id, BizExhibitionAppointment.del_flag == 0))
        .first()
    )


def appointment_no_exists(db: Session, appointment_no: str, tenant_no: str, exclude_id: Optional[str] = None) -> bool:
    q = db.query(BizExhibitionAppointment).filter(
        and_(
            BizExhibitionAppointment.appointment_no == appointment_no.strip(),
            BizExhibitionAppointment.tenant_no == tenant_no,
            BizExhibitionAppointment.del_flag == 0,
        )
    )
    if exclude_id:
        q = q.filter(BizExhibitionAppointment.id != exclude_id)
    return q.first() is not None


def _generate_appointment_no(db: Session, tenant_no: str) -> str:
    """生成预约编号：YYYYMMDD + 6位序号（如 20260515000001）"""
    today_str = datetime.now().strftime("%Y%m%d")
    prefix = today_str
    last_row = (
        db.query(BizExhibitionAppointment.appointment_no)
        .filter(
            BizExhibitionAppointment.tenant_no == tenant_no,
            BizExhibitionAppointment.appointment_no.like(f"{prefix}%"),
            BizExhibitionAppointment.del_flag == 0,
        )
        .order_by(BizExhibitionAppointment.appointment_no.desc())
        .first()
    )
    if last_row and last_row[0]:
        try:
            seq = int(last_row[0][len(prefix):]) + 1
        except (ValueError, IndexError):
            seq = 1
    else:
        seq = 1
    return f"{prefix}{seq:06d}"


def create_exhibition_appointment(
    db: Session,
    *,
    appointment_no: Optional[str] = None,
    visit_org: str,
    contact_name: str,
    visit_date: Optional[str] = None,
    visitor_count: int,
    reception_user_id: Optional[str] = None,
    tenant_no: str = "zkxw",
    order_num: Optional[int] = None,
    remark: Optional[str] = None,
    user_id: Optional[str] = None,
) -> BizExhibitionAppointment:
    no = (appointment_no or "").strip()
    if not no:
        no = _generate_appointment_no(db, tenant_no)
    else:
        if appointment_no_exists(db, no, tenant_no):
            raise ValueError("预约编号已存在")
    visit_date_val: Optional[datetime] = None
    if visit_date:
        try:
            visit_date_val = datetime.fromisoformat(visit_date)
        except (ValueError, TypeError):
            visit_date_val = None
    obj = BizExhibitionAppointment(
        id=generate_uuid(),
        appointment_no=no,
        visit_org=(visit_org or "").strip(),
        contact_name=(contact_name or "").strip(),
        visit_date=visit_date_val,
        visitor_count=visitor_count if visitor_count is not None else 0,
        reception_user_id=reception_user_id or "",
        tenant_no=tenant_no,
        order_num=order_num if order_num is not None else 0,
        remark=(remark or "").strip() or None,
        create_by=user_id,
        create_dept_id="",
        create_time=datetime.now(),
        update_by=user_id,
        update_time=datetime.now(),
        del_flag=0,
    )
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def update_exhibition_appointment(
    db: Session,
    appointment_id: str,
    *,
    appointment_no: Optional[str] = None,
    visit_org: Optional[str] = None,
    contact_name: Optional[str] = None,
    visit_date: Optional[str] = None,
    visitor_count: Optional[int] = None,
    reception_user_id: Optional[str] = None,
    order_num: Optional[int] = None,
    remark: Optional[str] = None,
    user_id: Optional[str] = None,
) -> BizExhibitionAppointment:
    obj = get_exhibition_appointment(db, appointment_id)
    if not obj:
        raise ValueError("展览预约不存在")
    if appointment_no is not None:
        new_no = appointment_no.strip()
        if not new_no:
            raise ValueError("预约编号不能为空")
        if new_no != obj.appointment_no and appointment_no_exists(db, new_no, obj.tenant_no, exclude_id=appointment_id):
            raise ValueError("预约编号已存在")
        obj.appointment_no = new_no
    if visit_org is not None:
        obj.visit_org = (visit_org or "").strip()
    if contact_name is not None:
        obj.contact_name = (contact_name or "").strip()
    if visit_date is not None:
        if visit_date:
            try:
                obj.visit_date = datetime.fromisoformat(visit_date)
            except (ValueError, TypeError):
                obj.visit_date = None
        else:
            obj.visit_date = None
    if visitor_count is not None:
        obj.visitor_count = visitor_count
    if reception_user_id is not None:
        obj.reception_user_id = reception_user_id or ""
    if order_num is not None:
        obj.order_num = order_num
    if remark is not None:
        obj.remark = (remark or "").strip() or None
    obj.update_by = user_id
    obj.update_time = datetime.now()
    db.add(obj)
    db.commit()
    db.refresh(obj)
    return obj


def soft_delete_exhibition_appointment(db: Session, appointment_id: str) -> None:
    obj = get_exhibition_appointment(db, appointment_id)
    if not obj:
        raise ValueError("展览预约不存在")
    obj.del_flag = 1
    obj.update_time = datetime.now()
    db.add(obj)
    db.commit()


def total_pages(total: int, page_size: int) -> int:
    if total <= 0:
        return 0
    return int(math.ceil(total / float(page_size)))