from __future__ import annotations
from datetime import datetime
from decimal import Decimal
from typing import Optional
from uuid import UUID

from sqlalchemy import Boolean, DateTime, ForeignKey, Numeric, String, Text
from sqlalchemy.dialects.postgresql import UUID as PG_UUID
from sqlalchemy.orm import Mapped, mapped_column

from src.database.base import TenantModel


class ContactInquiry(TenantModel):
    __tablename__ = "contact_inquiries"

    # Override tenant_id with explicit FK
    tenant_id: Mapped[UUID] = mapped_column(
        PG_UUID(as_uuid=True),
        ForeignKey("accounts.id", ondelete="CASCADE"),
        nullable=False,
        index=True,
    )

    # Auto-generated reference: INQ-YYYY-NNNN
    reference_id: Mapped[str] = mapped_column(String(30), nullable=False, unique=True, index=True)

    # Source type
    source_type: Mapped[str] = mapped_column(String(30), nullable=False, default="contact_form")
    # Values: contact_form | plot_availability | call_booking | email

    # Common fields
    sender_name: Mapped[str] = mapped_column(String(255), nullable=False)
    sender_email: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    sender_phone: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    message: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    # contact_form columns (INDL-24)
    topic: Mapped[str] = mapped_column(String(50), nullable=False, default="other")
    # Values: finding_record | memorial_tribute | pre_need_plot | service_arrangement | genealogy_research | other
    person_or_plot: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    subscribe_newsletter: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)

    # Status and workflow
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="new")
    # Values: new | read | converted | spam

    # Optional link to converted opportunity
    converted_opportunity_id: Mapped[Optional[UUID]] = mapped_column(
        PG_UUID(as_uuid=True),
        ForeignKey("opportunities.id", ondelete="SET NULL"),
        nullable=True,
    )

    # Audit
    created_by_user_id: Mapped[Optional[UUID]] = mapped_column(
        PG_UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="SET NULL"),
        nullable=True,
    )
    ip_address: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    user_agent: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    # plot_availability columns
    plot_number: Mapped[Optional[str]] = mapped_column(String(50), nullable=True)
    section_name: Mapped[Optional[str]] = mapped_column(String(100), nullable=True)
    listed_price: Mapped[Optional[Decimal]] = mapped_column(Numeric(10, 2), nullable=True)

    # call_booking columns
    call_date: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
    agent_name: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
    notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)

    # email columns
    email_subject: Mapped[Optional[str]] = mapped_column(String(500), nullable=True)
    email_received_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)