"""
Helper module for Supabase operations.
Provides simple functions for Android/external integrations.
"""
import os
import re
from typing import Optional, Dict, Any
from supabase import create_client, Client
from datetime import datetime


def get_client() -> Client:
    """
    Returns a Supabase client using environment variables.
    
    Raises:
        ValueError: If SUPABASE_URL or SUPABASE_SERVICE_KEY is not set.
    """
    supabase_url = os.getenv('SUPABASE_URL')
    supabase_key = os.getenv('SUPABASE_SERVICE_KEY')
    
    if not supabase_url or not supabase_key:
        raise ValueError("SUPABASE_URL and SUPABASE_SERVICE_KEY environment variables must be set")
    
    return create_client(supabase_url, supabase_key)


def resolve_agreement_id(agreement_number: str) -> Optional[int]:
    """
    Looks up agreement.id by agreement_number.
    
    Args:
        agreement_number: The agreement number to look up.
    
    Returns:
        The agreement ID, or None if not found.
    """
    client = get_client()
    result = client.table('agreement').select('id').eq('agreement_number', agreement_number).execute()
    
    if result.data and len(result.data) > 0:
        return result.data[0]['id']
    return None


def _resolve_agreement_id(agreement_ref) -> Optional[int]:
    """
    Internal helper to resolve agreement_ref to agreement_id.
    
    Args:
        agreement_ref: Either agreement_id (int) or agreement_number (str)
    
    Returns:
        The agreement ID, or None if not found
    """
    if isinstance(agreement_ref, int):
        return agreement_ref
    return resolve_agreement_id(agreement_ref)


def log_message(
    agreement_ref,
    channel: str,
    direction: str,
    message_text: str = None,
    intent_label: str = None,
    template_slug: str = None,
    source: str = "ai",
    model: str = None,
    happened_at=None
) -> Dict[str, Any]:
    """
    Inserts a message into contact_history with full metadata.
    
    Args:
        agreement_ref: Agreement number (str) or agreement_id (int)
        channel: Communication channel (whatsapp, sms, email)
        direction: Message direction (inbound, outbound)
        message_text: The actual message text (optional)
        intent_label: Intent classification (e.g., 'not_ready_yet', 'openai_fallback')
        template_slug: Template used if rule-based (optional)
        source: Message source (default: 'ai')
        model: AI model used if applicable (e.g., 'gpt-4o-mini')
        happened_at: Timestamp (ISO format), defaults to now() UTC
    
    Returns:
        The inserted row with all fields
    
    Raises:
        ValueError: If agreement_ref cannot be resolved
    """
    agreement_id = _resolve_agreement_id(agreement_ref)
    if agreement_id is None:
        raise ValueError(f"Agreement reference '{agreement_ref}' not found")
    
    client = get_client()
    
    # Build insert data with all fields
    insert_data = {
        'agreement_id': agreement_id,
        'channel': channel,
        'direction': direction,
        'source': source
    }
    
    # Add optional fields only if provided
    if message_text is not None:
        insert_data['message_text'] = message_text
    if intent_label is not None:
        insert_data['intent_label'] = intent_label
    if template_slug is not None:
        insert_data['template_slug'] = template_slug
    if model is not None:
        insert_data['model'] = model
    if happened_at is not None:
        insert_data['happened_at'] = happened_at
    
    result = client.table('contact_history').insert(insert_data).execute()
    return result.data[0] if result.data else {}


def open_thread(agreement_number: str, thread_type: str = 'renewal') -> Dict[str, Any]:
    """
    Upserts an open_thread record.
    
    Args:
        agreement_number: The agreement number.
        thread_type: Type of thread (default: 'renewal').
    
    Returns:
        The open_thread row (id, agreement_number, status, created_at).
    """
    client = get_client()
    
    # Check if an open thread exists
    existing = client.table('open_thread') \
        .select('*') \
        .eq('agreement_number', agreement_number) \
        .eq('status', 'open') \
        .execute()
    
    if existing.data and len(existing.data) > 0:
        return existing.data[0]
    
    # Create new open thread
    insert_data = {
        'agreement_number': agreement_number,
        'thread_type': thread_type,
        'status': 'open'
    }
    
    result = client.table('open_thread').insert(insert_data).execute()
    return result.data[0] if result.data else {}


def close_thread(agreement_number: str) -> Optional[Dict[str, Any]]:
    """
    Closes the most recent open thread for an agreement.
    
    Args:
        agreement_number: The agreement number.
    
    Returns:
        The updated row, or None if no open thread found.
    """
    client = get_client()
    
    # Find most recent open thread
    existing = client.table('open_thread') \
        .select('*') \
        .eq('agreement_number', agreement_number) \
        .eq('status', 'open') \
        .order('created_at', desc=True) \
        .limit(1) \
        .execute()
    
    if not existing.data or len(existing.data) == 0:
        return None
    
    thread_id = existing.data[0]['id']
    
    # Update to closed
    update_data = {
        'status': 'closed',
        'updated_at': datetime.utcnow().isoformat()
    }
    
    result = client.table('open_thread') \
        .update(update_data) \
        .eq('id', thread_id) \
        .execute()
    
    return result.data[0] if result.data else None


