import gradio as gr
import psycopg2
import pandas as pd
import os
import uuid
from datetime import datetime, timedelta
from dotenv import load_dotenv

# --- Constants and Configuration ---
load_dotenv() # Load environment variables from .env file

SUPABASE_PASSWORD = os.getenv("SUPABASE_PASSWORD")
DB_URL = f"postgresql://postgres.jzvflgshkmpffbefmvdh:{SUPABASE_PASSWORD}@aws-0-eu-north-1.pooler.supabase.com:6543/postgres"
BOOKING_DURATION_HOURS = 1.5

# --- Database Management ---

def get_db_connection():
    """Establishes and returns a connection to the PostgreSQL database."""
    return psycopg2.connect(DB_URL)

def initialize_db():
    """Initializes the database, creating tables for clubs, bookings, and performances if they don't exist, and populates them with sample data."""
    with get_db_connection() as con:
        with con.cursor() as cur:
            # Create clubs table
            cur.execute("""
                CREATE TABLE IF NOT EXISTS clubs (
                    club_id SERIAL PRIMARY KEY,
                    name TEXT NOT NULL,
                    city TEXT NOT NULL,
                    number_of_courts INTEGER NOT NULL
                )
            """)
            # Create bookings table
            cur.execute("""
                CREATE TABLE IF NOT EXISTS bookings (
                    id SERIAL PRIMARY KEY,
                    reservation_number TEXT NOT NULL UNIQUE,
                    club_id INTEGER NOT NULL REFERENCES clubs(club_id),
                    court_id INTEGER NOT NULL,
                    player1 TEXT NOT NULL,
                    player2 TEXT NOT NULL,
                    player3 TEXT NOT NULL,
                    player4 TEXT NOT NULL,
                    date DATE NOT NULL,
                    time TIME NOT NULL,
                    end_time TIME NOT NULL
                )
            """)
            # Create performances table
            cur.execute("""
                CREATE TABLE IF NOT EXISTS performances (
                    id SERIAL PRIMARY KEY,
                    booking_id INTEGER NOT NULL REFERENCES bookings(id),
                    set1_score TEXT NOT NULL,
                    set2_score TEXT NOT NULL,
                    set3_score TEXT,
                    description TEXT,
                    player_comments TEXT
                )
            """)
            # Pre-populate the clubs table
            cur.execute("SELECT COUNT(*) FROM clubs")
            if cur.fetchone()[0] == 0:
                french_clubs = [
                    ('4Padel Bordeaux', 'Bordeaux', 8),
                    ('Padel Attitude', 'Lille', 6),
                    ('Esprit Padel', 'Lyon', 10),
                    ('Casa Padel', 'Paris', 12),
                    ('Toulouse Padel Club', 'Toulouse', 9),
                    ('4Padel Strasbourg', 'Strasbourg', 5),
                    ('UrbanPadel Nantes', 'Nantes', 4),
                    ('Le Padel Club', 'Marseille', 7)
                ]
                cur.executemany("INSERT INTO clubs (name, city, number_of_courts) VALUES (%s, %s, %s)", french_clubs)

            # Pre-populate the bookings table
            cur.execute("SELECT COUNT(*) FROM bookings")
            if cur.fetchone()[0] == 0:
                sample_bookings = [
                    (str(uuid.uuid4().hex)[:12], 1, 1, 'Player A', 'Player B', 'Player C', 'Player D', '2025-07-26', '10:00:00', '11:30:00'),
                    (str(uuid.uuid4().hex)[:12], 2, 3, 'Player E', 'Player F', 'Player G', 'Player H', '2025-07-27', '14:00:00', '15:30:00')
                ]
                cur.executemany("INSERT INTO bookings (reservation_number, club_id, court_id, player1, player2, player3, player4, date, time, end_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", sample_bookings)
        con.commit()

# --- Core Logic ---

