import mysql.connector
from datetime import datetime
from openpyxl import load_workbook

from datetime import datetime

def convert_begin_date(code):
    if not code or not isinstance(code, str) or code.strip() == "":
        return None
    code = code.strip().upper()
    if len(code) < 2:
        return None
    season = code[0]
    try:
        year_suffix = int(code[1:])
        year = 1900 + year_suffix if year_suffix > 30 else 2000 + year_suffix
        if season == 'S':
            return datetime(year, 3, 1)  # Spring = March 1
        elif season == 'F':
            return datetime(year, 9, 1)  # Fall = September 1
    except ValueError:
        return None

xlsx_file_path = 'library-subscriptions.xlsx'  # path to your Excel file

# MySQL DB connection
mysql_conn = mysql.connector.connect(
    host='localhost',
    user='mathplac_pmeusr',
    password='rgPB!0E!3n1tb4dI',
    database='mathplac_pmedb'
)
mysql_cursor = mysql_conn.cursor()

# Load workbook and select the first sheet (or specify by name)
wb = load_workbook(filename=xlsx_file_path, data_only=True)
ws = wb.active  # or wb['SheetName']

# Get header row (assumed to be the first row)
headers = [cell.value for cell in next(ws.iter_rows(min_row=1, max_row=1))]

# Iterate over all subsequent rows
for row in ws.iter_rows(min_row=2, values_only=True):
    record = dict(zip(headers, row))

    # Combine Zip and NineZip into a single zip_code field
    zip_part = record.get('Zip')
    nine_zip = record.get('NineZip')
    if zip_part and nine_zip:
        zip_code = f"{zip_part}-{nine_zip}"
    elif zip_part:
        zip_code = zip_part
    elif nine_zip:
        zip_code = nine_zip
    else:
        zip_code = None

    # Insert into contactinfo table
    contactinfo_sql = """
        INSERT INTO contactinfo (
            addr1, addr2, city, state, zip, country, created, modified
        ) VALUES (%s, %s, %s, %s, %s, %s, NOW(), NOW())
    """
    contactinfo_values = (
        record.get('Address1'),
        record.get('Address2'),
        record.get('City'),
        record.get('State'),
        zip_code,
        'USA'
    )
    mysql_cursor.execute(contactinfo_sql, contactinfo_values)
    contactinfo_id = mysql_cursor.lastrowid

    # Combine notes and replacement copies
    notes = (record.get('Notes') or '')
    if record.get('ReplacementCopies'):
        notes += f"\nReplacementCopies: {record['ReplacementCopies']}"

    # Insert into subscribers table
    subscribers_sql = """
        INSERT INTO subscribers (
            contactinfo_id, libraryname, librarydept, libraryagent,
            start, end, created, modified, status, notes
        ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    begin_date = convert_begin_date(record.get('BeginDate'))
    now = datetime.now()
    subscribers_values = (
        contactinfo_id,
        record.get('LibraryName'),
        record.get('LibraryDepartment'),
        record.get('LibraryAgent'),
        begin_date,
        record.get('EndDate'),
        now,  # created
        now,  # modified
        record.get('Status'),
        notes
    )
    mysql_cursor.execute(subscribers_sql, subscribers_values)

# Finalize changes
mysql_conn.commit()
mysql_conn.close()