import os
import asyncpg
from typing import Optional
from uuid import UUID
from datetime import date

DATABASE_URL = os.getenv("DATABASE_URL")  # Supabase Postgres URL

async def get_conn():
    return await asyncpg.connect(DATABASE_URL)

async def fetch_monthly_summary(date_from: Optional[str] = None, date_to: Optional[str] = None, advisor: Optional[str] = None):
    """
    Fetch monthly KPI summary from v_retention_pen_monthly_v2.
    Returns up to last 12 months sorted oldest → newest.
    Note: advisor parameter is accepted for API compatibility but not used (view doesn't have advisor column).
    """
    q = """
      SELECT month_key, contact_rate, reply_rate, retention_pen_pct,
             total_contacts, total_replies, drop_off_total
      FROM v_retention_pen_monthly_v2
      WHERE ($1::date IS NULL OR month_key >= $1::date)
        AND ($2::date IS NULL OR month_key <  $2::date)
      ORDER BY month_key DESC
      LIMIT 12
    """
    conn = await get_conn()
    try:
        rows = await conn.fetch(q, date_from, date_to)
        # Reverse to get chronological order (oldest → newest)
        return list(reversed([dict(r) for r in rows]))
    except Exception as e:
        print(f"Warning: fetch_monthly_summary failed: {e}")
        return []
    finally:
        await conn.close()

async def fetch_retention_pen(month: Optional[str] = None, payments_lte: Optional[int] = None, limit: int = 100, advisor: Optional[str] = None):
    """
    Fetch customer-level retention pen data from v_retention_book_current.
    Joins with dp_customer (UUID-based) and v_agreement_latest to get customer names and agreement numbers.
    """
    q = """
      SELECT 
        COALESCE(dc.first_name || ' ' || dc.last_name, v.model) as customer_name,
        v.reg,
        v.payments_remaining,
        v.book_month as payment_month,
        COALESCE(agr.agreement_number, v.reg) as agreement_number
      FROM v_retention_book_current v
      LEFT JOIN dp_customer dc ON dc.customer_id = v.customer_id
      LEFT JOIN v_agreement_latest agr ON agr.registration = v.reg
      WHERE ($1::text IS NULL OR to_char(v.book_month, 'YYYY-MM') = $1::text)
        AND ($2::int  IS NULL OR v.payments_remaining <= $2::int)
      ORDER BY v.book_month ASC, v.payments_remaining ASC
      LIMIT $3
    """
    conn = await get_conn()
    try:
        rows = await conn.fetch(q, month, payments_lte, limit)
        return [dict(r) for r in rows]
    except Exception as e:
        print(f"Warning: fetch_retention_pen failed: {e}")
        return []
    finally:
        await conn.close()

async def fetch_campaigns(
    status: Optional[str] = None,
    today: Optional[date] = None,
):
    """
    Fetch campaigns, optionally filtering by status and today's date.
    - status: filter by campaign status (draft, running, etc.)
    - today:  if provided, ensures today is between date_from and date_to
    """
    query = """
        select *
        from dp_campaigns
        where 1 = 1
    """

    params = []

    if status:
        query += " AND status = $1"
        params.append(status)

    if today:
        if status:
            query += " AND date_from <= $2 AND date_to >= $2"
        else:
            query += " AND date_from <= $1 AND date_to >= $1"
        params.append(today)

    conn = await get_conn()
    try:
        rows = await conn.fetch(query, *params)
        return [dict(r) for r in rows]
    finally:
        await conn.close()

async def call_ingest_function(storage_path: str):
    q = "select dp_ingest_snapshot_from_storage($1) as ok"
    conn = await get_conn()
    try:
        row = await conn.fetchrow(q, storage_path)
        return dict(row) if row else {"ok": None}
    except Exception as e:
        # Bubble the error message so frontend can see missing function
        return {"error": str(e)}
    finally:
        await conn.close()

async def fetch_advisors():
    # Uses dp_contact as the unified contact/reply log
    q = """
      SELECT DISTINCT advisor
      FROM dp_contact
      WHERE advisor IS NOT NULL AND advisor <> ''
      ORDER BY advisor
    """
    conn = await get_conn()
    try:
        rows = await conn.fetch(q)
        return [r["advisor"] for r in rows]
    except Exception as e:
        print(f"Warning: fetch_advisors failed: {e}")
        return []
    finally:
        await conn.close()

