#!/usr/bin/env python3
"""
Script to extract calendar data from Word document and create Excel file
"""

import pandas as pd
from datetime import datetime
import re
import os

def extract_calendar_data():
    """
    Extract calendar data and create Excel file
    Since we can't directly read the Word document, we'll create a template
    that can be populated with the data from the document
    """
    
    # Create a comprehensive template for 2026 calendar
    calendar_data = []
    
    # Define months and their days
    months = {
        'January': 31,
        'February': 29,  # 2026 is a leap year
        'March': 31,
        'April': 30,
        'May': 31,
        'June': 30,
        'July': 31,
        'August': 31,
        'September': 30,
        'October': 31,
        'November': 30,
        'December': 31
    }
    
    # Create entries for each day of 2026
    for month_name, days_in_month in months.items():
        for day in range(1, days_in_month + 1):
            date_str = f"2026-{list(months.keys()).index(month_name) + 1:02d}-{day:02d}"
            calendar_data.append({
                'Date': date_str,
                'Month': month_name,
                'Day': day,
                'Event Name': '',  # To be filled from Word document
                'Event Type': '',  # Optional categorization
                'Notes': ''  # Additional notes
            })
    
    # Create DataFrame
    df = pd.DataFrame(calendar_data)
    
    # Save to Excel
    output_file = '/var/www/html/wordpress6/wordpress/EP/2026_full_calendar.xlsx'
    df.to_excel(output_file, index=False, sheet_name='2026 Calendar')
    
    print(f"Calendar template created: {output_file}")
    print(f"Total entries: {len(calendar_data)}")
    
    return output_file

def create_january_sample():
    """
    Create a sample with January data as we know it
    """
    january_events = [
        ('2026-01-01', 'NEW YEARS DAY SCRAMBLE'),
        ('2026-01-02', 'EPIC LADIES - Q MEN'),
        ('2026-01-03', 'MENS 60 TEES LADIES 52\'S'),
        ('2026-01-04', 'SCRAMBLE'),
        ('2026-01-05', 'EPIC LADIES (a.m), 321 (Draw), MEN- Q (p.m)'),
        ('2026-01-07', 'MATCHPLAY PRESIDENT VS CAPTAINS'),
        ('2026-01-09', 'EPIC LADIES - Q MEN'),
        ('2026-01-10', 'MENS 60 TEES LADIES 52\'S'),
        ('2026-01-11', 'SCRAMBLE'),
        ('2026-01-12', 'COURSE CLOSED'),
        ('2026-01-13', 'COURSE CLOSED'),
        ('2026-01-14', 'COURSE CLOSED'),
        ('2026-01-16', 'EPIC LADIES MEN'),
        ('2026-01-17', 'MENS 60 TEES LADIES 52\'S'),
        ('2026-01-18', 'SCRAMBLE'),
        ('2026-01-19', 'EPIC LADIES (p.m), Yellow ball MEN (a.m)'),
        ('2026-01-21', 'MEDAL STABLEFORD 9-HOLE QUALIFIERS'),
        ('2026-01-23', 'EPIC LADIES MEN'),
        ('2026-01-24', 'MENS 60 TEES LADIES 52\'S'),
        ('2026-01-25', 'SCRAMBLE'),
        ('2026-01-26', 'EPIC - Q LADIES (a.m)- Q MEN (p.m)'),
        ('2026-01-28', 'TEAM AM/AM (DRAW)'),
        ('2026-01-30', 'EPIC LADIES MEN'),
        ('2026-01-31', 'MENS 60 TEES LADIES 52\'S')
    ]
    
    # Create DataFrame with January data
    df = pd.DataFrame(january_events, columns=['Date', 'Event Name'])
    df['Month'] = 'January'
    df['Day'] = df['Date'].str.extract(r'-(\d{2})$').astype(int)
    
    # Save to Excel
    output_file = '/var/www/html/wordpress6/wordpress/EP/january_2026_sample.xlsx'
    df.to_excel(output_file, index=False, sheet_name='January 2026')
    
    print(f"January sample created: {output_file}")
    return output_file

if __name__ == "__main__":
    try:
        # Create the full calendar template
        full_calendar = extract_calendar_data()
        
        # Create January sample with known data
        january_sample = create_january_sample()
        
        print("\nFiles created:")
        print(f"1. Full 2026 calendar template: {full_calendar}")
        print(f"2. January 2026 sample: {january_sample}")
        print("\nTo populate the full calendar:")
        print("1. Open the Word document")
        print("2. Copy the event data for each month")
        print("3. Paste it into the corresponding Excel cells")
        print("4. Save the Excel file")
        
    except Exception as e:
        print(f"Error: {e}")
        print("Make sure pandas and openpyxl are installed:")
        print("pip install pandas openpyxl")