import os
import re
from django.db.models import Q
import pyodbc
from datetime import datetime
from django.db import connection
import unicodedata
import gspread
from google.oauth2.service_account import Credentials

from product_app.models import ProductMaster



def update_product_data():
    
    remove_data=False
    # Define the scopes
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

    sheet_id = '1dhLOdCuTE7HRvq-Rlp9P83gZbfcbyfSg666DioHkkVA'

    # Path to your service account credentials
    # SERVICE_ACCOUNT_FILE = 'D:\diffrenz\order_pro\order_pro\pvs_gent_crm\purchase_order_service\media\credentials\service_account_creds.json'
    SERVICE_ACCOUNT_FILE = 'C:\Apache24\service_account_creds.json'

    # Authenticate
    creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
    client = gspread.authorize(creds)

    # Open the sheet by ID
    # sheet link
    sheet_name='packaged_goods'
    spreadsheet_id = '1dhLOdCuTE7HRvq-Rlp9P83gZbfcbyfSg666DioHkkVA'
    sheet = client.open_by_key(spreadsheet_id).worksheet(sheet_name)  # Use sheet1 or .worksheet('Sheet1')

    print(sheet.title)

    data = sheet.get_all_records(head=1)
    headers = sheet.row_values(1)
    print(headers)

    multi_list = []


    for index, row in enumerate(data):
        print(index+1)

        try:
            product_data = {
                'index': index + 2,
                'article_code': row['ArticleCode'],
                'nick_name': row['NickName'].strip(),
                'chemical_name': row['ChemicalName'].strip(),
                'slot_category': row['SlotCategory'].strip(),
                'density': row['Density'],
                'packaging': row['Packaging'].strip(),
                'cost_center': row['CostCenter'].strip(),
                'full_name': row['FullName'].strip(),
                'dilution': row['Dilution'],
                'category': row['ProductCategory'].strip(),
                'nl_full_name': row['NL_FullName'].strip(),
                'fr_full_name': row['FR_FullName'].strip(),
                'en_full_name': row['EN_FullName'].strip(),
                'du_full_name': row['DU_FullName'].strip(),
                'product_name': row['ProductName'].strip(),
            }

            if product_data['packaging'].lower() == 'bulk':
                continue
            
            prod , created = ProductMaster.objects.get_or_create(ArticleCode=product_data['article_code'])
            # prod.NickName = product_data['nick_name']
            # prod.ChemicalName = product_data['chemical_name']
            # prod.Density = product_data['density']
            prod.CostCenter = product_data['cost_center']
            # prod.Dilution = product_data['dilution']
            prod.ProductCategory = product_data['slot_category'] if product_data['slot_category'] else ''
            # prod.DU_FullName = product_data['du_full_name']
            # prod.FR_FullName = product_data['fr_full_name']
            # prod.EN_FullName = product_data['en_full_name']
            # prod.Packaging = product_data['packaging']
            # prod.ProductName = product_data['product_name']
            # prod.FullName = product_data['full_name']

            prod.save()
            

        except Exception as e:
            multi_list.append(product_data['article_code'])
            print(product_data['article_code'], str(e))
            continue
        

    print('product data added')
    print("multiple list occured", multi_list)

update_product_data()