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

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

    # 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 = {
            'shipto_code': row['ExactERPAddressId'],
            'ship_to_id': row['ParentShipTo']  if row['ParentShipTo'] else None, 
            'is_active': row['IsActive']  if row['IsActive'] else 0, 

        }

        address = AddressMaster.objects.filter(
           ExactERPAddressId=ship_to_data['shipto_code']).last()
        
        if address:
            address.IsActive=ship_to_data['is_active']
            address.save()


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



update_address()