async def fetch_template(template_key: str):
    """
    Fetch a template and its objective/guidance from dp_templates + dp_objectives.
    Returns None if not found or not active.
    """
    q = """
      SELECT 
        t.template_key,
        t.category,
        t.stage,
        t.whatsapp_body,
        o.objective,
        o.guidance
      FROM dp_templates t
      LEFT JOIN dp_objectives o ON o.template_key = t.template_key
      WHERE t.template_key = $1 AND t.active = true
      LIMIT 1
    """
    conn = await get_conn()
    try:
        row = await conn.fetchrow(q, template_key)
        if row:
            return dict(row)
        return None
    except Exception as e:
        print(f"Error: fetch_template({template_key}) failed: {e}")
        raise  # Re-raise so the endpoint can handle it
    finally:
        await conn.close()

async def defer_conversation(conversation_id: str, defer_until, reason: Optional[str] = None) -> bool:
    """
    Pause a conversation until a future date.
    Returns True if a row was updated, False otherwise.
    """
    q = """
      UPDATE dp_conversations
      SET status = 'paused',
          defer_until = $2,
          defer_reason = $3,
          updated_at = now()
      WHERE conversation_id = $1
    """
    conn = await get_conn()
    try:
        # Convert string UUID to UUID object for asyncpg
        conversation_uuid = UUID(conversation_id)
        result = await conn.execute(q, conversation_uuid, defer_until, reason)
        # result format: "UPDATE n" where n is number of rows affected
        rows_affected = int(result.split()[-1]) if result else 0
        return rows_affected > 0
    except Exception as e:
        print(f"Error: defer_conversation({conversation_id}) failed: {e}")
        return False
    finally:
        await conn.close()

async def resume_conversation(conversation_id: str) -> bool:
    """
    Resume a paused conversation (set status to 'active', clear defer fields).
    Returns True if a row was updated, False otherwise.
    """
    q = """
      UPDATE dp_conversations
      SET status = 'active',
          defer_until = NULL,
          defer_reason = NULL,
          updated_at = now()
      WHERE conversation_id = $1
    """
    conn = await get_conn()
    try:
        # Convert string UUID to UUID object for asyncpg
        conversation_uuid = UUID(conversation_id)
        result = await conn.execute(q, conversation_uuid)
        rows_affected = int(result.split()[-1]) if result else 0
        return rows_affected > 0
    except Exception as e:
        print(f"Error: resume_conversation({conversation_id}) failed: {e}")
        return False
    finally:
        await conn.close()

async def fetch_conversation_status(conversation_id: str):
    """
    Return status, defer_until, defer_reason, and escalation info for a given conversation_id.
    """
    conv_uuid = UUID(conversation_id)
    
    conn = await get_conn()
    try:
        row = await conn.fetchrow(
            """
            SELECT status, defer_until, defer_reason, escalation_reason, escalation_at
            FROM dp_conversations
            WHERE conversation_id = $1
            """,
            conv_uuid,
        )
        if not row:
            return None
        return {
            "status": row["status"],
            "defer_until": row["defer_until"],
            "defer_reason": row["defer_reason"],
            "escalation_reason": row["escalation_reason"],
            "escalation_at": row["escalation_at"],
        }
    finally:
        await conn.close()

async def insert_message(
    conversation_id: str,
    direction: str,
    channel: str,
    sender: str,
    message_body: str,
    template_key: str | None = None,
    mode: str | None = None,
    ab_variant: str | None = None,
) -> None:
    """
    Insert a message into dp_messages table for logging.
    """
    conv_uuid = UUID(conversation_id)
    conn = await get_conn()
    try:
        await conn.execute(
            """
            INSERT INTO dp_messages (
                conversation_id, direction, channel, sender,
                message_body, template_key, mode, ab_variant
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
            """,
            conv_uuid,
            direction,
            channel,
            sender,
            message_body,
            template_key,
            mode,
            ab_variant,
        )
    finally:
        await conn.close()

async def fetch_messages_for_conversation(conversation_id: str, limit: int = 50):
    """
    Return the most recent messages for a conversation, ordered oldest → newest.
    """
    import uuid
    conv_uuid = uuid.UUID(conversation_id)
    conn = await get_conn()
    try:
        rows = await conn.fetch(
            """
            select message_id,
                   direction,
                   channel,
                   sender,
                   message_body,
                   template_key,
                   mode,
                   created_at
            from dp_messages
            where conversation_id = $1
            order by created_at asc
            limit $2
            """,
            conv_uuid,
            limit,
        )
        return [
            {
                "message_id": str(r["message_id"]),
                "direction": r["direction"],
                "channel": r["channel"],
                "sender": r["sender"],
                "message_body": r["message_body"],
                "template_key": r["template_key"],
                "mode": r["mode"],
                "created_at": r["created_at"].isoformat() if r["created_at"] else None,
            }
            for r in rows
        ]
    finally:
        await conn.close()

