"""SQLAlchemy models — full schema for BulkGmailMailer.

All OAuth secrets are stored encrypted (Fernet). Sensitive columns:
  oauth_tokens.access_token / refresh_token
  gmail_accounts.client_secret (per-account imported config)
"""
from datetime import datetime, timezone
from sqlalchemy import (JSON, Boolean, Column, DateTime, Enum, Float, ForeignKey,
                        Index, Integer, LargeBinary, String, Text, UniqueConstraint)
from sqlalchemy.orm import DeclarativeBase, relationship

from app.core import encryption


def utcnow():
    return datetime.now(timezone.utc)


class Base(DeclarativeBase):
    pass


class TimestampMixin:
    created_at = Column(DateTime(timezone=True), default=utcnow, nullable=False, index=True)
    updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)


class User(Base, TimestampMixin):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    email = Column(String(320), unique=True, nullable=False, index=True)
    password_hash = Column(String(255), nullable=False)
    full_name = Column(String(200), default="")
    is_admin = Column(Boolean, default=False, nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    last_login_at = Column(DateTime(timezone=True))

    gmail_accounts = relationship("GmailAccount", back_populates="user", cascade="all, delete-orphan")
    audit_logs = relationship("AuditLog", back_populates="user")


class OAuthClientConfig(Base, TimestampMixin):
    """An imported Google OAuth client configuration (from client_secret JSON).
    Secrets stored encrypted. Multiple configs may exist (e.g. per project)."""
    __tablename__ = "oauth_client_configs"
    id = Column(Integer, primary_key=True)
    name = Column(String(200), nullable=False)
    client_id = Column(String(300), nullable=False)
    client_secret_enc = Column(String(600), nullable=False)  # encrypted
    redirect_uri = Column(String(500), default="")
    project_id = Column(String(200), default="")
    is_active = Column(Boolean, default=True, nullable=False)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"))

    @property
    def client_secret(self) -> str:
        return encryption.decrypt(self.client_secret_enc)

    @client_secret.setter
    def client_secret(self, value: str) -> None:
        self.client_secret_enc = encryption.encrypt(value)


class GmailAccount(Base, TimestampMixin):
    __tablename__ = "gmail_accounts"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)
    email = Column(String(320), nullable=False)
    display_name = Column(String(200), default="")
    status = Column(String(30), default="pending")  # pending|authorized|expired|revoked|error
    sending_method = Column(String(20), default="api")  # api|smtp
    auth_provider = Column(String(60), default="google")
    google_user_id = Column(String(120), default="")
    # Per-account config (administrator-configurable)
    daily_limit = Column(Integer, default=200)          # provider/configured daily cap
    campaign_limit = Column(Integer, default=200)       # per-account per-campaign cap
    sends_today = Column(Integer, default=0)
    last_send_date = Column(DateTime(timezone=True))
    cooldown_until = Column(DateTime(timezone=True))
    last_success_at = Column(DateTime(timezone=True))
    last_error = Column(Text, default="")
    error_count = Column(Integer, default=0)
    token_expiry = Column(DateTime(timezone=True))
    # Imported client config reference (optional; may use server-level config)
    oauth_config_id = Column(Integer, ForeignKey("oauth_client_configs.id", ondelete="SET NULL"))
    # Encrypted SMTP OAuth2 credentials mirror (kept in oauth_tokens; this is metadata only)
    is_active = Column(Boolean, default=True, nullable=False)
    revoked_by_user = Column(Boolean, default=False)
    revoked_at = Column(DateTime(timezone=True))

    user = relationship("User", back_populates="gmail_accounts")
    # Eager-loaded (joined) so async endpoints/workers can read account.oauth_token
    # without triggering a lazy-load IO (which raises MissingGreenlet in async sessions).
    oauth_token = relationship("OAuthToken", back_populates="account", uselist=False,
                               cascade="all, delete-orphan", lazy="joined")
    oauth_config = relationship("OAuthClientConfig")

    __table_args__ = (UniqueConstraint("user_id", "email", name="uq_gmail_account_user_email"),
                      Index("ix_gmail_status", "status"),)

    def usage_summary(self):
        return {"daily_limit": self.daily_limit, "sends_today": self.sends_today,
                "campaign_limit": self.campaign_limit,
                "last_success_at": self.last_success_at.isoformat() if self.last_success_at else None,
                "errors": self.error_count, "status": self.status}


class OAuthToken(Base, TimestampMixin):
    __tablename__ = "oauth_tokens"
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey("gmail_accounts.id", ondelete="CASCADE"), nullable=False, index=True)
    access_token_enc = Column(String(1200), nullable=False)  # encrypted
    refresh_token_enc = Column(String(1200), default="")     # encrypted; may be empty if not granted
    token_type = Column(String(50), default="Bearer")
    scope = Column(Text, default="")
    expires_at = Column(DateTime(timezone=True))
    granted_scopes = Column(Text, default="")

    account = relationship("GmailAccount", back_populates="oauth_token")

    @property
    def access_token(self) -> str:
        return encryption.decrypt(self.access_token_enc)

    @access_token.setter
    def access_token(self, value: str) -> None:
        self.access_token_enc = encryption.encrypt(value)

    @property
    def refresh_token(self) -> str:
        return encryption.decrypt(self.refresh_token_enc or "")

    @refresh_token.setter
    def refresh_token(self, value: str) -> None:
        self.refresh_token_enc = encryption.encrypt(value or "")

    def to_safe_dict(self):
        return {"id": self.id, "account_id": self.account_id, "token_type": self.token_type,
                "scope": self.scope, "expires_at": self.expires_at.isoformat() if self.expires_at else None,
                "has_refresh_token": bool(self.refresh_token_enc)}