from datetime import date
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 add_parent_shipto():

    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)
    sheet_name = 'OdooPmMaster'

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

    print(sheet.title)

    # Get all records (excluding header)
    data = sheet.get_all_records(head=1)
    headers = sheet.row_values(1)

    skip_list = []

    for index, row in enumerate(data):
        
        print((index+1), row['ExactERPAddressId'])

        # try:
        ship_to_data = {
            'name': row['name'],
            'ship_to_id': row['default_code']  if row['default_code'] else None, 
            'parent_shipto_code': row['ParentShipto']  if row['ParentShipto'] else None, 
            
            'product_article': row['ArticleCode']  if row['ArticleCode'] else None, 
            'product_id': row['ProductId']  if row['ProductId'] else None, 

        }

        address = ShiptoMapping.objects.get_or_create(
           shipto_code=ship_to_data['shipto_code'], ship_to_id=ship_to_data['ship_to_id'], parent_shipto_code=ship_to_data['parent_shipto_code'], 
           product_article=ship_to_data['product_article'], product_id=ship_to_data['product_id'])


        # except Exception as e:
        #     skip_list.append(
        #         {
        #         'old': row['Old ShipToCode'],
        #         'new': row['Original ShipTo']  if row['Original ShipTo'] else None, 
        #     }
        #     )
        #     continue



add_parent_shipto()