# Interactive venue selection
print("\nAvailable venues:")
for idx, venue_name in enumerate(available_venues, 1):
    print(f"  {idx}. {venue_name}")

selection_input = input("\nSelect venues (numbers separated by commas, or 'all'): ").strip().lower()
if selection_input == 'all':
    selected_venue_names = list(available_venues)
else:
    selected_indices = [int(x.strip()) - 1 for x in selection_input.split(',')]
    selected_venue_names = [available_venues[idx] for idx in selected_indices]

# Account name filtering (optional)
account_filter_input = input("\nFilter by account names only? (y/n): ").strip().lower()
if account_filter_input == 'y':
    # Show available accounts and allow selection
    selected_account_names = [available_account_names[idx] for idx in selected_indices]
    # Filter events by selected account names
    for venue_name in venue_events:
        venue_events[venue_name] = [
            event for event in venue_events[venue_name]
            if event['title'] in selected_account_names
        ]

# Date range filtering (optional)
date_range_input = input("Filter by date range? (y/n): ").strip().lower()
if date_range_input == 'y':
    start_date_str = input("Enter start date (MM/DD/YYYY): ").strip()
    end_date_str = input("Enter end date (MM/DD/YYYY): ").strip()
    date_range_start = datetime.strptime(start_date_str, '%m/%d/%Y').date()
    date_range_end = datetime.strptime(end_date_str, '%m/%d/%Y').date()

# Dynamic filename generation
facility_names_str = '_'.join(venue_names).replace(' ', '_')
PDF_OUTPUT = f"venue_schedules_{facility_names_str}.pdf"
if filter_by_date_range:
    start_str = date_range_start.strftime('%m-%d-%Y')
    end_str = date_range_end.strftime('%m-%d-%Y')
    PDF_OUTPUT = f"{base_path}_{start_str}-{end_str}.pdf"

TABLOID = (792, 1224)  # 11x17 inches in points
START_HOUR = 5         # 5:00 AM
END_HOUR = 24          # 12:00 AM (midnight)
INTERVAL = 10          # 10-minute slots

# Venue name normalization
VENUE_MAPPINGS = {
    'exeter 1': 'EXETER 1', 'exeter 2': 'EXETER 2',
    'govs2': 'GOVS2', 'govs': 'GOVS', 'nhl': 'NHL',
    'oly': 'OLY', 'olympic': 'OLY',
    'north point suite': 'NORTH POINT', 'north pointe suite': 'NORTH POINT',
    'north point': 'NORTH POINT', 'north pointe': 'NORTH POINT',
}

# Dynamic venue rendering — Exeter venues only shown if they have events
exeter_venues = ['EXETER 1', 'EXETER 2']
for day_idx in range(7):
    current_date = week_start + timedelta(days=day_idx)
    for venue_idx, venue in enumerate(venue_names):
        if venue in exeter_venues:
            has_events = any(
                e['start'].date() == current_date
                for e in venue_events.get(venue, [])
            )
            venues_with_events.append(has_events)
        else:
            venues_with_events.append(True)  # Always show

# All-day event banners at top of columns
all_day_time = datetime.strptime('12:00 AM', '%I:%M %p').time()
for event in venue_events.get(venue, []):
    if event['start'].time() == all_day_time and event['end'].time() == all_day_time:
        # Draw banner at top of day/venue column
        c.drawString(x + 4, y - allday_banner_height + 3, event['title'])

def events_overlap(e1, e2):
    """Returns True if two events overlap in time."""
    latest_start = max(e1['start'], e2['start'])
    earliest_end = min(e1['end'], e2['end'])
    return latest_start < earliest_end

# Check all pairs per venue/date, excluding all-day events
for date, day_events in sorted(events_by_date.items()):
    regular_events = [e for e in day_events if not is_all_day_event(e)]
    regular_events = sorted(regular_events, key=lambda x: x['start'])
    for i in range(len(regular_events)):
        for j in range(i+1, len(regular_events)):
            if events_overlap(regular_events[i], regular_events[j]):
                overlaps.append((regular_events[i], regular_events[j]))

# Interactive operating hours input
def parse_time_input(time_str):
    """Parse time input in various formats (e.g., '5:00 AM', '5 AM', '5', '17:00', '17')"""
    time_str = time_str.strip().upper()
    # Try 12-hour formats first
    formats = ['%I:%M %p', '%I %p', '%I:%M%p', '%I%p']
    for fmt in formats:
        try:
            dt = datetime.strptime(time_str, fmt)
            return dt.hour
        except ValueError:
            continue
    # Try 24-hour format
    if ':' in time_str:
        hour = int(time_str.split(':')[0])
    else:
        hour = int(time_str)
    return hour if 0 <= hour <= 23 else None

START_HOUR = parse_time_input(input("Start hour (e.g., 5:00 AM or 5): "))
END_HOUR = parse_time_input(input("End hour (e.g., 10:00 PM or 22): "))
MIN_DURATION_MINUTES = int(input("Enter minimum open duration (minutes): "))

BUFFER_MINUTES = 10  # 10-minute gaps before and after events

