from django.test import TestCase
import gspread
from google.oauth2.service_account import Credentials
from declarations.models import Company, EUDeclaration, EUDContactHistory
from datetime import datetime
from dateutil.relativedelta import relativedelta

"""Pre run scripts to create declarations from google sheet"""

def sync_declaration_data():
    """function to create declaration data from exact"""

    # Define the scopes
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
    sheet_id = '1dhLOdCuTE7HRvq-Rlp9P83gZbfcbyfSg666DioHkkVA'

    # 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
    spreadsheet_id = '1dhLOdCuTE7HRvq-Rlp9P83gZbfcbyfSg666DioHkkVA'
    sheet = client.open_by_key(spreadsheet_id).worksheet("EUD_2025")  # Use sheet1 or .worksheet('Sheet1')
    # sheet = client.open_by_key(spreadsheet_id).worksheet("transporter")

    print(sheet.title)

    data = sheet.get_all_records(head=1)

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

        eud_data = {
            'index': index+1,
            'customer': row['COMPANY NAME'].strip(),
            'signed_by': row['SIGNED BY'].strip(),
            'start_date': row['START DATE'].strip(),
            'address': row['ADDRESS'].strip(),
            'email': row['E-MAIL'].strip(),
            'address_line1': row['Address Line1'].strip(),
            'address_line2': row['Address Line2'].strip(),
            'address_line3': row['Address Line3'].strip(),
            'add_data': row['AddData'],
            'product_name': row['Product'].strip(),
        }
        # print(eud_data)

        if eud_data['add_data']:
            company, created = Company.objects.get_or_create(
                company_name=eud_data['customer'])
            company.address_line1 = eud_data['address_line1']
            company.address_line2 = eud_data['address_line2']
            company.address_line3 = eud_data['address_line3']
            company.save()
            print(created, company)


            start_date = datetime.strptime(eud_data['start_date'], "%d/%m/%Y").date()
            expiry_date = start_date + relativedelta(years=1)

            print(start_date, expiry_date)
            declaration, created = EUDeclaration.objects.get_or_create(
                company=company,
                product_name=eud_data['product_name'],
                start_date=start_date,
                expiry_date=expiry_date
            )
            if eud_data['email']:
                contact_history, created = EUDContactHistory.objects.get_or_create(
                    declaration=declaration, email=eud_data['email'], address_type='to')

sync_declaration_data()