#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
بررسی وجود و محتوای فیلد customer_products در دیتابیس
"""

import sqlite3
import sys

if sys.platform == 'win32':
    import io
    sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

DB_PATH = "crm_database_new.db"

conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()

print("=" * 60)
print("بررسی فیلد customer_products در دیتابیس")
print("=" * 60)

# بررسی وجود ستون
cursor.execute("PRAGMA table_info(persons)")
columns = [row[1] for row in cursor.fetchall()]
has_customer_products = 'customer_products' in columns
print(f"\n✅ ستون customer_products وجود دارد: {has_customer_products}")

if has_customer_products:
    # آمار
    cursor.execute("SELECT COUNT(*) FROM persons WHERE customer_products IS NOT NULL AND customer_products != ''")
    with_products = cursor.fetchone()[0]
    
    cursor.execute("SELECT COUNT(*) FROM persons")
    total = cursor.fetchone()[0]
    
    print(f"\n📊 آمار:")
    print(f"  کل اشخاص: {total}")
    print(f"  با محصولات مشتری: {with_products}")
    print(f"  بدون محصولات مشتری: {total - with_products}")
    
    # نمونه داده‌ها
    print(f"\n📋 نمونه داده‌ها (10 مورد اول با محصولات):")
    cursor.execute("""
        SELECT didar_contact_id, last_name, customer_products, has_previous_purchase 
        FROM persons 
        WHERE customer_products IS NOT NULL AND customer_products != ''
        LIMIT 10
    """)
    for row in cursor.fetchall():
        print(f"  Contact ID: {row[0]}, Name: {row[1]}, Products: '{row[2]}', Has Purchase: {row[3]}")
    
    # بررسی انواع محصولات
    print(f"\n📦 انواع محصولات منحصر به فرد:")
    cursor.execute("""
        SELECT DISTINCT customer_products, COUNT(*) as count
        FROM persons 
        WHERE customer_products IS NOT NULL AND customer_products != ''
        GROUP BY customer_products
        ORDER BY count DESC
        LIMIT 20
    """)
    for row in cursor.fetchall():
        print(f"  '{row[0]}': {row[1]} مورد")

conn.close()