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 po_app.models import OffDays





def update_holidays():

    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='Holidays'
    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)


    for index, row in enumerate(data):

        try:
            holiday_data = {
                'index': index + 2,
                'name': row['Name'],
                'date': datetime.strptime(row['Date'], "%d/%m/%Y").date() if row[
                    'Date'] else None,
            }
            print(holiday_data)

            holiday, created = OffDays.objects.get_or_create(Date=holiday_data['date'])
            holiday.Remarks=holiday_data['name']
            print(holiday.Remarks)
            holiday.save()
        except Exception as e:
            print(str(e))
            continue


update_holidays()