#!/usr/bin/env python3
"""
Campaign Subset Importer
Imports pre-filtered campaign customers into Supabase campaign_batch and campaign_batch_item.
"""
import argparse
import csv
import sys
import os
from typing import Dict, Set, List, Optional
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from utils.tools import get_client


def ensure_campaign_batch(tag: str, summary: str) -> int:
    """
    Ensure campaign_batch record exists (upsert).
    
    Args:
        tag: Campaign batch tag (e.g., 'A5-Upgrade-Nov-Wk2')
        summary: Manager's one-line brief for Lia
    
    Returns:
        The batch_id (campaign_batch.id)
    """
    client = get_client()
    
    # Check if batch exists
    result = client.table('campaign_batch') \
        .select('id') \
        .eq('tag', tag) \
        .execute()
    
    if result.data and len(result.data) > 0:
        batch_id = result.data[0]['id']
        # Update summary if batch exists
        client.table('campaign_batch') \
            .update({'summary': summary}) \
            .eq('id', batch_id) \
            .execute()
        print(f"✅ Updated existing campaign_batch: {tag} (id={batch_id})")
        return batch_id
    
    # Create new batch
    insert_result = client.table('campaign_batch') \
        .insert({
            'tag': tag,
            'summary': summary
        }) \
        .execute()
    
    batch_id = insert_result.data[0]['id']
    print(f"✅ Created new campaign_batch: {tag} (id={batch_id})")
    return batch_id


def resolve_agreement_numbers(agreement_numbers: List[str]) -> Dict[str, Dict[str, any]]:
    """
    Resolve agreement numbers to agreement IDs in bulk.
    
    Args:
        agreement_numbers: List of agreement numbers to resolve
    
    Returns:
        Dict mapping agreement_number -> {id, agreement_number}
    """
    client = get_client()
    
    # Fetch all matching agreements in one query
    result = client.table('agreement') \
        .select('id, agreement_number') \
        .in_('agreement_number', agreement_numbers) \
        .execute()
    
    # Build lookup dict
    lookup = {}
    for row in result.data:
        lookup[row['agreement_number']] = {
            'id': row['id'],
            'agreement_number': row['agreement_number']
        }
    
    return lookup


def get_existing_batch_items(batch_id: int) -> Set[int]:
    """
    Get existing agreement IDs already in this batch.
    
    Args:
        batch_id: The campaign_batch ID
    
    Returns:
        Set of agreement IDs already in batch
    """
    client = get_client()
    
    result = client.table('campaign_batch_item') \
        .select('agreement_id') \
        .eq('batch_id', batch_id) \
        .execute()
    
    return {row['agreement_id'] for row in result.data}


def insert_batch_items(batch_id: int, agreements: List[Dict[str, any]], existing: Set[int]) -> int:
    """
    Insert new batch items, skipping duplicates.
    
    Args:
        batch_id: The campaign_batch ID
        agreements: List of dicts with {id, agreement_number}
        existing: Set of existing agreement IDs in batch
    
    Returns:
        Number of rows inserted
    """
    client = get_client()
    
    # Filter out duplicates
    new_agreements = [a for a in agreements if a['id'] not in existing]
    
    if not new_agreements:
        return 0
    
    # Build insert records
    records = [{
        'batch_id': batch_id,
        'agreement_id': a['id'],
        'agreement_number': a['agreement_number']
    } for a in new_agreements]
    
    # Batch insert
    client.table('campaign_batch_item') \
        .insert(records) \
        .execute()
    
    return len(records)


def run(tag: str, summary: str, file_path: str) -> None:
    """
    Main import logic.
    
    Args:
        tag: Campaign batch tag
        summary: Campaign summary for Lia
        file_path: Path to CSV file
    """
    print("=" * 80)
    print("📥 CAMPAIGN SUBSET IMPORTER")
    print("=" * 80)
    print(f"Tag: {tag}")
    print(f"Summary: {summary}")
    print(f"File: {file_path}")
    print()
    
    # Step 1: Ensure campaign_batch exists
    batch_id = ensure_campaign_batch(tag, summary)
    
    # Step 2: Read CSV and extract agreement_numbers
    print(f"\n📋 Reading CSV file...")
    agreement_numbers = []
    
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            reader = csv.DictReader(f)
            for row in reader:
                agreement_num = row.get('agreement_number', '').strip()
                if agreement_num:
                    agreement_numbers.append(agreement_num)
    except FileNotFoundError:
        print(f"❌ File not found: {file_path}")
        sys.exit(1)
    except Exception as e:
        print(f"❌ Error reading CSV: {e}")
        sys.exit(1)
    
    total_rows = len(agreement_numbers)
    print(f"   Found {total_rows} row(s) with agreement_number")
    
    if total_rows == 0:
        print("   ℹ️  No data to import")
        print("=" * 80)
        return
    
    # Step 3: Resolve agreement_numbers to IDs
    print(f"\n🔍 Resolving agreement_numbers to agreement IDs...")
    lookup = resolve_agreement_numbers(agreement_numbers)
    
    matched = len(lookup)
    unmatched = total_rows - matched
    
    print(f"   Matched: {matched}")
    print(f"   Unmatched: {unmatched}")
    
    if matched == 0:
        print("   ⚠️  No matching agreements found")
        print("=" * 80)
        return
    
    # Step 4: Get existing batch items
    print(f"\n🔄 Checking for existing batch items...")
    existing = get_existing_batch_items(batch_id)
    print(f"   Already in batch: {len(existing)}")
    
    # Step 5: Insert new batch items
    print(f"\n📤 Inserting new batch items...")
    agreements = list(lookup.values())
    inserted = insert_batch_items(batch_id, agreements, existing)
    
    deduped = matched - inserted
    print(f"   Inserted: {inserted}")
    print(f"   Skipped (duplicates): {deduped}")
    
    # Summary
    print("\n" + "=" * 80)
    print(f"✨ SUMMARY: Matched {matched} agreements, {unmatched} unmatched, "
          f"{inserted} inserted (deduped)")
    print("=" * 80)


def main():
    """CLI entry point."""
    parser = argparse.ArgumentParser(
        description='Import pre-filtered campaign customers into Supabase'
    )
    parser.add_argument(
        '--tag',
        required=True,
        help='Campaign batch tag (e.g., A5-Upgrade-Nov-Wk2)'
    )
    parser.add_argument(
        '--summary',
        required=True,
        help="Manager's one-line campaign brief for Lia"
    )
    parser.add_argument(
        '--file',
        required=True,
        help='Path to CSV file containing pre-filtered customers'
    )
    
    args = parser.parse_args()
    
    try:
        run(args.tag, args.summary, args.file)
    except Exception as e:
        print(f"\n❌ Fatal error: {e}")
        sys.exit(1)


if __name__ == '__main__':
    main()