import pyodbc
import datetime
import re
from datetime import datetime
from django.core.mail import EmailMessage
import os

COUNTRY_DATA = {
    'MX': 'Mexico',
    'NL': 'Netherlands',
    'BE': 'Belgium',
    'AE': 'UAE',
    'FR': 'France',
    'OM': 'Oman',
    'ES': 'Spain',
    'GB': 'United Kingdom',
    'LT': 'Lithuania',
    'DE': 'Germany',
    'IT': 'Italy',
    'GR': 'Greece',
    'AZ': 'Azerbaijan',
    'TR': 'Turkey',
    'DK': 'Denmark',
    'US': 'United States',
    'TH': 'Thailand',
    'BR': 'Brazil',
    'CH': 'Switzerland',
    'HU': 'Hungary',
    'RS': 'Serbia',
    'AU': 'Australia',
    'RO': 'Romania',
    'EG': 'Egypt',
    'ZA': 'South Africa',
    'PT': 'Portugal',
    'UA': 'Ukraine',
    'CZ': 'Czech Republic',
    'IE': 'Ireland',
    'XI': 'Northern Ireland',
    'PL': 'Poland',
    'CY': 'Cyprus',
    'SE': 'Sweden',
    'RU': 'Russia',
    'NO': 'Norway',
    'CA': 'Canada',
    'QA': 'Qatar',
    'TN': 'Tunisia',
    'LV': 'Latvia',
    'AT': 'Austria',
    'LU': 'Luxembourg',
    'FI': 'Finland',
    'SK': 'Slovakia',
    'AR': 'Argentina',
    'BG': 'Bulgaria',
    'CL': 'Chile',
    'SG': 'Singapore',
    'SZ': 'Swaziland',
    'EE': 'Estonia',
    'SI': 'Slovenia',
    'NZ': 'New Zealand'
}

log_file = "D:\diffrenz\order_pro\order_pro\pvs_gent_crm\customer_service\customer_app\scripts\AddressLog.txt"


def log_activity(message):
    with open(log_file, "a") as f:
        timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        f.write(f"[{timestamp}] {message}\n")


def extract_article_code(text):
    match = re.match(r'^(\d+)', text.strip())
    if match:
        return int(match.group(1))
    return None


def connect_sql_server_live():
    # Connection configuration
    server = 'SRVPSQL01'
    database = 'pvsmaster_gent'
    username = 'parvathy'
    password = 'Tyc@n123!'
    driver = '{ODBC Driver 17 for SQL Server}'

    conn = pyodbc.connect(
        f'DRIVER={driver};'
        f'SERVER={server};'
        f'DATABASE={database};'
        f'UID={username};'
        f'PWD={password}'
    )

    cursor = conn.cursor()

    return cursor, conn


def get_or_create_article_relation(cursor, article_code, ship_to, invoice_to, ordered_by):
    """Function to create if address relationship if not exists"""
    # First, check if the combination exists
    check_query = """
    SELECT 1 FROM AddressCodeRelationship
    WHERE ArticleCode = ? AND ShipToCode = ? AND InvoiceToCode = ? AND OrderedByCode = ?
    """

    cursor.execute(check_query, (article_code, ship_to, invoice_to, ordered_by))
    exists = cursor.fetchone()

    if not exists:
        # If it does not exist, insert it
        insert_query = """
        INSERT INTO AddressCodeRelationship (ArticleCode, ShipToCode, InvoiceToCode, OrderedByCode)
        VALUES (?, ?, ?, ?)
        """
        cursor.execute(insert_query, (article_code, ship_to, invoice_to, ordered_by))

        # Call the function since this is a new insert
        success_msg = f"{article_code, ship_to, invoice_to, ordered_by} records inserted successfully."
        # log_activity(success_msg)


def add_address_relation(cursor, debnr):
    """function to add address code relationship (combination of ShipToCode, ArticleCode, InvoiceToCode, OrderedByCode)"""

    order_query = """            
                SELECT 
                    t2.artcode, 
                    t1.debnr, t1.[fakdebnr]
                        ,t1.[verzdebnr]
                        ,t1.[einddebnr],
                    COUNT(DISTINCT t1.ordernr) AS order_count
                FROM [101].[dbo].[orkrg] t1
                JOIN [101].[dbo].[orsrg] t2
                    ON t1.ordernr = t2.ordernr
                WHERE t1.debnr = ?
                    AND t2.regel = 1
                GROUP BY t2.artcode, t1.debnr,  t1.[fakdebnr]
                        ,t1.[verzdebnr]
                        ,t1.[einddebnr];
                                """
    params = (
        debnr,
    )

    cursor.execute(order_query, params)
    orders = cursor.fetchall()

    formated_article_code = []
    for order in orders:
        print('=======')
        article_code = extract_article_code(order[0])
        formated_article_code.append(article_code)
        article_relation_data = {
            'article_code': extract_article_code(order[0]),
            'ShipToCode': int(order[1]),
            'InvoiceToCode': int(order[2]),
            'OrderedByCode': int(order[3]),
        }
        get_or_create_article_relation(cursor, article_relation_data['article_code'],
                                       article_relation_data['ShipToCode'],
                                       article_relation_data['InvoiceToCode'], article_relation_data['OrderedByCode'])

    return formated_article_code


