"""
SQLAlchemy ORM models.

Why a database at all, given Redis already caches results: Redis is
ephemeral (TTL-based) and not queryable in any structured way. The
client will want to know things like "how many requests for rice in
Thanjavur did we get last month" or "show me every analysis that hit a
conflict flag" — that needs a real relational store with indexes, not
a key-value cache.

AnalysisRecord is the permanent audit trail: every completed analysis,
regardless of whether it was served from cache or freshly computed.

User / CropPlan / Notification support the customer-facing SaaS layer
(see app/api/v1/endpoints/auth.py and docs/architecture.md) — these are
separate from the original API-key-based service auth, which remains in
place for any machine-to-machine integrations the client may also want.
"""

import uuid
from datetime import datetime

from sqlalchemy import JSON, Boolean, DateTime, ForeignKey, Integer, String, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class User(Base):
    """
    Customer account for the SaaS web application. Distinct from the
    API-key auth used elsewhere in this system — API keys identify a
    calling SERVICE/integration, a User identifies an individual
    customer logging into the dashboard.
    """
    __tablename__ = "users"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True)
    hashed_password: Mapped[str] = mapped_column(String(255))
    full_name: Mapped[str] = mapped_column(String(255), nullable=True)
    organization: Mapped[str] = mapped_column(String(255), nullable=True)

    is_active: Mapped[bool] = mapped_column(Boolean, default=True)
    is_verified: Mapped[bool] = mapped_column(Boolean, default=False)

    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    last_login_at: Mapped[datetime] = mapped_column(DateTime, nullable=True)

    crop_plans: Mapped[list["CropPlan"]] = relationship(back_populates="user")
    notifications: Mapped[list["Notification"]] = relationship(back_populates="user")


class CropPlan(Base):
    """
    A saved crop planning scenario a customer created via the dashboard
    (module 1.1.3 / 1.1.8 in the SaaS spec). Each plan can have multiple
    linked AnalysisRecord rows over time — e.g. the customer re-runs the
    analysis after climate data updates, or compares scenarios with
    different risk tolerance settings.
    """
    __tablename__ = "crop_plans"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id"), index=True)

    name: Mapped[str] = mapped_column(String(255))  # user-given label, e.g. "Kharif 2026 - Thanjavur rice"
    crop: Mapped[str] = mapped_column(String(50))
    location: Mapped[str] = mapped_column(String(100))
    planting_month: Mapped[int] = mapped_column(Integer)
    risk_tolerance: Mapped[str] = mapped_column(String(20), default="moderate")  # low | moderate | high

    is_archived: Mapped[bool] = mapped_column(Boolean, default=False)

    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    user: Mapped["User"] = relationship(back_populates="crop_plans")


class Notification(Base):
    """
    In-app / email alert record (module 1.1.11). Generated either by a
    completed analysis (e.g. "your rice plan is now classified as
    risky") or by a scheduled job (e.g. seasonal reminders) — the
    generation logic lives in app/services/, this table is just storage
    plus read/unread state for the dashboard bell icon.
    """
    __tablename__ = "notifications"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id"), index=True)

    title: Mapped[str] = mapped_column(String(255))
    message: Mapped[str] = mapped_column(Text)
    severity: Mapped[str] = mapped_column(String(20), default="info")  # info | warning | critical
    related_crop_plan_id: Mapped[str] = mapped_column(String(36), ForeignKey("crop_plans.id"), nullable=True)

    is_read: Mapped[bool] = mapped_column(Boolean, default=False)
    email_sent: Mapped[bool] = mapped_column(Boolean, default=False)

    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)

    user: Mapped["User"] = relationship(back_populates="notifications")


class AnalysisRecord(Base):
    __tablename__ = "analysis_records"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    request_id: Mapped[str] = mapped_column(String(64), index=True)

    # Nullable: analyses can still be triggered by API-key service calls
    # with no logged-in customer attached (e.g. internal testing, or a
    # client-side integration that isn't the SaaS dashboard itself).
    user_id: Mapped[str] = mapped_column(String(36), ForeignKey("users.id"), nullable=True, index=True)
    crop_plan_id: Mapped[str] = mapped_column(String(36), ForeignKey("crop_plans.id"), nullable=True, index=True)

    crop: Mapped[str] = mapped_column(String(50), index=True)
    district: Mapped[str] = mapped_column(String(100), index=True)
    month: Mapped[int] = mapped_column(Integer)

    feasibility_label: Mapped[str] = mapped_column(String(20), nullable=True)
    expected_yield_ton_ha: Mapped[float] = mapped_column(nullable=True)
    had_conflicts: Mapped[bool] = mapped_column(Boolean, default=False)
    degraded_models: Mapped[str] = mapped_column(Text, nullable=True)  # comma-separated

    full_result: Mapped[dict] = mapped_column(JSON)
    model_version: Mapped[str] = mapped_column(String(20))

    served_from_cache: Mapped[bool] = mapped_column(Boolean, default=False)
    processing_ms: Mapped[int] = mapped_column(Integer)

    api_key_prefix: Mapped[str] = mapped_column(String(20), nullable=True)
    client_ip: Mapped[str] = mapped_column(String(64), nullable=True)

    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)


class FailedRequestLog(Base):
    """
    Separate table for failed requests — deliberately kept apart from
    AnalysisRecord so a flood of failures (e.g. during an LLM outage)
    doesn't pollute the table used for legitimate usage analytics.
    """
    __tablename__ = "failed_request_logs"

    id: Mapped[str] = mapped_column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    request_id: Mapped[str] = mapped_column(String(64), index=True)
    error_code: Mapped[str] = mapped_column(String(50), index=True)
    error_message: Mapped[str] = mapped_column(Text)
    input_payload: Mapped[dict] = mapped_column(JSON, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=datetime.utcnow, index=True)