from pydantic import BaseModel
from typing import Optional
import uuid
from datetime import date
from app.db import get_conn, release_conn
from app.lia_rules import LIA_EOT_CAMPAIGN_OPENERS


class OutboundRequest(BaseModel):
    customer_id: Optional[str] = None
    reg: Optional[str] = None
    payments_remaining: Optional[int] = None
    campaign: str = "pcp_stage_auto"
    channel: str = "whatsapp"
    sandbox_mode: bool = False
    asset_type: Optional[str] = "new"
    from_number: Optional[str] = None


class OutboundResponse(BaseModel):
    message_text: str
    conversation_id: Optional[str] = None
    to_number: Optional[str] = None
    stage: Optional[str] = None


def select_outbound_stage(payments_remaining: Optional[int]) -> Optional[str]:
    """
    Determine the EOT campaign stage based on payments_remaining.
    Returns stage identifier like "18", "12", "9", "6", "3".
    """
    if payments_remaining is None:
        return None
    
    try:
        pr = int(payments_remaining)
    except (TypeError, ValueError):
        return None
    
    if pr >= 16:
        return "18"
    elif pr >= 10:
        return "12"
    elif pr >= 7:
        return "9"
    elif pr >= 4:
        return "6"
    else:
        return "3"


def select_outbound_template(stage: str, asset_type: str = "new") -> tuple[Optional[str], str]:
    """
    Select the correct EOT campaign opener template based on stage and asset_type.
    
    Args:
        stage: Campaign stage ("18", "12", "9", "6", "3")
        asset_type: "new" or "used"
    
    Returns:
        Tuple of (message template string, template_key) or (None, template_key) if not found
    """
    asset_suffix = "NEW" if asset_type.lower() == "new" else "USED"
    template_key = f"EOT_{stage}_{asset_suffix}"
    
    return LIA_EOT_CAMPAIGN_OPENERS.get(template_key), template_key


async def find_or_create_conversation(
    conn,
    customer_id: uuid.UUID,
    channel: str,
    campaign: str,
    dealer_id: str,
    from_number: Optional[str] = None,
    to_number: Optional[str] = None,
    stage: Optional[str] = None,
) -> uuid.UUID:
    """
    Find an existing open conversation for customer+channel+campaign, or create a new one.
    Note: template_key is not set here since it has a FK constraint on dp_templates.
    The template_key is tracked at the message level instead.
    """
    row = await conn.fetchrow(
        """
        SELECT conversation_id
        FROM dp_conversations
        WHERE customer_id = $1
          AND channel = $2
          AND campaign = $3
          AND status = 'active'
        ORDER BY created_at DESC
        LIMIT 1
        """,
        customer_id,
        channel,
        campaign,
    )
    
    if row:
        return row["conversation_id"]
    
    new_row = await conn.fetchrow(
        """
        INSERT INTO dp_conversations (
            customer_id, dealer_id, channel, campaign,
            from_number, to_number, stage, status
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, 'active')
        RETURNING conversation_id
        """,
        customer_id,
        uuid.UUID(dealer_id),
        channel,
        campaign,
        from_number,
        to_number,
        stage,
    )
    
    return new_row["conversation_id"]


async def log_outbound_message(
    conn,
    conversation_id: uuid.UUID,
    channel: str,
    message_body: str,
    template_key: Optional[str],
    mode: str = "outbound_campaign",
):
    """
    Log an outbound message to dp_messages using existing schema.
    Uses: conversation_id, direction, channel, sender, message_body, template_key, mode
    """
    await conn.execute(
        """
        INSERT INTO dp_messages (
            conversation_id, direction, channel, sender,
            message_body, template_key, mode
        )
        VALUES ($1, 'outbound', $2, 'lia', $3, $4, $5)
        """,
        conversation_id,
        channel,
        message_body,
        template_key,
        mode,
    )


class CampaignNotActiveError(Exception):
    """Raised when no active campaign matches the request."""
    pass


