import os
import re
import pyodbc
from django.db import connection
from product_app import models as prod_models

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 update_product_details_from_excat():

    cursor, conn = connect_sql_server_live()

    article_code = ('25010','25150','25500','25510','25520','25530','30106','30109','30110','30116',
                    '30117','30242','30243','30244','30245','30246','30207','30219','30220','30221',
                    '30222','30224','30226','30227','30228','30229','30232','31232','30233','30235',
                    '30236','30237','30503','30504','30322','30327','30330','30332','30333','30334',
                    '30335','30336','30337','30338')


    product_query = f"""
            select SearchCode, ItemCode,  Description_1, Description_2, Description_3, Description_4 from [101].[dbo].[Items]
                WHERE SearchCode in {article_code};        
    """

    cursor.execute(product_query, [])
    products = cursor.fetchall()

    not_fnd = []

    for prod in products:
        try:
            pack = prod_models.ProductMaster.objects.get(ArticleCode=prod[0].strip())
            # pack = prod_models.PackageMaster.objects.get(article_code=prod[0])
            # print(f"{prod[0]}, - { prod[2]}, - {pack.article_code}, - {pack.packaged_product}")
        except:
            not_fnd.append(prod[0].strip())

    print(not_fnd)


update_product_details_from_excat()