"""Email group + owner models."""
from __future__ import annotations

import uuid
from datetime import datetime

from sqlalchemy import (
    JSON,
    Boolean,
    Computed,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func

from app.core.database import Base
from app.core.sql_types import GUID as Uuid


class EmailGroup(Base):
    """A Google Workspace mail group tracked in Helix.

    The ``email`` column is the group's primary email (e.g.
    ``eng@pingcap.com``). One group row, N owners via
    :class:`EmailGroupOwner`.

    ``status='active' | 'deleted'`` — we soft-delete so historical
    references (audit rows, offboarding blockers) stay resolvable even
    after the group is removed from Google.
    """

    __tablename__ = "email_groups"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        Index("ix_email_groups_status", "status"),
        Index("ix_email_groups_created_by", "created_by"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    email: Mapped[str] = mapped_column(String(255), nullable=False, unique=True)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    status: Mapped[str] = mapped_column(String(16), nullable=False, default="active")
    google_group_id: Mapped[str | None] = mapped_column(String(128))
    created_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    employee_request_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("employee_requests.id"),
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False,
        server_default=func.now(), onupdate=func.now(),
    )
    last_synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    # Mirrored from Google's groups.list response. ``aliases`` merges
    # the editable ``aliases`` and read-only ``nonEditableAliases``
    # arrays — both route to the same group from a user's point of
    # view, the distinction only matters when DELETE-ing an alias.
    # ``NULL`` means "never synced yet" (vs ``[]`` = "synced, no
    # aliases") so the UI can distinguish.
    aliases: Mapped[list[str] | None] = mapped_column(JSON, nullable=True)
    # Google's ``directMembersCount`` — direct members only, does NOT
    # expand nested groups. Nullable for the same "not yet synced"
    # reason; the UI renders ``—`` rather than ``0`` when null.
    direct_members_count: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # Cloud Identity dynamic group? Populated by ``google.group_sync`` via
    # the Cloud Identity ``groups.list(view=FULL)`` ``dynamicGroupMetadata``
    # field. ``None`` = never determined (e.g. the SA lacks the
    # cloud-identity.groups.readonly scope → sync degrades gracefully and
    # leaves this NULL). ``True`` = membership is rule-computed, so a
    # member's self-service "leave" is a no-op (Google re-adds / rejects)
    # — the UI disables 退出 and points the user to IT.
    is_dynamic: Mapped[bool | None] = mapped_column(Boolean, nullable=True)

    # ── Dynamic-group management (HR-editable membership rules) ──────
    # Mirrored from Cloud Identity ``dynamicGroupMetadata.queries[0].query``
    # by ``google.group_sync``. The raw CEL string — source of truth for
    # groups Helix can't parse into a structured rule.
    membership_query: Mapped[str | None] = mapped_column(Text, nullable=True)
    # Parsed :class:`~app.domains.email_group.dynamic_cel.StructuredRule`
    # (``.to_dict()``): {field, match_values, include_emails, negate,
    # parseable, raw_query}. ``parseable=False`` → the UI shows the raw
    # query read-only ("managed in Google"). NULL = not yet synced.
    dynamic_rule: Mapped[dict | None] = mapped_column(JSON, nullable=True)
    # Match values in the rule that currently match ZERO active users —
    # the "HR renamed the department and the group silently emptied" case.
    # Refreshed on every group sync / drift check. NULL = never checked,
    # [] = checked, all values live.
    dynamic_dead_values: Mapped[list[str] | None] = mapped_column(
        JSON, nullable=True,
    )
    # Per-group opt-in: also warn when a NEW attribute value appears that
    # this group's rule doesn't cover ("a new L1 dept exists but g-all
    # doesn't include it"). Default OFF — most groups intentionally
    # exclude some values (e.g. g-all deliberately omits China Business
    # Group), so unsolicited "you're missing X" alerts would be noise.
    alert_on_missing_values: Mapped[bool] = mapped_column(
        Boolean, nullable=False, default=False, server_default="0",
    )
    # Dedup signature for the drift notifier — a hash of the current dead
    # (and, when opted-in, missing) value set. We only re-notify IT when
    # this changes, so a standing unfixed drift doesn't ping daily.
    drift_signature: Mapped[str | None] = mapped_column(
        String(64), nullable=True,
    )

    owners: Mapped[list[EmailGroupOwner]] = relationship(
        back_populates="group",
        cascade="all, delete-orphan",
    )


class EmailGroupOwner(Base):
    """Join table: N owners per EmailGroup.

    ``ON DELETE CASCADE`` on both FKs so deleting a person or group
    removes the link. Unique (group_id, person_id) — a person is an
    owner at most once per group.
    """

    __tablename__ = "email_group_owners"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        UniqueConstraint(
            "email_group_id", "person_id",
            name="uq_email_group_owners_group_person",
        ),
        Index("ix_email_group_owners_person_id", "person_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    email_group_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("email_groups.id", ondelete="CASCADE"), nullable=False,
    )
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="CASCADE"), nullable=False,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )

    group: Mapped[EmailGroup] = relationship(back_populates="owners")


class EmailGroupJoinRequest(Base):
    """A member's request to JOIN a mail group, decided by an owner.

    The directory promises "点『申请加入』,走审批;负责人通过后你就成为
    成员". This is the record of that request. It is an IN-HELIX approval
    (NOT a Feishu template) because a group's owner is an arbitrary
    employee, not a fixed Feishu approver — mirrors the cloud-access
    grant model (``CmdbCloudAccessGrant``) where "being an owner is a
    data-driven duty, not a role".

    Lifecycle ``status``:

    * ``pending``   — awaiting an owner decision.
    * ``approved``  — an owner approved; the applicant was added to the
      Google group (``members.insert`` role=MEMBER, idempotent).
    * ``rejected``  — an owner declined.
    * ``cancelled`` — the requester withdrew before a decision.

    Only ONE ``pending`` row may exist per (group, requester): the
    generated ``open_request_key`` collapses to NULL for terminal rows
    so a user may re-apply after a rejection, but a double-submit race
    hits the unique index and 409s cleanly (mirrors the DAX
    ``active_semantic_key`` trick — MySQL/TiDB have no partial indexes).
    """

    __tablename__ = "email_group_join_requests"
    __mapper_args__ = {"eager_defaults": True}
    __table_args__ = (
        Index(
            "ix_email_group_join_requests_group_status",
            "email_group_id", "status",
        ),
        Index("ix_email_group_join_requests_requester", "requester_id"),
        UniqueConstraint(
            "open_request_key",
            name="uq_email_group_join_requests_open",
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    email_group_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("email_groups.id", ondelete="CASCADE"), nullable=False,
    )
    requester_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="CASCADE"), nullable=False,
    )
    reason: Mapped[str | None] = mapped_column(String(500))
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="pending",
    )
    # Person who approved / rejected. NULL while pending or after a
    # self-cancel.
    decided_by_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    decision_note: Mapped[str | None] = mapped_column(String(500))
    # Generated single-pending guard — see class docstring. Only
    # ``pending`` rows carry a non-NULL key, so the UNIQUE index enforces
    # "at most one open request per (group, requester)" without blocking
    # a re-apply after a terminal decision.
    open_request_key: Mapped[str | None] = mapped_column(
        String(80),
        Computed(
            "CASE WHEN status = 'pending' THEN "
            "CONCAT(email_group_id, '|', requester_id) ELSE NULL END",
        ),
        nullable=True,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False,
        server_default=func.now(), onupdate=func.now(),
    )

    group: Mapped[EmailGroup] = relationship()