def list_clubs():
    """Retrieves and lists all padel clubs from the database."""
    try:
        with get_db_connection() as con:
            df = pd.read_sql_query("SELECT name, city, number_of_courts FROM clubs ORDER BY city", con)
            return df
    except psycopg2.Error as e:
        return pd.DataFrame(columns=['name', 'city', 'number_of_courts'])

def get_club_names():
    """Gets a list of club names for the booking dropdown."""
    try:
        with get_db_connection() as con:
            with con.cursor() as cur:
                cur.execute("SELECT name FROM clubs ORDER BY name")
                return [row[0] for row in cur.fetchall()]
    except psycopg2.Error:
        return []

def add_booking(club_name, court_id, player1, player2, player3, player4, date, time):
    """Handles the logic for adding a new booking."""
    # 1. Validate inputs
    if not club_name or not club_name.strip():
        return "Error: Club selection is required."
    if court_id is None:
        return "Error: Court ID is required."
    try:
        court_id = int(court_id)
    except (ValueError, TypeError):
        return "Error: Court ID must be a valid number."

    player_names = [player1, player2, player3, player4]
    if not all(p and p.strip() for p in player_names):
        return "Error: All four player names are required."
    if not date or not date.strip() or not time or not time.strip():
        return "Error: Date and Time are required."

    # 2. Validate that the booking is for a future time.
    try:
        start_datetime = datetime.strptime(f"{date.strip()} {time.strip()}", "%Y-%m-%d %H:%M")
        if start_datetime < datetime.now():
            return "Error: Booking date and time must be in the future."
    except ValueError:
        return "Error: Invalid date or time format. Use YYYY-MM-DD and HH:MM."

    # 3. Calculate the booking's end time.
    end_datetime = start_datetime + timedelta(hours=BOOKING_DURATION_HOURS)
    end_time_str = end_datetime.strftime("%H:%M")

    reservation_number = str(uuid.uuid4().hex)[:12]

    try:
        with get_db_connection() as con:
            with con.cursor() as cur:
                # Get club_id and max_courts
                cur.execute("SELECT club_id, number_of_courts FROM clubs WHERE name = %s", (club_name,))
                club_data = cur.fetchone()
                if not club_data:
                    return "Error: Selected club not found."
                club_id, max_courts = club_data

                if not 1 <= int(court_id) <= max_courts:
                    return f"Error: Invalid Court ID for {club_name}. Please choose a court from 1 to {max_courts}."

                # Availability Check
                cur.execute("""
                    SELECT * FROM bookings
                    WHERE club_id = %s AND court_id = %s AND date = %s AND (
                        (%s::TIME, %s::TIME) OVERLAPS (time, end_time)
                    )
                """, (club_id, court_id, date, time, end_time_str))
                if cur.fetchone():
                    return f"Error: Court {court_id} is already booked for the selected time slot."

                # Insert Booking
                cur.execute("""
                    INSERT INTO bookings (reservation_number, club_id, court_id, player1, player2, player3, player4, date, time, end_time)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (reservation_number, club_id, court_id, player1, player2, player3, player4, date, time, end_time_str))
            con.commit()
        return f"Booking added successfully! Your reservation number is {reservation_number}."
    except psycopg2.Error as e:
        return f"Database error: {e}"

def list_bookings():
    """Retrieves and lists all current bookings."""
    try:
        with get_db_connection() as con:
            query = """
                SELECT b.id, b.reservation_number, c.name as club, b.court_id, b.player1, b.player2, b.player3, b.player4, b.date, b.time, b.end_time
                FROM bookings b
                JOIN clubs c ON b.club_id = c.club_id
                ORDER BY b.date, b.time
            """
            df = pd.read_sql_query(query, con)
            return "Current Bookings:", df
    except psycopg2.Error as e:
        return f"Database error: {e}", pd.DataFrame()

def remove_booking(reservation_number):
    """Removes a booking from the database by its unique reservation number."""
    if not reservation_number or not reservation_number.strip():
        return "Error: Reservation number is required."
    try:
        with get_db_connection() as con:
            with con.cursor() as cur:
                cur.execute("DELETE FROM bookings WHERE reservation_number = %s RETURNING id", (reservation_number.strip(),))
                deleted_id = cur.fetchone()
                con.commit()
                if deleted_id:
                    return f"Booking with reservation number {reservation_number} removed successfully."
                else:
                    return f"Error: No booking found with reservation number {reservation_number}."
    except psycopg2.Error as e:
        return f"Database error: {e}"

def add_performance(reservation_number, set1_score, set2_score, set3_score, description, player_comments):
    """Adds a performance record for a given booking."""
    if not reservation_number or not reservation_number.strip():
        return "Error: Reservation number is required."
    try:
        with get_db_connection() as con:
            with con.cursor() as cur:
                cur.execute("SELECT id FROM bookings WHERE reservation_number = %s", (reservation_number.strip(),))
                booking_id = cur.fetchone()
                if not booking_id:
                    return f"Error: No booking found with reservation number {reservation_number}."
                booking_id = booking_id[0]

                cur.execute("""
                    INSERT INTO performances (booking_id, set1_score, set2_score, set3_score, description, player_comments)
                    VALUES (%s, %s, %s, %s, %s, %s)
                """, (booking_id, set1_score, set2_score, set3_score, description, player_comments))
            con.commit()
        return "Performance record added successfully."
    except psycopg2.Error as e:
        return f"Database error: {e}"

def list_performances():
    """Retrieves and lists all performance records."""
    try:
        with get_db_connection() as con:
            query = """
                SELECT p.id, b.reservation_number, p.set1_score, p.set2_score, p.set3_score, p.description, p.player_comments
                FROM performances p
                JOIN bookings b ON p.booking_id = b.id
                ORDER BY b.date, b.time
            """
            df = pd.read_sql_query(query, con)
            return "Performance Records:", df
    except psycopg2.Error as e:
        return f"Database error: {e}", pd.DataFrame()

# --- UI Theming and Layout ---
padel_theme = gr.themes.Base(
    primary_hue=gr.themes.colors.blue,
    secondary_hue=gr.themes.colors.green,
    neutral_hue=gr.themes.colors.gray,
    font=gr.themes.GoogleFont("Roboto"),
).set(
    body_background_fill="#f0f2f5",
    block_background_fill="#ffffff",
    block_border_width="1px",
    block_border_color="#e0e0e0",
    block_title_background_fill="*primary_200",
    block_title_text_color="*primary_800",
    button_primary_background_fill="*primary_500",
    button_primary_text_color="#ffffff",
    button_secondary_background_fill="*secondary_200",
    button_secondary_text_color="*secondary_800",
    input_background_fill="#f9f9f9",
)

# --- Gradio Interface Definition ---
def build_ui():
    """Constructs the Gradio user interface."""
    with gr.Blocks(theme=padel_theme, title="Padel Court Booking") as demo:
        gr.Markdown("""
        <div style="text-align: center; font-size: 2.5em; font-weight: bold; color: #0b57d0;">
            🎾 Padel Court Booking System 🎾
        </div>
        """)

        with gr.Tabs():
            with gr.TabItem("Clubs"):
                with gr.Column(variant="panel"):
                    club_list_df = gr.DataFrame(label="Available Padel Clubs")
                    refresh_clubs_btn = gr.Button("Refresh Club List")
                    refresh_clubs_btn.click(fn=list_clubs, outputs=club_list_df)

            with gr.TabItem("List Bookings"):
                with gr.Column(variant="panel"):
                    list_output_text = gr.Textbox(label="Status", interactive=False)
                    list_output_df = gr.DataFrame(label="Current Bookings")
                    list_btn = gr.Button("🔄 Refresh Bookings", variant="primary")
                    list_btn.click(fn=list_bookings, outputs=[list_output_text, list_output_df])

            with gr.TabItem("Add Booking"):
                with gr.Column(variant="panel"):
                    club_dropdown = gr.Dropdown(label="Select a Club", choices=get_club_names())
                    add_court_id = gr.Number(label="Court ID", precision=0, min_width=50)
                    add_date = gr.Textbox(label="Date", placeholder="YYYY-MM-DD")
                    add_time = gr.Textbox(label="Start Time", placeholder="HH:MM")
                    gr.Markdown("**Player Information**")
                    with gr.Row():
                        add_player1 = gr.Textbox(label="Player 1", placeholder="Full Name")
                        add_player2 = gr.Textbox(label="Player 2", placeholder="Full Name")
                    with gr.Row():
                        add_player3 = gr.Textbox(label="Player 3", placeholder="Full Name")
                        add_player4 = gr.Textbox(label="Player 4", placeholder="Full Name")
                    add_btn = gr.Button("✅ Add Booking", variant="primary")
                    add_output = gr.Textbox(label="Status", interactive=False)
                    add_btn.click(
                        fn=add_booking,
                        inputs=[club_dropdown, add_court_id, add_player1, add_player2, add_player3, add_player4, add_date, add_time],
                        outputs=add_output
                    )

            with gr.TabItem("Remove Booking"):
                with gr.Column(variant="panel"):
                    gr.Markdown("Enter the 12-character reservation number to remove a booking.")
                    remove_id = gr.Textbox(label="Reservation Number", placeholder="e.g., 9a3b7c1d5e0f")
                    remove_btn = gr.Button("🗑️ Remove Booking", variant="stop")
                    remove_output = gr.Textbox(label="Status", interactive=False)
                    remove_btn.click(fn=remove_booking, inputs=remove_id, outputs=remove_output)

            with gr.TabItem("Add Performance"):
                with gr.Column(variant="panel"):
                    gr.Markdown("Enter the reservation number and performance details for a past match.")
                    add_perf_res_num = gr.Textbox(label="Reservation Number", placeholder="e.g., 9a3b7c1d5e0f")
                    add_perf_set1 = gr.Textbox(label="Set 1 Score", placeholder="e.g., 6-2")
                    add_perf_set2 = gr.Textbox(label="Set 2 Score", placeholder="e.g., 7-5")
                    add_perf_set3 = gr.Textbox(label="Set 3 Score (optional)", placeholder="e.g., 6-4")
                    add_perf_desc = gr.Textbox(label="Match Description", placeholder="A brief summary of the match.")
                    add_perf_comments = gr.Textbox(label="Player Comments", placeholder="Comments on player performances.")
                    add_perf_btn = gr.Button("Add Performance", variant="primary")
                    add_perf_output = gr.Textbox(label="Status", interactive=False)
                    add_perf_btn.click(
                        fn=add_performance,
                        inputs=[add_perf_res_num, add_perf_set1, add_perf_set2, add_perf_set3, add_perf_desc, add_perf_comments],
                        outputs=add_perf_output
                    )

            with gr.TabItem("List Performances"):
                with gr.Column(variant="panel"):
                    list_perf_output_text = gr.Textbox(label="Status", interactive=False)
                    list_perf_output_df = gr.DataFrame(label="Performance Records")
                    list_perf_btn = gr.Button("Refresh Performances", variant="primary")
                    list_perf_btn.click(fn=list_performances, outputs=[list_perf_output_text, list_perf_output_df])

        # Set initial data on load
        demo.load(list_clubs, outputs=club_list_df)
        demo.load(list_bookings, outputs=[list_output_text, list_output_df])
        demo.load(list_performances, outputs=[list_perf_output_text, list_perf_output_df])

        return demo

# --- Application Entry Point ---
if __name__ == "__main__":
    initialize_db()
    app = build_ui()
    app.launch(mcp_server=True)