from datetime import datetime, timezone

from sqlalchemy import DateTime, Integer, String, 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"}


MEMBER_STATUSES = ("active", "disabled", "cancelled")


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


class SiteMember(Base):
    __tablename__ = "site_member"
    __table_args__ = _mysql_args()

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    email: Mapped[str] = mapped_column(String(191), unique=True, nullable=False)
    password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
    status: Mapped[str] = mapped_column(String(16), nullable=False, default="active")
    privacy_accepted_at: Mapped[datetime | None] = mapped_column(DateTime, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime, nullable=False, default=_now)


def get_member_by_email(db, email: str) -> SiteMember | None:
    normalized = (email or "").strip().lower()
    if not normalized:
        return None
    return db.scalar(select(SiteMember).where(SiteMember.email == normalized))


def get_member_by_id(db, member_id: int) -> SiteMember | None:
    return db.get(SiteMember, member_id)


def create_member(db, *, email: str, password_hash: str, privacy_accepted_at: datetime) -> SiteMember:
    row = SiteMember(
        email=email.strip().lower(),
        password_hash=password_hash,
        status="active",
        privacy_accepted_at=privacy_accepted_at,
        created_at=_now(),
        updated_at=_now(),
    )
    db.add(row)
    db.commit()
    db.refresh(row)
    return row


def set_member_password(db, member: SiteMember, password_hash: str) -> None:
    member.password_hash = password_hash
    member.updated_at = _now()
    db.commit()