def schedule_followup(agreement_number: str, due_at_iso: str, reason: str) -> Dict[str, Any]:
    """
    Schedules a follow-up task.
    
    Args:
        agreement_number: The agreement number.
        due_at_iso: Due date/time in ISO format.
        reason: Reason for follow-up.
    
    Returns:
        The inserted row (id, agreement_id, due_at, reason).
    
    Raises:
        ValueError: If agreement_number not found.
    """
    agreement_id = resolve_agreement_id(agreement_number)
    if agreement_id is None:
        raise ValueError(f"Agreement number '{agreement_number}' not found")
    
    client = get_client()
    insert_data = {
        'agreement_id': agreement_id,
        'due_at': due_at_iso,
        'reason': reason
    }
    
    result = client.table('follow_up').insert(insert_data).execute()
    return result.data[0] if result.data else {}


def book_appt_request(
    agreement_number: str,
    requested_text: Optional[str] = None,
    notes: Optional[str] = None,
    channel: str = 'whatsapp',
    requested_by: str = 'ai'
) -> Dict[str, Any]:
    """
    Creates an appointment request with safe date/time parsing.
    
    Args:
        agreement_number: The agreement number.
        requested_text: Raw customer phrase (e.g., "Friday 10:30", "next week").
        notes: Additional notes.
        channel: Communication channel (default: 'whatsapp').
        requested_by: Who requested the appointment (default: 'ai').
    
    Returns:
        The inserted row (id, agreement_id, status, created_at, parsed info).
    
    Raises:
        ValueError: If agreement_number not found.
    """
    from deferral_parser import parse_appointment_when
    
    agreement_id = resolve_agreement_id(agreement_number)
    if agreement_id is None:
        raise ValueError(f"Agreement number '{agreement_number}' not found")
    
    client = get_client()
    
    # Parse the requested_text if provided
    preferred_date = None
    preferred_time = None
    parsed_ok = False
    
    if requested_text:
        parsed = parse_appointment_when(requested_text)
        preferred_date = parsed.get('date')
        preferred_time = parsed.get('time')
        parsed_ok = parsed.get('parsed', False)
    
    # Insert appointment request
    insert_data = {
        'agreement_id': agreement_id,
        'requested_by': requested_by,
        'channel': channel,
        'requested_text': requested_text,
        'preferred_date': preferred_date,
        'preferred_time': preferred_time,
        'notes': notes
    }
    
    result = client.table('appointment_request').insert(insert_data).execute()
    appt = result.data[0] if result.data else {}
    
    # Create manager_task if parsing failed
    if requested_text and not parsed_ok:
        task_data = {
            'agreement_id': agreement_id,
            'task_type': 'confirm_appointment',
            'title': 'Confirm appointment time',
            'description': f'Customer requested: "{requested_text}"\nNeed to confirm specific date/time.',
            'priority': 'high',
            'status': 'pending'
        }
        client.table('manager_task').insert(task_data).execute()
    
    return appt


def get_equity_snapshot(agreement_number: str) -> Optional[Dict[str, Any]]:
    """
    Gets the latest equity snapshot for an agreement.
    
    Args:
        agreement_number: The agreement number.
    
    Returns:
        Dict with equity data, or None if not found.
    """
    agreement_id = resolve_agreement_id(agreement_number)
    if agreement_id is None:
        return None
    
    client = get_client()
    result = client.table('v_equity_latest') \
        .select('*') \
        .eq('agreement_id', agreement_id) \
        .execute()
    
    if result.data and len(result.data) > 0:
        return result.data[0]
    return None


def sanitize_internal_codes(text: str) -> str:
    """
    Remove internal campaign codes and technical identifiers from text.
    
    This prevents "mail-merge" feel by stripping:
    - Campaign tags like "A5-Upgrade-Nov-Wk2", "Journey-renewal_12-20251102"
    - Code-like patterns with multiple hyphens or underscores
    - Date fragments like "20251102"
    
    Args:
        text: Message text that may contain internal codes
    
    Returns:
        Cleaned text with internal codes removed
    
    Examples:
        >>> sanitize_internal_codes("Check out A5-Upgrade-Nov-Wk2 offer")
        "Check out  offer"
        >>> sanitize_internal_codes("Journey-renewal_12-20251102 looks great!")
        " looks great!"
    """
    if not text:
        return text
    
    cleaned = text
    
    # Pattern 1: Words with 2+ hyphens or underscores (campaign tags)
    # Matches: A5-Upgrade-Nov-Wk2, Journey-renewal_12-20251102
    cleaned = re.sub(r'\b[\w]+-[\w]+-[\w-]+\b', '', cleaned)
    cleaned = re.sub(r'\b[\w]+_[\w]+_[\w_]+\b', '', cleaned)
    
    # Pattern 2: Date-like sequences (8 digits in a row)
    # Matches: 20251102
    cleaned = re.sub(r'\b\d{8}\b', '', cleaned)
    
    # Pattern 3: Week labels like "Wk2", "Week3"
    cleaned = re.sub(r'\b[Ww]k?\d+\b', '', cleaned)
    
    # Clean up extra whitespace that may have been created
    cleaned = re.sub(r'\s+', ' ', cleaned)
    cleaned = cleaned.strip()
    
    return cleaned