def get_occupied_time_ranges(events, date):
    """Get occupied time ranges including buffers, then merge overlaps."""
    occupied_ranges = []
    for event in events:
        if is_all_day_event(event):
            continue
        # Handle multi-day events
        if event_start.date() <= date <= event_end.date():
            if event_start.date() == date:
                day_start = event_start
            else:
                day_start = datetime.combine(date, time(hour=START_HOUR))
            # Add 10-minute buffer before and after
            buffered_start = day_start - timedelta(minutes=BUFFER_MINUTES)
            buffered_end = day_end + timedelta(minutes=BUFFER_MINUTES)
            # Constrain to operating hours
            buffered_start = max(buffered_start, day_start_time)
            buffered_end = min(buffered_end, day_end_time)
            if buffered_start < buffered_end:
                occupied_ranges.append((buffered_start, buffered_end))
    
    # Merge overlapping ranges
    merged_ranges = []
    for start, end in sorted(occupied_ranges):
        if merged_ranges and start <= merged_ranges[-1][1]:
            merged_ranges[-1] = (merged_ranges[-1][0], max(merged_ranges[-1][1], end))
        else:
            merged_ranges.append((start, end))
    return merged_ranges

def normalize_sheet(ws):
    """Parse unstructured schedule sheet — handles dates appearing after teams."""
    week = ws.title
    current_day = None
    current_date = None
    pending_for_day = []  # Records waiting for a date

    def flush_pending_with_date(date):
        """Apply discovered date to all pending records."""
        nonlocal pending_for_day
        for rec in pending_for_day:
            rec["date"] = date
            records.append(rec)
        pending_for_day = []

    for r in range(1, ws.max_row + 1):
        a, b, c = ws.cell(row=r, column=1).value, ...
        
        # Detect day headers (MON, TUESDAY, etc.)
        if isinstance(a, str) and a.strip().upper() in valid_days:
            flush_pending_with_date(current_date)
            current_day = a.strip()
            current_date = None
        
        # Detect and apply date — flush pending records
        parsed_date = parse_possible_date(a)
        if parsed_date is not None:
            current_date = parsed_date
            flush_pending_with_date(current_date)
        
        # If date not yet known, hold records until we see it
        if current_date is None:
            pending_for_day.extend(row_recs)
        else:
            records.extend(row_recs)

def process_spreadsheets(existing_file, new_file, existing_sheet, new_sheet):
    existing_df = pd.read_excel(existing_file, sheet_name=existing_sheet)
    new_df = pd.read_excel(new_file, sheet_name=new_sheet)
    
    # Match and reorder columns to existing structure
    matched_headers = [h for h in new_df.columns if h in existing_df.columns]
    matched_df = new_df[matched_headers]
    
    # Add blank columns for headers present in existing but not in new
    for header in existing_df.columns:
        if header not in matched_df.columns:
            matched_df[header] = np.nan
    matched_df = matched_df[existing_df.columns]  # Reorder
    
    # Append with separator row, preserving all other sheets
    blank_row = pd.DataFrame([{col: np.nan for col in existing_df.columns}])
    combined_df = pd.concat([existing_df, blank_row, matched_df], ignore_index=True)
    
    # Write all sheets back, then autofit columns
    with pd.ExcelWriter(existing_file, engine='openpyxl') as writer:
        for sheet_name, df in all_sheets.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    autofit_columns(existing_file)

# Position order for sorting
position_order = {
    'forward': 0, 'forward/defense': 1,
    'defense': 2, 'goalie': 3
}

# Forwards: 3-column grid, aligned to page width
position_groups = [("FORWARDS", ["forward", "forward/defense"])]
cols = 3
available_width = width - 2 * margin
box_width = (available_width - (cols - 1) * spacing) / cols

# Defense: 2-column grid using same box_width for alignment
d_cols = 2
d_box_width = box_width  # Same as forward columns

# Goalies: 1-column grid, positioned in 3rd column
g_x_start = margin + 2 * (box_width + spacing)
g_box_width = box_width  # Perfect alignment with forward grid

# Player box with info sections and number display
def draw_player_box(c, player, x, y_box, box_width, box_height, idx):
    divider_x = x + box_width * 0.75
    # Left: Name (bold), Shot · Position, Team, Birthday, City/State
    # Right: Large jersey number in blue

# Smart opponent detection for schedule display
team1_str = str(game['Team 1'])
team2_str = str(game['Team 2'])
is_rs_vs_rs = 'Real Speed' in team1_str and 'Real Speed' in team2_str

if is_rs_vs_rs:
    # Use birth year suffix to identify our team (e.g. '2009' → '09')
    our_id = str(birth_year).strip()[-2:] if '/' not in str(birth_year) else ''
    if our_id and our_id in team1_str:
        opponent = team2_str
    elif our_id and our_id in team2_str:
        opponent = team1_str
    else:
        opponent = f"{team1_str} vs {team2_str}"
elif 'Real Speed' in team1_str:
    opponent = team2_str
elif 'Real Speed' in team2_str:
    opponent = team1_str
else:  # Playoff game — neither team named yet
    opponent = f"{team1_str} vs {team2_str}"