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 customer_app.models import AddressMaster


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 = 'SyncPriceShipTo'

    # 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['Old ShipToCode'])

        # try:
        ship_to_data = {
            'old': row['Old ShipToCode'],
            'new': row['Original ShipTo']  if row['Original ShipTo'] else None, 
        }

        address = AddressMaster.objects.filter(ExactERPAddressId=ship_to_data['old'])
        address.update(ParentShipTo=ship_to_data['new'])


        # 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()