import pandas as pd
from .models import City, Product, Customer
from django.contrib.auth import get_user_model
from django.utils.timezone import make_aware
User = get_user_model()

def read_and_analyze_excel(file_path, sheet_name=0):
    """
    Reads an Excel file and provides basic analytics.
    
    :param file_path: Path to the .xlsx file
    :param sheet_name: Sheet name (str or int). Use None to read all sheets.
    :return: Dictionary with sheet names as keys, and each value is a tuple (DataFrame, analytics)
    """
    # Read the Excel file
    data = pd.read_excel(file_path, sheet_name=sheet_name, engine='openpyxl')
    
    df = data
    analytics = {
        "Head": df.head(),
        "Shape": df.shape,
        "Dtypes": df.dtypes,
        "Missing Values": df.isnull().sum(),
        "Describe": df.describe()
    }
    #value_counts = df["Mã hàng"].value_counts()
    #print(value_counts)
    #print(df["Mã hàng"].unique())

    return df, analytics

def getCodes(code):
    arrs = code.split(',')
    results = []
    for item in arrs:
        if item.find('\n') == -1:
            if item.strip() not in results:
                results.append(item.strip().upper())
        else:
            narrs = item.strip().split('\n')
            for nitem in narrs:
                if nitem.strip() not in results:
                    results.append(nitem.strip().upper())   
    return set(results)  

def addCode(df):
    code_results = []
    codes = df["codes"].unique()
    for code in codes:
        if code != None and type(code) == str and code.strip() != "":
            code_results = list(set(code_results) | getCodes(code))
    for code in code_results:
        product = Product.objects.filter(name=code).first()
        if product == None:
            product = Product(code=code, name=code)
            product.save()

def addCity(df):
    code_results = []
    codes = df["city"].unique()
    for code in codes:
        if code != None and type(code) == str and code.strip() != "":
            city = City.objects.filter(name=code.strip().title()).first()
            if city == None:
                city = City(name=code.strip().title())
                city.save()
    #print (code_results)
def safe_str(value):
    """Chuyển giá trị sang chuỗi an toàn, tránh lỗi nếu là NaN"""
    return str(value).strip() if pd.notna(value) else ""
def getUserName(name):
    users = {"Duy Em": "Duy","Chloe":"Chloe","Duyên":"Duyen","Gia Huy":"Huy","Duy Anh":"Binh","Tuấn":"Tuan","Thủy":"Thuy","Lan":"Lan","Trinh":"Trinh","chị Huyền":"Huyen","Đạt":"Dat","QA":"QuynhAnh","Vân":"Van","Thanh Mai":"Alice","thủy":"Thuy","Hiền":"Jannie","TM":"ThuongMai","Big customer":"BigCustomer","Jannie":"Jannie","Khách to":"BigCustomer","tuấn":"Tuan","X":"X"}
    return users[name]
def import_customers_from_dataframe(df):
    imported = 0
    errors = []

    for index, row in df.iterrows():
        #try:
        name = safe_str(row.get("name", "")).strip()
        tax_code = safe_str(row.get("mst", "")).strip()
        vat = safe_str(row.get("vat", ""))
        vat_status = 1
        if vat.strip().lower() == "ko" or vat.strip().lower() == "":
            vat_status = 0
        elif vat.strip().lower() == "mix":
            vat_status = 2

        potential = safe_str(row.get("potential", "")).strip()
        city_name = safe_str(row.get("city", "")).strip().title()
        pic_name = safe_str(row.get("pic", "")).strip()
        register_date1 = pd.to_datetime(row.get("register_date"), errors="coerce", dayfirst=True)
        register_date = make_aware(register_date1) if pd.notna(register_date1) else None
        note = safe_str(row.get("note", "")).strip()
        feedback = safe_str(row.get("feedback", "")).strip()
        address = safe_str(row.get("address", "")).strip()
        contact_person = safe_str(row.get("contact", "")).strip()
        phone_number = safe_str(row.get("tel", "")).strip()
        #print (phone_number)
        product_codes = []
        codes = safe_str(row.get("codes", "")).strip()
        if codes != "":
            product_codes = getCodes(codes)

        if not tax_code:
            errors.append(f"Dòng {index+2}: thiếu MST")
            continue

        city = City.objects.filter(name=city_name).first() if city_name else None
        pic = User.objects.filter(username=getUserName(pic_name)).first() if pic_name else None

        customer, created = Customer.objects.get_or_create(
            tax_code=tax_code,
            defaults={
                "name": name,
                "vat_status": vat_status,
                "potential": potential,
                "city": city,
                "pic": pic,
                "register_date": register_date,
                "note": note,
                "feedback": feedback,
                "address": address,
                "contact_person": contact_person,
                "phone_number": phone_number
            }
        )

        # if not created:
        #     # Nếu đã tồn tại thì cập nhật thông tin
        #     customer.name = name
        #     customer.vat_status = vat_status
        #     customer.potential = potential
        #     customer.city = city
        #     customer.pic = pic
        #     customer.register_date = register_date
        #     customer.note = note
        #     customer.feedback = feedback
        #     customer.address = address
        #     customer.contact_person = contact_person
        #     customer.phone_number = phone_number
        #     customer.save()

        # # Gắn sản phẩm
        product_objs = []
        for code in product_codes:
            product = Product.objects.filter(name=code).first()
            if product != None:
                product_objs.append(product)

        customer.products.set(product_objs)
        customer.save()
        imported += 1

    return imported, errors



file_path = '/home/code/crm/crm.xlsx'
df, analytics = read_and_analyze_excel(file_path, "Book2")
imported, errors = import_customers_from_dataframe(df)
print (imported, errors)
#print (analytics)
#addCode(df)
#addCity(df)