from datetime import date, datetime, timedelta, timezone

from sqlalchemy import DateTime, Integer, String, Text, func, select
from sqlalchemy.orm import Mapped, mapped_column

from app.db import Base


def _mysql_args() -> dict:
    return {"mysql_charset": "utf8mb4", "mysql_collate": "utf8mb4_unicode_ci"}


QUESTION_STATUSES = ("pending", "replied")
DEFAULT_REPLY_WORKDAYS = 3


def _now() -> datetime:
    return datetime.now(timezone.utc).replace(tzinfo=None)


class ExpertQuestion(Base):
    __tablename__ = "expert_question"
    __table_args__ = _mysql_args()

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    member_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False, default="")
    body: Mapped[str] = mapped_column(Text, nullable=False, default="")
    status: Mapped[str] = mapped_column(String(16), nullable=False, default="pending", index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=_now, index=True)
    replied_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)


class ExpertQuestionAttachment(Base):
    __tablename__ = "expert_question_attachment"
    __table_args__ = _mysql_args()

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    question_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    file_id: Mapped[int] = mapped_column(Integer, nullable=False)
    position: Mapped[int] = mapped_column(Integer, nullable=False, default=0)


class ExpertReply(Base):
    __tablename__ = "expert_reply"
    __table_args__ = _mysql_args()

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    question_id: Mapped[int] = mapped_column(Integer, unique=True, nullable=False)
    body_html: Mapped[str] = mapped_column(Text, nullable=False, default="")
    staff_id: Mapped[int] = mapped_column(Integer, nullable=False)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=_now)


class ExpertReplyAttachment(Base):
    __tablename__ = "expert_reply_attachment"
    __table_args__ = _mysql_args()

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    reply_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    file_id: Mapped[int] = mapped_column(Integer, nullable=False)
    position: Mapped[int] = mapped_column(Integer, nullable=False, default=0)


def reply_workdays(db) -> int:
    from app.models.content import SiteSetting

    row = db.scalar(select(SiteSetting).where(SiteSetting.key == "expert_reply_workdays"))
    raw = (row.value if row else "").strip()
    if not raw:
        return DEFAULT_REPLY_WORKDAYS
    try:
        value = int(raw)
    except ValueError:
        return DEFAULT_REPLY_WORKDAYS
    return value if value > 0 else DEFAULT_REPLY_WORKDAYS


def workday_deadline(start: datetime | date, days: int) -> date:
    current = start.date() if isinstance(start, datetime) else start
    remaining = max(int(days), 0)
    while remaining > 0:
        current = current + timedelta(days=1)
        if current.weekday() < 5:
            remaining -= 1
    return current


def is_overdue(created_at: datetime, days: int, *, now: datetime | None = None) -> bool:
    if created_at is None:
        return False
    current = now or _now()
    return current.date() > workday_deadline(created_at, days)


def _private_file(db, file_id: int):
    from app.models.content import AdminFile

    if file_id is None:
        return None
    row = db.get(AdminFile, file_id)
    if row is None or row.bucket != "private":
        return None
    return row


def _attachment_payload(db, file_id: int) -> dict | None:
    from app.services.storage import private_object_url

    row = _private_file(db, file_id)
    if row is None:
        return None
    url, expires_at = private_object_url(row.object_key)
    return {
        "id": row.id,
        "filename": row.filename,
        "contentType": row.content_type or "",
        "url": url,
        "expiresAt": expires_at.isoformat(),
    }


def _question_attachments(db, question_id: int) -> list[dict]:
    links = db.scalars(
        select(ExpertQuestionAttachment)
        .where(ExpertQuestionAttachment.question_id == question_id)
        .order_by(ExpertQuestionAttachment.position.asc(), ExpertQuestionAttachment.id.asc())
    ).all()
    items = []
    for link in links:
        payload = _attachment_payload(db, link.file_id)
        if payload is not None:
            items.append(payload)
    return items


def _reply_attachments(db, reply_id: int) -> list[dict]:
    links = db.scalars(
        select(ExpertReplyAttachment)
        .where(ExpertReplyAttachment.reply_id == reply_id)
        .order_by(ExpertReplyAttachment.position.asc(), ExpertReplyAttachment.id.asc())
    ).all()
    items = []
    for link in links:
        payload = _attachment_payload(db, link.file_id)
        if payload is not None:
            items.append(payload)
    return items


def _question_list_item(db, row: ExpertQuestion, *, days: int, include_member: bool = False) -> dict:
    from app.models.member import get_member_by_id

    item = {
        "id": row.id,
        "title": row.title,
        "status": row.status,
        "statusLabel": "已回复" if row.status == "replied" else "待回复",
        "createdAt": row.created_at.isoformat(timespec="seconds") if row.created_at else "",
        "repliedAt": row.replied_at.isoformat(timespec="seconds") if row.replied_at else "",
        "overdue": bool(row.status == "pending" and is_overdue(row.created_at, days)),
    }
    if include_member:
        member = get_member_by_id(db, row.member_id)
        item["memberId"] = row.member_id
        item["memberEmail"] = member.email if member else ""
    return item


