from itertools import count

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


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 extract_article_code(text):
    match = re.match(r'^(\d+)', text.strip())
    if match:
        return int(match.group(1))
    return None


def get_or_create_article_relation(cursor, article_code, ship_to, invoice_to, ordered_by, new_data):
    """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."
        print(success_msg)
        new_data.append(success_msg)
        # log_activity(success_msg)


def add_address_relation(cursor, debnr, new_data):
    """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:
        try:
            print('=======')
            article_code = extract_article_code(order[0])
            article_relation_data = {
                'article_code': article_code,
                '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'],
                                           new_data)
        except:
            pass

    return formated_article_code


def get_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
                        FROM [101].dbo.cicmpy  c1
                        WHERE c1.debnr IS NOT NULL
                """
    params = (
    )

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


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

    address = get_address_data(cursor)

    new_data = []

    for index, instance in enumerate(address):
        print('index', index)
        address_dict = {
            'ExactERPAddressId': instance[0],
            'CustomerName': instance[1],
            'AddressLine1': instance[2],
            'City': instance[3],

        }

        ship_to = instance[0]

        add_address_relation(cursor, ship_to, new_data)

    new_data = list(set(new_data))
    print('count of new data', len(new_data))
    conn.commit()
    conn.close()
# sync_address_relation()