def insert_address_data(cursor, conn, addition_list):
    """function to insert newly added address and log them"""

    insert_query = """
          INSERT INTO pvsmaster_gent.dbo.AddressMaster (
                    ExactERPAddressId,
                    CustomerName,
                    AddressLine1,
                    AddressLine2,
                    AddressLine3,
                    City,
                    PostalCode,
                    UpdatedBy,
                    IsDeleted,
                    CountryCode,
                    ContactPerson,
                    Phone,
                    Email, 
                    CustomerNickName,
                    CreatedBy,
                    CreatedDate,
                    Country, 
                    ArticleCode

             )
             VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
          """

    # try:
    for row in addition_list:
        debnr = row['ExactERPAddressId']
        formated_article_code = add_address_relation(cursor, debnr)
        formated_article_code = ",".join(map(str, formated_article_code))

        if formated_article_code == '':
            tail = row['City']
        else:
            tail = f" ({formated_article_code})"

        nickname = f"{debnr} {row['CustomerName']}-{tail}"
        country_code = row['CountryCode'].strip().upper()
        country = COUNTRY_DATA.get(country_code, None)

        cursor.execute(insert_query, (
            debnr,
            row['CustomerName'],
            row['AddressLine1'],
            row['AddressLine2'],
            row['AddressLine3'],
            row['City'],
            row['PostalCode'],
            row.get('UpdatedBy', 27),
            row.get('IsDeleted', 0),
            row['CountryCode'],
            row['ContactPerson'],
            row['Phone'],
            row['Email'],
            nickname,
            27,
            datetime.now(),
            country,
            formated_article_code
        ))

        conn.commit()
        success_msg = f"{len(addition_list)} records inserted successfully."
        print(success_msg)
        # log_activity(success_msg)

    # except Exception as e:
    #     error_msg = f"Error inserting data: {str(e)}"
    #     print(error_msg)
    # log_activity(error_msg)
    # finally:
    #     cursor.close()
    #     conn.close()
    #
    # return True


def update_address_data(cursor, conn, update_list):
    """function to update modified address and log them"""

    update_query = """
        UPDATE pvsmaster_gent.dbo.AddressMaster SET 
            CustomerName = ?,
            AddressLine1 = ?,
            City = ?,
            PostalCode = ?,
            UpdatedBy = ?,
            IsDeleted = ?,
            CountryCode = ?,
            ContactPerson = ?,
            Phone = ?,
            Email = ?,
            AddressLine2 = ?,
            AddressLine3 = ?
        WHERE ExactERPAddressId = ?
    """

    try:
        for row in update_list:
            cursor.execute(update_query, (
                row['CustomerName'],
                row['AddressLine1'],
                row['City'],
                row['PostalCode'],
                row.get('UpdatedBy', 27),
                row.get('IsDeleted', 0),
                row['CountryCode'],
                row['ContactPerson'],
                row['Phone'],
                row['Email'],
                row['AddressLine2'],
                row['AddressLine3'],
                row['ExactERPAddressId']
            ))

            conn.commit()
            success_msg = f"address  with shiptocode {row['ExactERPAddressId']} updated successfully."
            # print(success_msg)
            # log_activity(success_msg)

    except Exception as e:
        error_msg = f"Error updating data: {str(e)}"
        print(error_msg)
        # log_activity(error_msg)

    finally:
        cursor.close()
        conn.close()

    return True


def get_modified_address_data(cursor):
    """function to get all modified data in 10 days"""
    select_query = """
                        SELECT c1.debnr, c1.cmp_name, c1.cmp_fadd1, c1.cmp_fcity, c1.cmp_fpc, c1.cmp_fctry, c2.cnt_email,  c2.cnt_f_tel,  c2.FullName,  c1.cmp_fadd2, c1.cmp_fadd3 
                        FROM [101].dbo.cicmpy c1 
                        JOIN [101].dbo.cicntp c2 ON c2.cnt_id = c1.cnt_id
                        WHERE c1.debnr IS NOT NULL
                        AND c1.sysmodified >= CAST(DATEADD(DAY, -5, CAST(GETDATE() AS DATE)) AS DATETIME)
                """

    params = (

    )

    cursor.execute(select_query, params)
    address = cursor.fetchall()
    return address


def sync_address_data_table():
    cursor, conn = connect_sql_server_live()

    address = get_modified_address_data(cursor)

    addition_list = []
    update_list = []
    db_address = []

    for instance in address:
        address_dict = {
            'ExactERPAddressId': instance[0],
            'CustomerName': instance[1],
            'AddressLine1': instance[2],
            'City': instance[3],
            'PostalCode': instance[4],
            'UpdatedBy': 27,
            'IsDeleted': 0,
            'CountryCode': instance[5],
            'Email': instance[6],
            'Phone': instance[7],
            'ContactPerson': instance[8],
            'AddressLine2': instance[9],
            'AddressLine3': instance[10],
        }
        print(address_dict)
        dbnr = instance[0]
        if dbnr == "000001":
            print("skiped dbnr with 000001")
            continue

        select_query = """
            SELECT ExactERPAddressId, CustomerName, AddressLine1, City,PostalCode, CountryCode, AddressLine2, AddressLine3  FROM  pvsmaster_gent.dbo.AddressMaster
            WHERE ExactERPAddressId = ?
        """
        params = (
            dbnr
        )
        cursor.execute(select_query, params)
        address = cursor.fetchone()

        if address:
            update_list.append(address_dict)
            db_address.append({'ExactERPAddressId': address[0], 'CustomerName': address[1], 'AddressLine1': address[2],
                               'City': address[3], 'PostalCode': address[4], 'CountryCode': address[5],
                               'AddressLine2': address[6], 'AddressLine3': address[7]})
        else:
            addition_list.append(address_dict)

    print("for updation", len(update_list))
    for instance, db_data in zip(update_list, db_address):
        print(instance)
        print(db_data)

    print("for addition", len(addition_list))
    for instance in addition_list:
        print(instance)

    # Send email with address comparison
    # send_address_comparison_email(addition_list, update_list, db_address)

    # Update the database after sending the email
    update_address_data(cursor, conn, update_list)
    insert_address_data(cursor, conn, addition_list)

#sync_address_data_table()