import pandas as pd
from datetime import datetime, timedelta
import holidays
import os
from pathlib import Path
import random
import csv

# Define date range
start_date = datetime(2023, 1, 1)
end_date = datetime(2023, 12, 31)

# Austrian public holidays
at_holidays = holidays.country_holidays('AT', years=[2023])

# Exclude dates in external CSV-defined ranges
exclude_ranges_path = Path.home() / 'Documents' / 'zisco' / 'exclude_ranges.csv'
exclude_ranges = []
if exclude_ranges_path.exists():
    with open(exclude_ranges_path, newline='') as csvfile:
        reader = csv.reader(csvfile)
        for row in reader:
            try:
                start = datetime.strptime(row[0], '%Y-%m-%d')
                end = datetime.strptime(row[1], '%Y-%m-%d')
                exclude_ranges.append((start, end))
            except Exception:
                continue

def is_in_exclude_ranges(date):
    for start, end in exclude_ranges:
        if start <= date <= end:
            return True
    return False

# Generate all dates in range
dates = []
current = start_date
while current <= end_date:
    if current.weekday() in [1, 3]:  # 1=Tuesday, 3=Thursday
        if current not in at_holidays and not is_in_exclude_ranges(current):
            dates.append((current, "Wien"))
    current += timedelta(days=1)

# Add meetings from separate CSV file
meetings_path = Path.home() / 'Documents' / 'zisco' / 'meetings_2023.csv'
meetings = []
if meetings_path.exists():
    with open(meetings_path, newline='') as csvfile:
        reader = csv.reader(csvfile)
        for row in reader:
            try:
                date = datetime.strptime(row[1], '%d.%m.%Y')
                ziel = row[3]
                #if date not in at_holidays and not is_in_exclude_ranges(date):
                if not is_in_exclude_ranges(date):
                    meetings.append((date, ziel))
            except Exception:
                continue

# Combine dates and meetings, ensuring no duplicates
# all_entries = dates+meetings
# all_entries.sort()

# Combine and remove duplicates (as tuples to deduplicate)
all_entries = list({tuple(row) for row in meetings + dates})

# Sort by date (index 0)
all_entries.sort(key=lambda x: x[0])

df = pd.DataFrame({
    'Datum': [d.strftime('%Y-%m-%d') for d, _ in all_entries],
    'Ziel': [z for _, z in all_entries],
    'Zweck': ['Besprechung'] * len(all_entries),
    'Std.': [random.randint(3, 7) for _ in all_entries],
    'Inland': [''] * len(all_entries),
    'Ausland': [''] * len(all_entries),
    'Tage': [''] * len(all_entries),
    'INLAND': [''] * len(all_entries),
    'AUSLAND': [''] * len(all_entries)
})

# df = pd.DataFrame({
#     'Datum': [d.strftime('%Y-%m-%d') for d in dates],
#     'Ziel': ['Wien'] * len(dates),
#     'Zweck': ['Besprechung'] * len(dates),
#     'Std.': [random.randint(3, 9) for _ in dates],
#     'Inland': [''] * len(dates),
#     'Ausland': [''] * len(dates),
#     'Tage': [''] * len(dates),
#     'INLAND': [''] * len(dates),
#     'AUSLAND': [''] * len(dates)
# })

headers = [['', '', '', '', '', '', '', 'Taggeld', 'Nächtigungen'],
           ['Datum', 'Ziel', 'Zweck', 'Std.', 'Inland', 'Ausland', 'Tage', 'Inland', 'Ausland']]
multi_header = pd.MultiIndex.from_arrays(headers)
df.columns = multi_header

documents_dir = Path.home() / 'Documents'
csv_path = documents_dir / 'zisco' / 'travel_schedule.csv'
os.makedirs(csv_path.parent, exist_ok=True)
df.to_csv(csv_path, index=False)