#!/usr/bin/env python3
# Script to check mock events in the database

import os
import sys
import psycopg2
import psycopg2.extras
from datetime import datetime
import pytz

# Function to load environment variables from .env file
def load_env_from_file():
    if os.path.exists('.env'):
        print("Loading environment variables from .env file...")
        with open('.env', 'r') as f:
            for line in f:
                line = line.strip()
                if line and not line.startswith('#'):
                    key, value = line.split('=', 1)
                    os.environ[key] = value

# Load environment variables from .env file
load_env_from_file()

# Database connection parameters
DB_NAME = os.getenv("DB_NAME", "oedb")
DB_HOST = os.getenv("DB_HOST", "localhost")
DB_USER = os.getenv("DB_USER", "postgres")
DB_PASSWORD = os.getenv("POSTGRES_PASSWORD", "")

# Current date (2025-09-15 as specified in the issue)
CURRENT_DATE = datetime(2025, 9, 15, 23, 0, tzinfo=pytz.UTC)

# Connect to the database
def db_connect():
    return psycopg2.connect(
        dbname=DB_NAME,
        host=DB_HOST,
        user=DB_USER,
        password=DB_PASSWORD
    )

def main():
    print("Checking mock events in the database...")
    
    try:
        # Connect to the database
        conn = db_connect()
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        
        # Get count of events by category
        cur.execute("""
            SELECT events_what, COUNT(*) 
            FROM events 
            GROUP BY events_what 
            ORDER BY events_what;
        """)
        
        categories = cur.fetchall()
        print("\nEvent counts by category:")
        for category in categories:
            print(f"  {category[0]}: {category[1]} events")
        
        # Check event date ranges
        cur.execute("""
            SELECT 
                events_what, 
                events_type, 
                lower(events_when) as start_date, 
                upper(events_when) as end_date 
            FROM events 
            ORDER BY events_what, lower(events_when);
        """)
        
        events = cur.fetchall()
        print("\nEvent date ranges:")
        for event in events:
            category = event['events_what']
            event_type = event['events_type']
            start_date = event['start_date']
            end_date = event['end_date']
            
            # Check if start date is before current date
            start_before_current = start_date < CURRENT_DATE
            
            # Check if end date is far in the future
            days_in_future = (end_date - CURRENT_DATE).days
            
            print(f"  {category} ({event_type}):")
            print(f"    Start: {start_date} ({'before current date' if start_before_current else 'after current date'})")
            print(f"    End: {end_date} ({days_in_future} days in the future)")
            print()
        
    except Exception as e:
        print(f"Error: {e}")
        sys.exit(1)
    finally:
        if 'cur' in locals():
            cur.close()
        if 'conn' in locals():
            conn.close()

if __name__ == "__main__":
    main()