async def release_conn(conn):
    """Helper to close connection - for backwards compatibility with fetch_messages_for_conversation."""
    await conn.close()


async def escalate_conversation(conversation_id: str, reason: str) -> bool:
    """
    Set a conversation to paused and mark escalation details.
    Sets needs_human_review = TRUE so manager can see it in dashboard.
    Clears defer fields because this is a manager escalation, not a scheduled pause.
    """
    import uuid as uuid_module

    try:
        conv_uuid = uuid_module.UUID(conversation_id)
    except Exception:
        return False

    conn = await get_conn()
    try:
        result = await conn.execute("""
            UPDATE dp_conversations
            SET status = 'paused',
                needs_human_review = TRUE,
                defer_until = NULL,
                defer_reason = NULL,
                escalation_reason = $2,
                escalation_at = NOW(),
                updated_at = NOW()
            WHERE conversation_id = $1
        """, conv_uuid, reason)

        return result == "UPDATE 1"
    finally:
        await conn.close()


async def resolve_manager_reply(conversation_id: str) -> bool:
    """
    Resolve an escalation when a manager sends a reply.
    Sets status to 'active', clears needs_human_review and escalation fields.
    """
    import uuid as uuid_module

    try:
        conv_uuid = uuid_module.UUID(conversation_id)
    except Exception:
        return False

    conn = await get_conn()
    try:
        result = await conn.execute("""
            UPDATE dp_conversations
            SET status = 'active',
                needs_human_review = FALSE,
                escalation_reason = NULL,
                updated_at = NOW()
            WHERE conversation_id = $1
        """, conv_uuid)

        return result == "UPDATE 1"
    finally:
        await conn.close()


async def fetch_active_campaign_by_key(
    campaign_key: str,
    channel: Optional[str] = None,
    check_date: Optional[date] = None,
) -> Optional[dict]:
    """
    Find an active campaign matching the given campaign_key (matched against 'type' column).
    
    Args:
        campaign_key: The campaign type to match (e.g., "pcp_stage_auto", "renewal_offer")
        channel: Optional channel filter (not currently used as dp_campaigns has no channel column)
        check_date: Date to check against campaign window (defaults to today)
    
    Returns:
        Campaign dict if found and active, None otherwise.
        
    A campaign is considered active if:
        - status = 'running'
        - date_from <= check_date (or date_from is NULL)
        - date_to >= check_date (or date_to is NULL)
    """
    if check_date is None:
        check_date = date.today()
    
    query = """
        SELECT *
        FROM dp_campaigns
        WHERE type = $1
          AND status = 'running'
          AND (date_from IS NULL OR date_from <= $2)
          AND (date_to IS NULL OR date_to >= $2)
        LIMIT 1
    """
    
    conn = await get_conn()
    try:
        row = await conn.fetchrow(query, campaign_key, check_date)
        if row:
            return dict(row)
        return None
    finally:
        await conn.close()


# DEBUG HELPERS

async def get_conversation_with_messages(conversation_id: UUID) -> dict | None:
    """
    Return a single conversation and all its messages as a dict:
    {
      "conversation": { ...dp_conversations columns... },
      "messages": [ { ...dp_messages columns... }, ... ]
    }
    """
    conn = await get_conn()
    try:
        conv_row = await conn.fetchrow(
            """
            SELECT *
            FROM dp_conversations
            WHERE conversation_id = $1
            """,
            conversation_id,
        )
        if not conv_row:
            return None

        msg_rows = await conn.fetch(
            """
            SELECT *
            FROM dp_messages
            WHERE conversation_id = $1
            ORDER BY created_at ASC
            """,
            conversation_id,
        )

        conversation = dict(conv_row)
        messages = [dict(r) for r in msg_rows]

        return {
            "conversation": conversation,
            "messages": messages,
        }
    finally:
        await conn.close()


