import sqlite3
import pandas as pd
from typing import Dict, Any, List

DB_PATH = "hackathon_participants.db"
TABLE_NAME = "participants"

def initialize_database():
    """
    Initializes the database and creates the participants table if it doesn't exist.
    """
    with sqlite3.connect(DB_PATH) as conn:
        cursor = conn.cursor()
        cursor.execute(f"""
            CREATE TABLE IF NOT EXISTS {TABLE_NAME} (
                email TEXT PRIMARY KEY,
                name TEXT NOT NULL,
                linkedin_profile TEXT,
                background TEXT,
                goals TEXT
            )
        """)
        conn.commit()

def add_participant(participant: Dict[str, Any]):
    """
    Adds a new participant to the database.

    Args:
        participant: A dictionary containing participant data.
                     Keys should be 'email', 'name', 'linkedin_profile', 'background', 'goals'.
    """
    with sqlite3.connect(DB_PATH) as conn:
        cursor = conn.cursor()
        cursor.execute(f"""
            INSERT OR REPLACE INTO {TABLE_NAME} (email, name, linkedin_profile, background, goals)
            VALUES (:email, :name, :linkedin_profile, :background, :goals)
        """, participant)
        conn.commit()

def get_participants_dataframe() -> pd.DataFrame:
    """
    Retrieves all participants from the database and returns them as a pandas DataFrame.

    Returns:
        A pandas DataFrame containing all participant data.
    """
    with sqlite3.connect(DB_PATH) as conn:
        df = pd.read_sql_query(f"SELECT * FROM {TABLE_NAME}", conn)
    return df

if __name__ == '__main__':
    # Example usage and basic test
    print("Initializing database...")
    initialize_database()
    print("Database initialized.")

    # Sample participants
    participants_to_add = [
        {
            "email": "alice@example.com",
            "name": "Alice Wonderland",
            "linkedin_profile": "linkedin.com/in/alicew",
            "background": "5 years of experience in frontend development with React and TypeScript. Interested in UI/UX design.",
            "goals": "I want to build a cool project for my portfolio and learn about backend development."
        },
        {
            "email": "bob@example.com",
            "name": "Bob Builder",
            "linkedin_profile": "linkedin.com/in/bobb",
            "background": "Backend developer specializing in Python, Django, and PostgreSQL. I'm also a DevOps enthusiast.",
            "goals": "Looking to work on a challenging problem, maybe involving infrastructure or data engineering."
        },
        {
            "email": "charlie@example.com",
            "name": "Charlie Chocolate",
            "linkedin_profile": "linkedin.com/in/charliec",
            "background": "Data scientist with expertise in Python, Pandas, Scikit-learn, and TensorFlow. I love creating predictive models.",
            "goals": "I hope to apply my machine learning skills to a real-world problem and collaborate with a diverse team."
        }
    ]

    print(f"Adding {len(participants_to_add)} participants...")
    for p in participants_to_add:
        add_participant(p)
    print("Participants added.")

    print("\nRetrieving all participants:")
    df = get_participants_dataframe()
    print(df)
    
    # Verify data
    assert len(df) == 3
    assert "alice@example.com" in df["email"].values
    print("\nData verified successfully.")