#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Analyze database structure to understand the schema
"""

import sqlite3
import json
import sys

def analyze_database(db_path):
    """Analyze the database structure"""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    
    print("=" * 80)
    print("DATABASE STRUCTURE ANALYSIS")
    print("=" * 80)
    
    # Get all tables
    print("\n1. TABLES:")
    print("-" * 80)
    tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()
    for table in tables:
        print(f"  - {table[0]}")
    
    # Analyze users table in detail
    print("\n2. USERS TABLE STRUCTURE:")
    print("-" * 80)
    try:
        columns = cursor.execute("PRAGMA table_info(users)").fetchall()
        print(f"  Total columns: {len(columns)}")
        print("\n  Columns:")
        for col in columns:
            print(f"    - {col[1]} ({col[2]}) - Nullable: {not col[3]}, Default: {col[4]}")
        
        # Check if display_name exists
        has_display_name = any(col[1] == 'display_name' for col in columns)
        print(f"\n  Has 'display_name' column: {has_display_name}")
        
        # Get sample data
        print("\n3. SAMPLE DATA FROM USERS TABLE:")
        print("-" * 80)
        sample = cursor.execute("SELECT * FROM users LIMIT 3").fetchall()
        if sample:
            for i, row in enumerate(sample, 1):
                print(f"\n  Row {i}:")
                for key in row.keys():
                    value = row[key]
                    if value is None:
                        value = "NULL"
                    elif isinstance(value, str) and len(value) > 50:
                        value = value[:50] + "..."
                    print(f"    {key}: {value}")
        else:
            print("  No data in users table")
            
        # Count users
        count = cursor.execute("SELECT COUNT(*) FROM users").fetchone()[0]
        print(f"\n  Total users: {count}")
        
    except Exception as e:
        print(f"  Error analyzing users table: {e}")
    
    # Check persons table for owner references
    print("\n4. PERSONS TABLE - OWNER REFERENCES:")
    print("-" * 80)
    try:
        persons_columns = cursor.execute("PRAGMA table_info(persons)").fetchall()
        has_owner = any(col[1] == 'owner_didar_id' for col in persons_columns)
        print(f"  Has 'owner_didar_id' column: {has_owner}")
        
        if has_owner:
            # Count persons with owners
            with_owner = cursor.execute("SELECT COUNT(*) FROM persons WHERE owner_didar_id IS NOT NULL").fetchone()[0]
            total = cursor.execute("SELECT COUNT(*) FROM persons").fetchone()[0]
            print(f"  Persons with owner: {with_owner} / {total}")
            
            # Sample owner IDs
            sample_owners = cursor.execute("SELECT DISTINCT owner_didar_id FROM persons WHERE owner_didar_id IS NOT NULL LIMIT 5").fetchall()
            print(f"\n  Sample owner_didar_id values:")
            for owner in sample_owners:
                print(f"    - {owner[0]}")
                
    except Exception as e:
        print(f"  Error analyzing persons table: {e}")
    
    # Check all queries that might use display_name
    print("\n5. CHECKING WHERE display_name MIGHT BE USED:")
    print("-" * 80)
    print("  Checking if any users have data that could be display_name...")
    
    try:
        # Check first_name + last_name combinations
        users_with_names = cursor.execute("""
            SELECT didar_user_id, first_name, last_name 
            FROM users 
            WHERE first_name IS NOT NULL OR last_name IS NOT NULL 
            LIMIT 5
        """).fetchall()
        
        print("\n  Sample users (first_name + last_name):")
        for user in users_with_names:
            full_name = f"{user[1] or ''} {user[2] or ''}".strip()
            print(f"    - {user[0]}: '{full_name}'")
            
    except Exception as e:
        print(f"  Error: {e}")
    
    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_database(db_path)
    except Exception as e:
        print(f"Error: {e}")
        import traceback
        traceback.print_exc()