import json
import pandas as pd

from Data.database.sql_connector import engine
from src.similarity_model.preprocessing import preprocess_dataset
from src.similarity_model.embedding_engine import (
    train_embedding_engine
)


def sync_projects():

    projects_query = """
    SELECT *
    FROM Projects
    WHERE Status IN (
        'Completed',
        'UnderReview',
        'In_Progress'
    )
    """

    projects_df = pd.read_sql(
        projects_query,
        engine
    )

    existing_df = pd.read_sql(
        """
        SELECT id
        FROM PreProcessed_Projects
        """,
        engine
    )

    allowed_ids = set(
        projects_df["Id"].tolist()
    )

    processed_ids = set(
        existing_df["id"].tolist()
    )

    # ---------------------------------
    # Remove projects no longer allowed
    # ---------------------------------

    ids_to_remove = (
        processed_ids - allowed_ids
    )

    changed = False

    if ids_to_remove:

        ids_str = ",".join(
            map(str, ids_to_remove)
        )

        with engine.begin() as conn:

            conn.exec_driver_sql(
                f"""
                DELETE FROM PreProcessed_Projects
                WHERE id IN ({ids_str})
                """
            )

        print(
            f"Removed {len(ids_to_remove)} projects"
        )

        changed = True

    # ---------------------------------
    # Add new projects
    # ---------------------------------

    new_projects = projects_df[
        ~projects_df["Id"].isin(
            processed_ids
        )
    ].copy()

    if len(new_projects) > 0:

        print(
            f"Found {len(new_projects)} new projects"
        )

        processed_df = preprocess_dataset(
            new_projects
        )

        processed_df = processed_df[
            [
                "id",
                "submittedat",
                "project_title",
                "studentnames",
                "year",
                "abstract",
                "description",
                "problemstatement",
                "proposedsolution",
                "objectives",
                "full_content",
                "clean_text",
                "word_count",
                "features"
            ]
        ]

        processed_df = processed_df.rename(
            columns={
                "submittedat": "submitted_at",
                "studentnames": "student_names",
                "problemstatement": "problem_statement",
                "proposedsolution": "proposed_solution"
            }
        )

        processed_df["features"] = (
            processed_df["features"]
            .apply(json.dumps)
        )

        processed_df.to_sql(
            "PreProcessed_Projects",
            engine,
            if_exists="append",
            index=False
        )

        print(
            f"Processed and inserted {len(processed_df)} projects"
        )

        changed = True

    else:

        print(
            "No new projects found"
        )

    # ---------------------------------
    # Rebuild FAISS only if changed
    # ---------------------------------

    if changed:

        print(
            "Updating embeddings..."
        )

        train_embedding_engine()

        print(
            "Embeddings updated"
        )

    else:

        print(
            "No changes detected"
        )


if __name__ == "__main__":

    sync_projects()