class DNCBlockedError(Exception):
    """Raised when customer has an active DNC (Do Not Contact) status."""
    pass


class CampaignNotFoundError(Exception):
    """Raised when the campaign is not found for the given site."""
    pass


class InvalidCustomerIdError(Exception):
    """Raised when customer_id is not a valid UUID."""
    pass


class MissingPhoneNumberError(Exception):
    """Raised when customer has no mobile phone number in dp_customer."""
    pass


class InvalidPaymentsBandError(Exception):
    """Raised when payments_remaining is not one of the valid bands [18, 12, 9, 6, 3]."""
    pass


class DuplicateConversationError(Exception):
    """Raised when a conversation already exists for this customer/campaign/stage/channel."""
    def __init__(self, message: str, conversation_id: str, stage: str):
        super().__init__(message)
        self.conversation_id = conversation_id
        self.stage = stage


class NudgeBlockedError(Exception):
    """Raised when a nudge cannot be sent due to conversation status (paused, deferred)."""
    def __init__(self, message: str, reason: str = "blocked"):
        super().__init__(message)
        self.reason = reason


class ConversationNotFoundError(Exception):
    """Raised when conversation_id does not exist in dp_conversations."""
    pass


VALID_PAYMENT_BANDS = (18, 12, 9, 6, 3)


def validate_uuid(value: str) -> uuid.UUID:
    """
    Validate and parse a string as UUID.
    
    Args:
        value: String to validate as UUID
        
    Returns:
        UUID object if valid
        
    Raises:
        InvalidCustomerIdError: If the string is not a valid UUID
    """
    try:
        return uuid.UUID(value)
    except (ValueError, AttributeError):
        raise InvalidCustomerIdError(f"'{value}' is not a valid UUID format")


