import os
from dotenv import load_dotenv
from neo4j import GraphDatabase

# Load environment variables from .env file
load_dotenv()

# Get Neo4j connection info from environment variables
NEO4J_URI = os.getenv('NEO4J_URI')
NEO4J_USER = os.getenv('NEO4J_USER')
NEO4J_PASSWORD = os.getenv('NEO4J_PASSWORD')

# Function to connect to the Neo4j database and fetch the "sponsors.0.bioguideId" property of all Bill nodes
def get_sponsors_bioguide_id(uri, user, password):
    driver = GraphDatabase.driver(uri, auth=(user, password))

    with driver.session() as session:
        # Cypher query to get all Bill nodes and match Person node with the same bioguideId
        query = """
        MATCH (b:Bill)
        WITH b, b.`sponsors.0.bioguideId` AS bioguideId
        OPTIONAL MATCH (p:Person {bioguideId: bioguideId})
        WHERE NOT EXISTS((b)-[:SPONSORED]->(p))
        RETURN b.`sponsors.0.bioguideId` AS sponsorBioguideId, p
        """

        result = session.run(query)

        for record in result:
            sponsor_bioguide_id = record['sponsorBioguideId']
            matched_person = record['p']

            # Print the value of sponsors.0.bioguideId and the matched Person node
            # print(f"Value of sponsors.0.bioguideId: {sponsor_bioguide_id}")
            if matched_person:
                person_properties = matched_person.items()
                print("Matched Person Node:")
                for key, value in person_properties:
                    print(f"{key}: {value}")

                # Create the SPONSORED relationship
                create_relationship_query = """
                MATCH (b:Bill), (p:Person {bioguideId: $bioguideId})
                WHERE b.`sponsors.0.bioguideId` = $sponsorBioguideId
                CREATE (p)-[:SPONSORED]->(b)
                """
                session.run(create_relationship_query, bioguideId=sponsor_bioguide_id, sponsorBioguideId=sponsor_bioguide_id)
                print("Created SPONSORED relationship.")
            else:
                continue # print("No matching Person node found.")

    driver.close()

# Call the function with your connection info
get_sponsors_bioguide_id(NEO4J_URI, NEO4J_USER, NEO4J_PASSWORD)