def create_question(db, *, member_id: int, title: str, body: str, attachment_file_ids: list[int]) -> int:
    cleaned_title = (title or "").strip()
    cleaned_body = (body or "").strip()
    if not cleaned_title:
        raise ValueError("标题不能为空")
    if not cleaned_body:
        raise ValueError("正文不能为空")
    if len(cleaned_title) > 200:
        raise ValueError("标题过长")
    files = []
    for file_id in attachment_file_ids or []:
        row = _private_file(db, file_id)
        if row is None:
            raise ValueError("附件必须是已上传的私有文件")
        files.append(row)
    question = ExpertQuestion(
        member_id=member_id,
        title=cleaned_title,
        body=cleaned_body,
        status="pending",
        created_at=_now(),
    )
    db.add(question)
    db.flush()
    for index, file_row in enumerate(files):
        db.add(
            ExpertQuestionAttachment(
                question_id=question.id,
                file_id=file_row.id,
                position=index,
            )
        )
    db.commit()
    db.refresh(question)
    return question.id


def member_questions(db, member_id: int) -> dict:
    days = reply_workdays(db)
    rows = db.scalars(
        select(ExpertQuestion)
        .where(ExpertQuestion.member_id == member_id)
        .order_by(ExpertQuestion.created_at.desc(), ExpertQuestion.id.desc())
    ).all()
    return {"items": [_question_list_item(db, row, days=days) for row in rows]}


def member_question(db, member_id: int, question_id: int) -> dict | None:
    row = db.get(ExpertQuestion, question_id)
    if row is None or row.member_id != member_id:
        return None
    days = reply_workdays(db)
    body = _question_list_item(db, row, days=days)
    body["body"] = row.body
    body["attachments"] = _question_attachments(db, row.id)
    reply = db.scalar(select(ExpertReply).where(ExpertReply.question_id == row.id))
    if reply is not None:
        body["reply"] = {
            "bodyHtml": reply.body_html,
            "createdAt": reply.created_at.isoformat(timespec="seconds") if reply.created_at else "",
            "attachments": _reply_attachments(db, reply.id),
        }
    return body


def pending_count(db) -> int:
    return int(
        db.scalar(
            select(func.count())
            .select_from(ExpertQuestion)
            .where(ExpertQuestion.status == "pending")
        )
        or 0
    )


def admin_questions(db, *, status: str | None = None) -> dict:
    days = reply_workdays(db)
    stmt = select(ExpertQuestion).order_by(ExpertQuestion.created_at.desc(), ExpertQuestion.id.desc())
    if status in QUESTION_STATUSES:
        stmt = stmt.where(ExpertQuestion.status == status)
    rows = db.scalars(stmt).all()
    return {
        "items": [_question_list_item(db, row, days=days, include_member=True) for row in rows],
        "pendingCount": pending_count(db),
        "replyWorkdays": days,
    }


def admin_question(db, question_id: int) -> dict | None:
    row = db.get(ExpertQuestion, question_id)
    if row is None:
        return None
    days = reply_workdays(db)
    body = _question_list_item(db, row, days=days, include_member=True)
    body["body"] = row.body
    body["attachments"] = _question_attachments(db, row.id)
    reply = db.scalar(select(ExpertReply).where(ExpertReply.question_id == row.id))
    if reply is not None:
        body["reply"] = {
            "bodyHtml": reply.body_html,
            "createdAt": reply.created_at.isoformat(timespec="seconds") if reply.created_at else "",
            "attachments": _reply_attachments(db, reply.id),
        }
    return body


def reply_question(
    db,
    *,
    question_id: int,
    staff_id: int,
    body_html: str,
    attachment_file_ids: list[int],
) -> None:
    from app.core.config import get_settings
    from app.models.content import AdminFile
    from app.services.html_filter import filter_html

    question = db.get(ExpertQuestion, question_id)
    if question is None:
        raise ValueError("提问不存在")
    if question.status != "pending":
        raise ValueError("该提问已回复")
    files = []
    for file_id in attachment_file_ids or []:
        row = _private_file(db, file_id)
        if row is None:
            raise ValueError("附件必须是已上传的私有文件")
        files.append(row)
    keys = list(db.scalars(select(AdminFile.object_key).where(AdminFile.bucket == "public")))
    cleaned = filter_html(body_html or "", list(keys), get_settings().minio_base_url)
    if not cleaned.strip():
        raise ValueError("回复正文不能为空")
    reply = ExpertReply(
        question_id=question.id,
        body_html=cleaned,
        staff_id=staff_id,
        created_at=_now(),
    )
    db.add(reply)
    db.flush()
    for index, file_row in enumerate(files):
        db.add(
            ExpertReplyAttachment(
                reply_id=reply.id,
                file_id=file_row.id,
                position=index,
            )
        )
    question.status = "replied"
    question.replied_at = _now()
    db.commit()