#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
ساخت دیتابیس جدید از دیتابیس فعلی و فایل اکسل
- کپی کردن همه اطلاعات به جز persons
- وارد کردن persons از اکسل با mapping صحیح
- تنظیم virtual stages بر اساس "داری فروش قبلی"
"""

import sqlite3
import pandas as pd
import sys
import os
import shutil
from datetime import datetime
import re

# تنظیم encoding برای Windows
if sys.platform == 'win32':
    import io
    sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
    sys.stderr = io.TextIOWrapper(sys.stderr.buffer, encoding='utf-8')

# مسیر فایل‌ها
SOURCE_DB = "crm_database (5).db"
EXCEL_FILE = "پایگاه داده با فروش قبلی.xlsx"
NEW_DB = "crm_database_new.db"

def log_message(msg):
    """چاپ پیام با timestamp"""
    print(f"[{datetime.now().strftime('%Y-%m-%d %H:%M:%S')}] {msg}")

def remove_leading_zero(phone):
    """حذف همه صفرهای اول از شماره تلفن"""
    if not phone or pd.isna(phone):
        return None
    
    phone = str(phone).strip()
    # حذف فاصله‌ها و کاراکترهای غیر عددی
    phone = re.sub(r'[^\d]', '', phone)
    
    # حذف همه صفرهای اول
    phone = phone.lstrip('0')
    
    return phone if phone else None

def normalize_owner_name(name):
    """نرمال‌سازی نام مسئول برای matching"""
    if not name or pd.isna(name):
        return None
    return str(name).strip()

# ============================================
# مرحله 1: خواندن کاربران از دیتابیس فعلی
# ============================================

log_message("📖 خواندن کاربران از دیتابیس فعلی...")
source_conn = sqlite3.connect(SOURCE_DB)
source_cursor = source_conn.cursor()

source_cursor.execute("SELECT id, display_name, didar_user_id FROM users WHERE is_active = 1")
users_data = source_cursor.fetchall()

# ساخت mapping: display_name -> didar_user_id
user_mapping = {}
user_id_mapping = {}  # برای حفظ id اصلی
for user_id, display_name, didar_user_id in users_data:
    normalized_name = normalize_owner_name(display_name)
    if normalized_name:
        user_mapping[normalized_name] = didar_user_id
        user_id_mapping[normalized_name] = user_id

log_message(f"✅ {len(user_mapping)} کاربر فعال پیدا شد")

# ============================================
# مرحله 2: خواندن فایل اکسل
# ============================================

log_message("📊 خواندن فایل اکسل...")
try:
    df = pd.read_excel(EXCEL_FILE)
    log_message(f"✅ {len(df)} ردیف از اکسل خوانده شد")
except Exception as e:
    log_message(f"❌ خطا در خواندن اکسل: {e}")
    sys.exit(1)

# ============================================
# مرحله 3: ساخت دیتابیس جدید
# ============================================

log_message("🗄️ ساخت دیتابیس جدید...")

# حذف دیتابیس قبلی اگر وجود دارد
if os.path.exists(NEW_DB):
    os.remove(NEW_DB)
    log_message("⚠️ دیتابیس قبلی حذف شد")

new_conn = sqlite3.connect(NEW_DB)
new_cursor = new_conn.cursor()

# ============================================
# مرحله 4: کپی کردن ساختار جداول (به جز persons)
# ============================================

log_message("📋 کپی کردن ساختار جداول...")

# لیست جداول
source_cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT IN ('persons', 'sqlite_sequence')")
tables_to_copy = [row[0] for row in source_cursor.fetchall()]

for table_name in tables_to_copy:
    log_message(f"  📋 کپی کردن جدول: {table_name}")
    
    # دریافت ساختار جدول
    source_cursor.execute(f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{table_name}'")
    create_sql = source_cursor.fetchone()
    
    if create_sql:
        new_cursor.execute(create_sql[0])
        log_message(f"    ✅ ساختار {table_name} کپی شد")

# ============================================
# مرحله 5: ساخت جدول persons با فیلد جدید
# ============================================

log_message("📋 ساخت جدول persons با فیلد محصولات مشتری...")

persons_create_sql = """CREATE TABLE IF NOT EXISTS persons (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    didar_contact_id TEXT UNIQUE NOT NULL,
    owner_didar_id TEXT,
    code TEXT,
    first_name TEXT,
    last_name TEXT,
    mobile_phone TEXT,
    secondary_mobile_phone TEXT,
    work_phone TEXT,
    email TEXT,
    contact_type TEXT,
    city TEXT,
    job_title TEXT,
    acquaintance_source TEXT,
    acquaintance_detail TEXT,
    content_topic TEXT,
    national_id TEXT,
    has_previous_purchase INTEGER DEFAULT 0,
    birth_date DATE,
    register_time DATETIME,
    visibility_type TEXT,
    background_info TEXT,
    customer_products TEXT,
    last_sync DATETIME,
    raw_json TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)"""

new_cursor.execute(persons_create_sql)
log_message("✅ جدول persons با فیلد customer_products ساخته شد")

# ============================================
# مرحله 6: کپی کردن داده‌های جداول دیگر
# ============================================

log_message("📥 کپی کردن داده‌های جداول...")

for table_name in tables_to_copy:
    log_message(f"  📥 کپی کردن داده‌های {table_name}...")
    
    # دریافت ستون‌ها
    source_cursor.execute(f"PRAGMA table_info({table_name})")
    columns = [row[1] for row in source_cursor.fetchall()]
    columns_str = ', '.join(columns)
    placeholders = ', '.join(['?' for _ in columns])
    
    # کپی داده‌ها
    source_cursor.execute(f"SELECT {columns_str} FROM {table_name}")
    rows = source_cursor.fetchall()
    
    new_cursor.executemany(f"INSERT INTO {table_name} ({columns_str}) VALUES ({placeholders})", rows)
    log_message(f"    ✅ {len(rows)} ردیف از {table_name} کپی شد")

new_conn.commit()

# ============================================
# مرحله 7: وارد کردن persons از اکسل
# ============================================

log_message("📥 وارد کردن persons از اکسل...")

# Mapping ستون‌های اکسل
excel_columns = {
    'didar_contact_id': 'کد دیدار شخص',
    'last_name': 'نام خانوادگی مشتری',
    'mobile_phone': 'تلفن همراه مشتری',
    'work_phone': 'تلفن ثابت مشتری',
    'owner_name': 'مسئول مشتری',
    'register_time': 'تاریخ ثبت مشتری',
    'city': 'شهر_1',
    'job_title': 'شغل',
    'acquaintance_source': 'شیوه آشنایی',
    'customer_products': 'محصولات مشتری',
    'has_purchase': 'دارای فروش قبلی'
}

# بررسی وجود ستون‌ها
missing_columns = []
for excel_col in excel_columns.values():
    if excel_col not in df.columns:
        missing_columns.append(excel_col)

if missing_columns:
    log_message(f"⚠️ ستون‌های زیر پیدا نشد: {missing_columns}")
    log_message(f"ستون‌های موجود: {list(df.columns)}")

# آماده‌سازی داده‌ها
persons_to_insert = []
unmatched_owners = set()
skipped_count = 0

for idx, row in df.iterrows():
    try:
        # خواندن داده‌ها
        didar_contact_id = str(row.get(excel_columns['didar_contact_id'], '')).strip()
        if not didar_contact_id or didar_contact_id == 'nan':
            skipped_count += 1
            continue
        
        last_name = str(row.get(excel_columns['last_name'], '')).strip() if pd.notna(row.get(excel_columns['last_name'])) else ''
        mobile_phone_raw = row.get(excel_columns['mobile_phone'])
        mobile_phone = remove_leading_zero(mobile_phone_raw)
        work_phone_raw = row.get(excel_columns['work_phone'])
        work_phone = remove_leading_zero(work_phone_raw)
        owner_name = normalize_owner_name(row.get(excel_columns['owner_name']))
        city = str(row.get(excel_columns['city'], '')).strip() if pd.notna(row.get(excel_columns['city'])) else ''
        job_title = str(row.get(excel_columns['job_title'], '')).strip() if pd.notna(row.get(excel_columns['job_title'])) else ''
        acquaintance_source = str(row.get(excel_columns['acquaintance_source'], '')).strip() if pd.notna(row.get(excel_columns['acquaintance_source'])) else ''
        customer_products = str(row.get(excel_columns['customer_products'], '')).strip() if pd.notna(row.get(excel_columns['customer_products'])) else ''
        
        # بررسی "داری فروش قبلی"
        has_purchase_str = str(row.get(excel_columns['has_purchase'], '')).strip().lower()
        has_previous_purchase = 1 if 'بله' in has_purchase_str or 'yes' in has_purchase_str else 0
        
        # پیدا کردن owner_didar_id
        owner_didar_id = None
        if owner_name and owner_name in user_mapping:
            owner_didar_id = user_mapping[owner_name]
        else:
            if owner_name:
                unmatched_owners.add(owner_name)
            # اگر owner پیدا نشد، skip نمی‌کنیم، فقط owner_didar_id را null می‌گذاریم
        
        # تاریخ ثبت
        register_time = None
        register_time_str = row.get(excel_columns['register_time'])
        if pd.notna(register_time_str):
            try:
                # تبدیل تاریخ شمسی به میلادی (ساده)
                register_time = str(register_time_str)
            except:
                pass
        
        # ساخت رکورد
        person_data = (
            didar_contact_id,  # didar_contact_id
            owner_didar_id,    # owner_didar_id
            didar_contact_id,  # code (همان didar_contact_id)
            '',                # first_name
            last_name,         # last_name
            mobile_phone,       # mobile_phone
            None,              # secondary_mobile_phone
            work_phone,        # work_phone
            None,              # email
            'Lead',            # contact_type
            city,              # city
            job_title,         # job_title
            acquaintance_source, # acquaintance_source
            None,              # acquaintance_detail
            None,              # content_topic
            None,              # national_id
            has_previous_purchase, # has_previous_purchase
            None,              # birth_date
            register_time,     # register_time
            None,              # visibility_type
            None,              # background_info
            customer_products, # customer_products (جدید)
            datetime.now().strftime('%Y-%m-%d %H:%M:%S'), # last_sync
            None,              # raw_json
            datetime.now().strftime('%Y-%m-%d %H:%M:%S')  # created_at
        )
        
        persons_to_insert.append(person_data)
        
    except Exception as e:
        log_message(f"⚠️ خطا در پردازش ردیف {idx}: {e}")
        skipped_count += 1
        continue

log_message(f"✅ {len(persons_to_insert)} person آماده برای insert")
log_message(f"⚠️ {skipped_count} ردیف skip شد")

if unmatched_owners:
    log_message(f"\n⚠️ {len(unmatched_owners)} مسئول تطبیق نیافته:")
    for owner in sorted(unmatched_owners):
        count = len(df[df[excel_columns['owner_name']].astype(str).str.strip() == owner])
        log_message(f"  - {owner}: {count} لید")

# Insert persons
log_message("💾 در حال insert کردن persons...")
insert_sql = """INSERT OR REPLACE INTO persons (
    didar_contact_id, owner_didar_id, code, first_name, last_name,
    mobile_phone, secondary_mobile_phone, work_phone, email,
    contact_type, city, job_title, acquaintance_source, acquaintance_detail,
    content_topic, national_id, has_previous_purchase, birth_date,
    register_time, visibility_type, background_info, customer_products,
    last_sync, raw_json, created_at
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""

new_cursor.executemany(insert_sql, persons_to_insert)
new_conn.commit()
log_message(f"✅ {len(persons_to_insert)} person insert شد")

# ============================================
# مرحله 8: ساخت virtual_stages
# ============================================

log_message("📊 ساخت virtual_stages...")

# ساخت جدول virtual_stages اگر وجود ندارد
new_cursor.execute("""
    CREATE TABLE IF NOT EXISTS virtual_stages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        contact_didar_id TEXT NOT NULL,
        stage_name TEXT NOT NULL,
        entered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        last_activity_id TEXT,
        notes TEXT,
        UNIQUE(contact_didar_id, stage_name)
    )
""")

# حذف virtual_stages قدیمی (که از دیتابیس قبلی کپی شده)
new_cursor.execute("DELETE FROM virtual_stages")
new_conn.commit()
log_message("  ✅ virtual_stages قدیمی حذف شد")

# برای هر person، virtual stage را بر اساس has_previous_purchase تنظیم می‌کنیم
new_cursor.execute("SELECT didar_contact_id, has_previous_purchase FROM persons")
persons_for_stages = new_cursor.fetchall()

virtual_stages_to_insert = []
for didar_contact_id, has_previous_purchase in persons_for_stages:
    if has_previous_purchase == 1:
        stage_name = 'with_purchase'
    else:
        stage_name = 'without_purchase'
    
    virtual_stages_to_insert.append((
        didar_contact_id,
        stage_name,
        datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
        None,
        None
    ))

if virtual_stages_to_insert:
    new_cursor.executemany("""
        INSERT OR REPLACE INTO virtual_stages 
        (contact_didar_id, stage_name, entered_at, last_activity_id, notes)
        VALUES (?, ?, ?, ?, ?)
    """, virtual_stages_to_insert)
    new_conn.commit()
    log_message(f"✅ {len(virtual_stages_to_insert)} virtual_stage ساخته شد")

# ============================================
# مرحله 9: خلاصه
# ============================================

log_message("\n" + "=" * 60)
log_message("✅ دیتابیس جدید با موفقیت ساخته شد!")
log_message("=" * 60)

# آمار
new_cursor.execute("SELECT COUNT(*) FROM persons")
person_count = new_cursor.fetchone()[0]

new_cursor.execute("SELECT COUNT(*) FROM virtual_stages")
vs_count = new_cursor.fetchone()[0]

new_cursor.execute("SELECT COUNT(*) FROM users")
user_count = new_cursor.fetchone()[0]

log_message(f"\n📊 آمار دیتابیس جدید:")
log_message(f"  👥 کاربران: {user_count}")
log_message(f"  👤 اشخاص: {person_count}")
log_message(f"  📊 Virtual Stages: {vs_count}")

log_message(f"\n💾 نام فایل: {NEW_DB}")

# بستن اتصالات
source_conn.close()
new_conn.close()

log_message("\n✅ تمام!")