#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Analyze virtual_stages table to understand the stage transition issue
"""

import sqlite3
import json
import sys
from datetime import datetime

def analyze_virtual_stages(db_path):
    """Analyze virtual_stages table"""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    print("=" * 80)
    print("VIRTUAL STAGES ANALYSIS")
    print("=" * 80)
    
    # 1. Get all virtual stages
    print("\n1. ALL VIRTUAL STAGES:")
    print("-" * 80)
    stages = cursor.execute("""
        SELECT contact_didar_id, stage_name, entered_at 
        FROM virtual_stages 
        ORDER BY contact_didar_id, entered_at DESC
    """).fetchall()
    
    if not stages:
        print("  No virtual stages found in database")
    else:
        print(f"  Total virtual stage records: {len(stages)}")
        
        # Group by contact
        contacts = {}
        for stage in stages:
            contact_id = stage['contact_didar_id']
            if contact_id not in contacts:
                contacts[contact_id] = []
            contacts[contact_id].append({
                'stage_name': stage['stage_name'],
                'entered_at': stage['entered_at']
            })
        
        print(f"  Unique contacts with stages: {len(contacts)}")
        
        # Show contacts with special stages
        print("\n2. CONTACTS WITH SPECIAL STAGES (with_purchase, without_purchase, refer_crm):")
        print("-" * 80)
        special_stages = ['with_purchase', 'without_purchase', 'refer_crm']
        contacts_with_special = {}
        
        for contact_id, stage_list in contacts.items():
            has_special = any(s['stage_name'] in special_stages for s in stage_list)
            if has_special:
                contacts_with_special[contact_id] = stage_list
        
        print(f"  Contacts with special stages: {len(contacts_with_special)}")
        
        # Show sample contacts
        sample_count = 0
        for contact_id, stage_list in list(contacts_with_special.items())[:5]:
            print(f"\n  Contact ID: {contact_id}")
            for stage in stage_list:
                print(f"    - {stage['stage_name']} (entered: {stage['entered_at']})")
            sample_count += 1
        
        if len(contacts_with_special) > sample_count:
            print(f"\n  ... and {len(contacts_with_special) - sample_count} more contacts")
        
        # 3. Check contacts that should be able to transition to "new"
        print("\n3. CONTACTS THAT CANNOT TRANSITION TO 'new':")
        print("-" * 80)
        print("  (Contacts with special stages that prevent 'new' stage)")
        
        blocked_contacts = []
        for contact_id, stage_list in contacts_with_special.items():
            # Get most recent stage
            most_recent = stage_list[0] if stage_list else None
            if most_recent and most_recent['stage_name'] in special_stages:
                blocked_contacts.append({
                    'contact_id': contact_id,
                    'current_stage': most_recent['stage_name'],
                    'all_stages': stage_list
                })
        
        print(f"  Total blocked contacts: {len(blocked_contacts)}")
        
        # 4. Check activities for these contacts
        print("\n4. RECENT ACTIVITIES FOR BLOCKED CONTACTS:")
        print("-" * 80)
        
        if blocked_contacts:
            sample_contact = blocked_contacts[0]
            contact_id = sample_contact['contact_id']
            
            activities = cursor.execute("""
                SELECT didar_activity_id, activity_type_title, title, result_note, 
                       stage, register_date 
                FROM activities 
                WHERE contact_didar_id = ? 
                ORDER BY register_date DESC 
                LIMIT 5
            """, (contact_id,)).fetchall()
            
            print(f"  Sample contact: {contact_id}")
            print(f"  Current stage: {sample_contact['current_stage']}")
            print(f"  Recent activities:")
            
            for act in activities:
                print(f"    - {act['activity_type_title']} ({act['register_date']})")
                print(f"      Stage in activity: {act['stage']}")
                if act['result_note']:
                    note_preview = act['result_note'][:100] + "..." if len(act['result_note']) > 100 else act['result_note']
                    print(f"      Note: {note_preview}")
    
    # 5. Check persons table for has_previous_purchase
    print("\n5. PERSONS WITH has_previous_purchase:")
    print("-" * 80)
    try:
        persons_with_purchase = cursor.execute("""
            SELECT didar_contact_id, first_name, last_name, has_previous_purchase,
                   (SELECT stage_name FROM virtual_stages 
                    WHERE contact_didar_id = persons.didar_contact_id 
                    ORDER BY entered_at DESC LIMIT 1) as current_stage
            FROM persons 
            WHERE has_previous_purchase = 1 
            LIMIT 5
        """).fetchall()
        
        print(f"  Sample persons with previous purchase:")
        for person in persons_with_purchase:
            name = f"{person['first_name'] or ''} {person['last_name'] or ''}".strip()
            print(f"    - {person['didar_contact_id']}: {name}")
            print(f"      has_previous_purchase: {person['has_previous_purchase']}")
            print(f"      current_stage: {person['current_stage']}")
    except Exception as e:
        print(f"  Error: {e}")
    
    # 6. Summary
    print("\n6. SUMMARY:")
    print("-" * 80)
    print("  Issue Analysis:")
    print("  - The updateVirtualStage function prevents overwriting special stages")
    print("  - When a contact has 'with_purchase' or 'without_purchase',")
    print("    trying to set stage to 'new' will be blocked")
    print("  - This is by design to preserve special stage information")
    print("  - However, this prevents normal stage transitions when needed")
    
    conn.close()
    
    print("\n" + "=" * 80)
    print("ANALYSIS COMPLETE")
    print("=" * 80)

if __name__ == "__main__":
    db_path = "crm_database_new.db"
    if len(sys.argv) > 1:
        db_path = sys.argv[1]
    
    try:
        analyze_virtual_stages(db_path)
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()