import unittest
import os
import pandas as pd
import sys
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from database import initialize_database, add_participant, get_participants_dataframe

# Use a separate test database
TEST_DB_PATH = "test_hackathon.db"

class TestDatabase(unittest.TestCase):

    @classmethod
    def setUpClass(cls):
        """Set up a test database before any tests run."""
        cls.db_path = TEST_DB_PATH
        # Point the database module to the test DB
        import database
        database.DB_PATH = cls.db_path

    def setUp(self):
        """Initialize a clean database before each test."""
        if os.path.exists(self.db_path):
            os.remove(self.db_path)
        initialize_database()

    def tearDown(self):
        """Remove the database file after each test."""
        if os.path.exists(self.db_path):
            os.remove(self.db_path)

    def test_01_initialize_database(self):
        """Test that the database and table are created."""
        self.assertTrue(os.path.exists(self.db_path))
        df = get_participants_dataframe()
        self.assertIsInstance(df, pd.DataFrame)
        self.assertEqual(len(df), 0)

    def test_02_add_and_get_participant(self):
        """Test adding a single participant and retrieving it."""
        participant = {
            "email": "test@example.com",
            "name": "Test User",
            "linkedin_profile": "linkedin.com/in/test",
            "background": "Testing background.",
            "goals": "Testing goals."
        }
        add_participant(participant)

        df = get_participants_dataframe()
        self.assertEqual(len(df), 1)
        
        retrieved = df.iloc[0]
        self.assertEqual(retrieved["email"], participant["email"])
        self.assertEqual(retrieved["name"], participant["name"])

    def test_03_add_multiple_participants(self):
        """Test adding multiple participants."""
        participants_to_add = [
            {"email": "user1@example.com", "name": "User One", "linkedin_profile": "", "background": "", "goals": ""},
            {"email": "user2@example.com", "name": "User Two", "linkedin_profile": "", "background": "", "goals": ""},
        ]
        for p in participants_to_add:
            add_participant(p)

        df = get_participants_dataframe()
        self.assertEqual(len(df), 2)
        self.assertIn("user1@example.com", df["email"].values)
        self.assertIn("user2@example.com", df["email"].values)

    def test_04_replace_participant(self):
        """Test that adding a participant with an existing email updates the record."""
        original_participant = {
            "email": "replace@example.com",
            "name": "Original Name",
            "linkedin_profile": "original_linkedin",
            "background": "original_background",
            "goals": "original_goals"
        }
        add_participant(original_participant)

        df_before = get_participants_dataframe()
        self.assertEqual(len(df_before), 1)
        self.assertEqual(df_before.iloc[0]["name"], "Original Name")

        updated_participant = {
            "email": "replace@example.com",
            "name": "Updated Name",
            "linkedin_profile": "updated_linkedin",
            "background": "updated_background",
            "goals": "updated_goals"
        }
        add_participant(updated_participant)

        df_after = get_participants_dataframe()
        self.assertEqual(len(df_after), 1)
        self.assertEqual(df_after.iloc[0]["name"], "Updated Name")
        self.assertEqual(df_after.iloc[0]["linkedin_profile"], "updated_linkedin")

if __name__ == '__main__':
    unittest.main()