import csv
import os
from dotenv import load_dotenv
from neo4j import GraphDatabase

def main():
    # Load environment variables from .env file
    load_dotenv()

    print("Hello from cypher-runner!")

    # Read unallowed domains from CSV
    unallowed_domains = []
    with open('unallowed-domains.csv', 'r') as file:
        reader = csv.reader(file)
        next(reader)  # Skip header row
        for row in reader:
            if row:  # Skip empty rows
                unallowed_domains.append(row[0])

    print(f"Loaded {len(unallowed_domains)} unallowed domains")
    print("First few domains:", unallowed_domains[:5])

    # Create Neo4j driver
    uri = os.getenv('NEO4J_URL')
    user = os.getenv('NEO4J_USERNAME')
    password = os.getenv('NEO4J_PASSWORD')

    if not all([uri, user, password]):
        print("Missing required Neo4j environment variables")
        return

    driver = GraphDatabase.driver(uri, auth=(user, password))

    try:
        driver.verify_connectivity()
        print("Successfully connected to Neo4j database")
    except Exception as e:
        print(f"Failed to connect to Neo4j database: {e}")
        return

    # Query for each domain and accumulate results
    all_results = []
    query = """
    MATCH (i:Identity)-[:HAS_PROFILE]->(p:Profile)-[r:HAS_ACCESS_TO|HAS_ADMIN_ACCESS_TO]->(v:Vendor { id: '*'})
    WHERE i.email CONTAINS $domain
    RETURN i.email, p.profileType
    """

    print(f"Executing query for {len(unallowed_domains)} domains...")

    with driver.session() as session:
        for domain in unallowed_domains:
            try:
                result = session.run(query, {"domain": domain})
                records = [{"email": record["i.email"], "profileType": record["p.profileType"], "domain": domain}
                          for record in result]
                all_results.extend(records)
                if records:
                    print(f"Found {len(records)} results for domain: {domain}")
            except Exception as e:
                print(f"Error querying domain {domain}: {e}")

    print(f"Total results found: {len(all_results)}")

    # Export results to plain text formatted for Notion
    if all_results:
        output_file = 'unallowed_domains_results.txt'

        # Group results by domain
        results_by_domain = {}
        for result in all_results:
            domain = result['domain']
            if domain not in results_by_domain:
                results_by_domain[domain] = []
            results_by_domain[domain].append(result)

        with open(output_file, 'w') as f:
            # Write header
            f.write("domain\temail\tnotes\tremove or keep access\tUpdate ows-permissions?\n")

            # Write results grouped by domain
            for domain, records in results_by_domain.items():
                # First row shows domain name
                first_record = records[0]
                f.write(f"{domain}\t{first_record['email']} - {first_record['profileType']}\t\t\t\n")

                # Subsequent rows for the same domain (empty domain column)
                for record in records[1:]:
                    f.write(f"\t{record['email']} - {record['profileType']}\t\t\t\n")

        print(f"Results exported to {output_file} (formatted for Notion)")
    else:
        print("No results to export")

    driver.close()


if __name__ == "__main__":
    main()