"""
LeadAI — Database module.
Async SQLAlchemy with SQLite for leads, calls, transcripts, and campaigns.
"""
import datetime
from sqlalchemy import (
    Column, Integer, String, Text, DateTime, Float,
    ForeignKey, Enum, Boolean, create_engine
)
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.orm import declarative_base, relationship
from app.config import DATABASE_URL

# --- Engine & Session ---
engine = create_async_engine(DATABASE_URL, echo=False)
async_session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

Base = declarative_base()


# --- Models ---

class Campaign(Base):
    """A calling campaign with a specific purpose and AI script."""
    __tablename__ = "campaigns"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    description = Column(Text, default="")
    system_prompt = Column(Text, default="")
    voice = Column(String(100), default="en-US-GuyNeural")
    status = Column(String(50), default="draft")  # draft, active, paused, completed
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    # Relationships
    leads = relationship("Lead", back_populates="campaign", lazy="selectin")


class Lead(Base):
    """A lead/contact to be called."""
    __tablename__ = "leads"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    phone = Column(String(50), nullable=False)
    company = Column(String(255), default="")
    purpose = Column(Text, default="")
    status = Column(String(50), default="pending")  # pending, calling, completed, failed, no_answer
    notes = Column(Text, default="")
    campaign_id = Column(Integer, ForeignKey("campaigns.id"), nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    # Relationships
    campaign = relationship("Campaign", back_populates="leads")
    calls = relationship("Call", back_populates="lead", lazy="selectin")


class Call(Base):
    """A phone call record."""
    __tablename__ = "calls"

    id = Column(Integer, primary_key=True, autoincrement=True)
    lead_id = Column(Integer, ForeignKey("leads.id"), nullable=False)
    twilio_call_sid = Column(String(100), default="")
    status = Column(String(50), default="initiated")  # initiated, ringing, in_progress, completed, failed, no_answer
    outcome = Column(String(100), default="")  # interested, not_interested, callback, voicemail, error
    duration_seconds = Column(Float, default=0.0)
    started_at = Column(DateTime, default=datetime.datetime.utcnow)
    ended_at = Column(DateTime, nullable=True)
    error_message = Column(Text, default="")

    # Relationships
    lead = relationship("Lead", back_populates="calls")
    transcripts = relationship("Transcript", back_populates="call", lazy="selectin")


class Transcript(Base):
    """Individual message in a call transcript."""
    __tablename__ = "transcripts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    call_id = Column(Integer, ForeignKey("calls.id"), nullable=False)
    speaker = Column(String(20), nullable=False)  # "agent" or "human"
    text = Column(Text, nullable=False)
    timestamp = Column(DateTime, default=datetime.datetime.utcnow)

    # Relationships
    call = relationship("Call", back_populates="transcripts")


# --- Database Lifecycle ---

async def init_db():
    """Create all tables."""
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)


async def get_session() -> AsyncSession:
    """Get an async database session."""
    async with async_session() as session:
        yield session