async def list_conversations_for_customer(customer_id: UUID) -> list[dict]:
    """
    Return all conversations for a customer, newest first, with a light summary.
    """
    conn = await get_conn()
    try:
        rows = await conn.fetch(
            """
            SELECT
                c.conversation_id,
                c.customer_id,
                c.dealer_id,
                c.channel,
                c.campaign,
                c.stage,
                c.status,
                c.appointment_status,
                c.soft_no_count,
                c.confusion_count,
                c.needs_human_review,
                c.escalation_reason,
                c.defer_until,
                c.defer_reason,
                c.created_at,
                c.updated_at
            FROM dp_conversations c
            WHERE c.customer_id = $1
            ORDER BY c.created_at DESC
            """,
            customer_id,
        )
        return [dict(r) for r in rows]
    finally:
        await conn.close()


async def get_conversation_summary() -> dict:
    """
    Return a summary of conversations by stage, status, and campaign.
    """
    conn = await get_conn()
    try:
        # Total conversations
        total_row = await conn.fetchrow(
            "SELECT COUNT(*) AS total FROM dp_conversations"
        )
        total = total_row["total"] if total_row else 0

        # By status
        status_rows = await conn.fetch(
            """
            SELECT status, COUNT(*) AS count
            FROM dp_conversations
            GROUP BY status
            ORDER BY status
            """
        )
        by_status = {row["status"] or "unknown": row["count"] for row in status_rows}

        # By stage
        stage_rows = await conn.fetch(
            """
            SELECT stage, COUNT(*) AS count
            FROM dp_conversations
            GROUP BY stage
            ORDER BY stage
            """
        )
        by_stage = {row["stage"] or "unknown": row["count"] for row in stage_rows}

        # By campaign
        campaign_rows = await conn.fetch(
            """
            SELECT campaign, COUNT(*) AS count
            FROM dp_conversations
            GROUP BY campaign
            ORDER BY campaign
            """
        )
        by_campaign = {row["campaign"] or "unknown": row["count"] for row in campaign_rows}

        return {
            "total_conversations": total,
            "by_status": by_status,
            "by_stage": by_stage,
            "by_campaign": by_campaign,
        }
    finally:
        await conn.close()


async def get_active_conversation_for_number(phone_e164: str) -> dict | None:
    """
    Given a phone number in +44... E.164 format, return the most recent
    non-DNC conversation for that customer (if any).
    
    Join dp_customer.phone_mobile -> dp_conversations.customer_id.
    Exclude status='dnc'. Order by created_at DESC limit 1.
    
    If phone matches multiple customers, picks the most recent conversation
    across all of them.
    
    Returns dict with conversation + customer details, or None if not found.
    """
    conn = await get_conn()
    try:
        row = await conn.fetchrow(
            """
            SELECT 
                c.conversation_id,
                c.customer_id,
                c.dealer_id,
                c.template_key,
                c.stage,
                c.status,
                c.defer_until,
                c.defer_reason,
                c.from_number,
                c.to_number,
                c.channel,
                c.campaign,
                c.soft_no_count,
                c.confusion_count,
                c.needs_human_review,
                c.appointment_status,
                c.escalation_reason,
                c.created_at,
                c.updated_at,
                cust.first_name,
                cust.last_name,
                cust.phone_mobile
            FROM dp_customer cust
            JOIN dp_conversations c ON c.customer_id = cust.customer_id
            WHERE cust.phone_mobile = $1
              AND c.status != 'dnc'
            ORDER BY c.created_at DESC
            LIMIT 1
            """,
            phone_e164,
        )
        if row:
            return dict(row)
        return None
    finally:
        await conn.close()


async def get_customer_by_phone(phone_e164: str) -> dict | None:
    """
    Find a customer by phone number in E.164 format.
    Returns dict with customer details, or None if not found.
    """
    conn = await get_conn()
    try:
        row = await conn.fetchrow(
            """
            SELECT customer_id, phone_mobile, first_name, last_name
            FROM dp_customer
            WHERE phone_mobile = $1
            LIMIT 1
            """,
            phone_e164,
        )
        if row:
            return dict(row)
        return None
    finally:
        await conn.close()


async def check_dnc_conversation_for_number(phone_e164: str) -> bool:
    """
    Check if the customer with this phone number has ANY conversation with status='dnc'.
    This is used as a secondary DNC check - if ANY conversation is DNC, the customer
    should not be contacted.
    
    Returns True if a DNC conversation exists, False otherwise.
    """
    conn = await get_conn()
    try:
        row = await conn.fetchrow(
            """
            SELECT 1
            FROM dp_customer cust
            JOIN dp_conversations c ON c.customer_id = cust.customer_id
            WHERE cust.phone_mobile = $1
              AND c.status = 'dnc'
            LIMIT 1
            """,
            phone_e164,
        )
        return row is not None
    finally:
        await conn.close()