async def fetch_campaign_for_outbound(
    campaign_name: str,
    sandbox_mode: bool = False,
    site_id: str = "lincoln_audi",
    check_date: Optional[date] = None,
) -> Optional[dict]:
    """
    Fetch campaign for outbound messaging with sandbox-aware validation.
    
    Args:
        campaign_name: The campaign name or type to look up
        sandbox_mode: If True, relax validation to just check row exists and isn't archived
        site_id: The site ID to filter by
        check_date: Date to check against campaign window (defaults to today)
    
    Returns:
        Campaign dict if found and valid, None otherwise.
    
    Sandbox mode (sandbox_mode=True):
        - Campaign row must exist (matched by name OR type)
        - status != 'archived' (allows 'active', 'running', 'paused', etc.)
        - Date range checks are SKIPPED
    
    Production mode (sandbox_mode=False):
        - Campaign row must exist (matched by name OR type)
        - status must be 'running' or 'active'
        - 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()
    
    conn = await get_conn()
    try:
        row = await conn.fetchrow(
            """
            SELECT *
            FROM dp_campaigns
            WHERE (name = $1 OR type = $1)
              AND site_id = $2
            LIMIT 1
            """,
            campaign_name,
            site_id,
        )
        
        if not row:
            return None
        
        campaign = dict(row)
        
        if sandbox_mode:
            if campaign.get("status") == "archived":
                return None
            return campaign
        
        status = campaign.get("status", "")
        if status not in ("running", "active"):
            return None
        
        date_from = campaign.get("date_from")
        date_to = campaign.get("date_to")
        
        if date_from and date_from > check_date:
            return None
        if date_to and date_to < check_date:
            return None
        
        return campaign
    
    finally:
        await release_conn(conn)


async def list_active_campaigns(
    site_id: str = "lincoln_audi",
    asset_type: Optional[str] = None,
) -> list[dict]:
    """
    List all campaigns that are currently active by production criteria.
    
    Args:
        site_id: The site ID to filter by
        asset_type: Optional filter for campaign type
    
    Returns:
        List of active campaign dicts
    """
    today = date.today()
    conn = await get_conn()
    try:
        if asset_type:
            rows = await conn.fetch(
                """
                SELECT name, type, status, date_from, date_to, target_view, initial_template_key
                FROM dp_campaigns
                WHERE site_id = $1
                  AND type = $2
                  AND status IN ('running', 'active')
                  AND (date_from IS NULL OR date_from <= $3)
                  AND (date_to IS NULL OR date_to >= $3)
                ORDER BY name
                """,
                site_id,
                asset_type,
                today,
            )
        else:
            rows = await conn.fetch(
                """
                SELECT name, type, status, date_from, date_to, target_view, initial_template_key
                FROM dp_campaigns
                WHERE site_id = $1
                  AND status IN ('running', 'active')
                  AND (date_from IS NULL OR date_from <= $2)
                  AND (date_to IS NULL OR date_to >= $2)
                ORDER BY name
                """,
                site_id,
                today,
            )
        
        return [
            {
                "campaign": r["name"],
                "type": r["type"],
                "status": r["status"],
                "date_from": str(r["date_from"]) if r["date_from"] else None,
                "date_to": str(r["date_to"]) if r["date_to"] else None,
                "target_view": r["target_view"],
                "initial_template_key": r["initial_template_key"],
            }
            for r in rows
        ]
    finally:
        await release_conn(conn)


async def check_dnc_status(conn, customer_id: uuid.UUID, channel: str) -> bool:
    """
    Check if customer has any conversation with status='dnc' for this channel.
    Returns True if DNC is active (should be blocked), False if OK to proceed.
    """
    row = await conn.fetchrow(
        """
        SELECT 1 FROM dp_conversations
        WHERE customer_id = $1
          AND channel = $2
          AND status = 'dnc'
        LIMIT 1
        """,
        customer_id,
        channel,
    )
    return row is not None


async def process_outbound_message(payload: OutboundRequest) -> OutboundResponse:
    """
    Process an outbound campaign message request from Make.com.
    
    This endpoint:
    1. Checks for a valid campaign matching the request (campaign-safety gate)
    2. Determines campaign stage from payments_remaining
    3. Looks up customer details
    4. Finds or creates a conversation in dp_conversations
    5. Logs the outbound message in dp_messages
    6. Returns message_text, conversation_id, to_number, stage
    
    It does NOT send the message - Make.com handles the actual sending via Twilio.
    
    Campaign validation modes:
    - Sandbox mode: Accepts campaign if row exists and status != 'archived'
    - Production mode: Requires status in ('running', 'active') AND valid date range
    
    Raises:
        CampaignNotActiveError: If no active campaign matches (production mode)
        CampaignNotFoundError: If campaign not found (sandbox mode)
        DNCBlockedError: If customer has DNC status
    """
    DEALER_ID = "451dbf4d-ff8b-4f45-9ace-0405cf73aaff"
    LIA_FROM_NUMBER = "+447700000000"
    
    # -------------------------------------------------------------------------
    # CAMPAIGN-SAFETY CHECK: Verify there is a valid campaign for this request
    # -------------------------------------------------------------------------
    # Sandbox mode (sandbox_mode=True):
    #   - Campaign row must exist (matched by name OR type)
    #   - status != 'archived' (allows 'active', 'running', 'paused', etc.)
    #   - Date range checks are SKIPPED
    # 
    # Production mode (sandbox_mode=False):
    #   - Campaign row must exist (matched by name OR type)
    #   - status must be 'running' or 'active'
    #   - date_from <= today (or NULL)
    #   - date_to >= today (or NULL)
    # -------------------------------------------------------------------------
    active_campaign = await fetch_campaign_for_outbound(
        campaign_name=payload.campaign,
        sandbox_mode=payload.sandbox_mode,
        site_id="lincoln_audi",
        check_date=date.today(),
    )
    
    if not active_campaign:
        if payload.sandbox_mode:
            raise CampaignNotFoundError(f"Campaign '{payload.campaign}' not found for this site")
        else:
            raise CampaignNotActiveError(f"Campaign not active for this request")
    
    # -------------------------------------------------------------------------
    # TASK 1: VALIDATE PAYMENTS_REMAINING BAND
    # -------------------------------------------------------------------------
    # Only allow specific payment bands for outbound campaigns: 18, 12, 9, 6, 3
    # This ensures we only reach out at defined touchpoints in the customer journey.
    # -------------------------------------------------------------------------
    if payload.payments_remaining not in VALID_PAYMENT_BANDS:
        raise InvalidPaymentsBandError(
            f"payments_remaining must be one of {list(VALID_PAYMENT_BANDS)} for this endpoint"
        )
    
    # Campaign is active - proceed with outbound message generation
    conn = await get_conn()
    try:
        # -------------------------------------------------------------------------
        # STAGE DERIVATION: Always derive stage from payments_remaining
        # -------------------------------------------------------------------------
        # The 'campaign' field is ONLY for the safety gate (dp_campaigns lookup).
        # Stage must ALWAYS be derived from payments_remaining → "18", "12", "9", "6", "3"
        # This ensures template lookups use canonical keys like EOT_12_NEW, EOT_12_USED
        # -------------------------------------------------------------------------
        stage = select_outbound_stage(payload.payments_remaining)
        if not stage:
            return OutboundResponse(
                message_text="Error: Unable to determine campaign stage from payments_remaining",
                conversation_id=None,
                to_number=None,
                stage=None,
            )
        
        # -------------------------------------------------------------------------
        # ASSET TYPE NORMALIZATION: Must be "new" or "used"
        # -------------------------------------------------------------------------
        # PCP/HP are finance product types (used in campaign/dp_campaigns), NOT asset types.
        # asset_type controls which template branch: EOT_XX_NEW vs EOT_XX_USED
        # -------------------------------------------------------------------------
        raw_asset_type = (payload.asset_type or "new").lower().strip()
        
        # Normalize: only accept "new" or "used"; default to "new" if invalid
        if raw_asset_type in ("new", "used"):
            asset_type = raw_asset_type
        else:
            # Log warning and default to "new" for invalid values like "PCP", "HP"
            print(f"⚠️ Outbound: Invalid asset_type '{payload.asset_type}' - defaulting to 'new'")
            asset_type = "new"
        
        message_template, template_key = select_outbound_template(stage, asset_type)
        
        if not message_template:
            return OutboundResponse(
                message_text=f"Error: No template found for stage {stage} and asset_type {asset_type}",
                conversation_id=None,
                to_number=None,
                stage=stage,
            )
        
        customer_name = None
        phone_number = None
        customer_uuid = None
        vehicle_model = None
        
        if payload.customer_id:
            # Validate customer_id is a valid UUID format
            try:
                parsed_customer_id = validate_uuid(payload.customer_id)
            except InvalidCustomerIdError:
                raise InvalidCustomerIdError(
                    f"customer_id '{payload.customer_id}' is not a valid UUID format. "
                    f"Expected format: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
                )
            
            row = await conn.fetchrow(
                """
                SELECT dc.customer_id, dc.first_name, dc.last_name, dc.phone_mobile,
                       v.model as vehicle_model
                FROM dp_customer dc
                LEFT JOIN v_retention_book_current vr ON vr.customer_id = dc.customer_id
                LEFT JOIN vehicle v ON v.registration = vr.reg
                WHERE dc.customer_id = $1
                """,
                parsed_customer_id,
            )
            if row:
                customer_uuid = row["customer_id"]
                first_name = row["first_name"] or ""
                last_name = row["last_name"] or ""
                customer_name = first_name.strip() or "there"
                phone_number = row["phone_mobile"]
                vehicle_model = row["vehicle_model"] or "Audi"
        elif payload.reg:
            row = await conn.fetchrow(
                """
                SELECT c.customer_id, c.first_name, c.last_name, c.phone_mobile, v.model as vehicle_model
                FROM vehicle v
                JOIN agreement a ON a.vehicle_id = v.id
                JOIN customer c ON c.id = a.customer_id
                WHERE v.registration = $1
                LIMIT 1
                """,
                payload.reg,
            )
            if row:
                customer_uuid = row["customer_id"]
                first_name = row["first_name"] or ""
                last_name = row["last_name"] or ""
                customer_name = first_name.strip() or "there"
                phone_number = row["phone_mobile"]
                vehicle_model = row["vehicle_model"] or "Audi"
        
        if not customer_name:
            customer_name = "there"
        
        if not vehicle_model:
            vehicle_model = "Audi"
        
        if not customer_uuid:
            return OutboundResponse(
                message_text="Error: Customer not found",
                conversation_id=None,
                to_number=None,
                stage=stage,
            )
        
        is_dnc = await check_dnc_status(conn, customer_uuid, payload.channel)
        if is_dnc:
            raise DNCBlockedError(f"Customer has DNC status for {payload.channel}")
        
        # -------------------------------------------------------------------------
        # TASK 2: DUPLICATE CONVERSATION CHECK
        # -------------------------------------------------------------------------
        # Prevent creating a new conversation if one already exists for this
        # customer + campaign + stage + channel combination.
        # This runs regardless of sandbox_mode. Any existing row blocks new creation.
        # -------------------------------------------------------------------------
        existing = await conn.fetchrow(
            """
            SELECT conversation_id, status
            FROM dp_conversations
            WHERE customer_id = $1
              AND campaign    = $2
              AND stage       = $3
              AND channel     = $4
            LIMIT 1
            """,
            customer_uuid,
            payload.campaign,
            stage,
            payload.channel,
        )
        
        if existing:
            raise DuplicateConversationError(
                message="Conversation already exists for this customer, campaign, stage and channel",
                conversation_id=str(existing["conversation_id"]),
                stage=stage,
            )
        
        # -------------------------------------------------------------------------
        # PHONE NUMBER VALIDATION: Required for outbound messaging
        # -------------------------------------------------------------------------
        # phone_mobile from dp_customer is required for Make/Twilio to send the message.
        # Format: "whatsapp:" + phone_mobile (e.g., "whatsapp:+447972194025")
        # This applies to BOTH sandbox_mode and production for consistent wiring.
        # -------------------------------------------------------------------------
        if not phone_number or not phone_number.strip():
            raise MissingPhoneNumberError(
                f"No mobile phone number found for customer_id '{payload.customer_id}' in dp_customer"
            )
        
        # Format to_number for WhatsApp channel
        if payload.channel == "whatsapp":
            # Ensure phone_number starts with +, then add whatsapp: prefix
            clean_phone = phone_number.strip()
            if not clean_phone.startswith("+"):
                clean_phone = "+" + clean_phone
            to_number = f"whatsapp:{clean_phone}"
        else:
            to_number = phone_number.strip()
        
        message_text = message_template.replace("[customer_name]", customer_name)
        message_text = message_text.replace("[vehicle_model]", vehicle_model)
        
        from_number = payload.from_number or LIA_FROM_NUMBER
        
        conversation_id = await find_or_create_conversation(
            conn=conn,
            customer_id=customer_uuid,
            channel=payload.channel,
            campaign=payload.campaign,
            dealer_id=DEALER_ID,
            from_number=from_number,
            to_number=to_number,
            stage=stage,
        )
        
        await log_outbound_message(
            conn=conn,
            conversation_id=conversation_id,
            channel=payload.channel,
            message_body=message_text,
            template_key=template_key,
            mode="outbound_campaign",
        )
        
        return OutboundResponse(
            message_text=message_text,
            conversation_id=str(conversation_id),
            to_number=to_number,
            stage=stage,
        )
    
    finally:
        await release_conn(conn)