import csv
import psycopg2
import os
from dotenv import load_dotenv

# Load environment variables from .env file
load_dotenv()

# Get database connection
def get_database_connection():
    database_url = os.getenv("DATABASE_URL")
    if not database_url:
        raise ValueError("DATABASE_URL is not set in environment variables.")
    return psycopg2.connect(database_url)

# Verify data
def verify_data(row):
    # Check if necessary fields are present
    required_fields = ["Name", "About (description after screening)", "Skills (after screening)", "Rating", "Trust Score", "Ninja level(chunin ,join ,kage )", "Task Experience", "Online or not or schedule at this time each day"]
    for field in required_fields:
        if field not in row or not row[field]:
            return False
    
    # Check if rating and trust_score are within valid ranges
    try:
        rating = float(row["Rating"])
        if rating < 0 or rating > 5:
            return False
    except ValueError:
        return False
    
    try:
        trust_score = int(row["Trust Score"])
        if trust_score < 0 or trust_score > 100:
            return False
    except ValueError:
        return False
    
    return True

# Insert data into database
def insert_data(row):
    skills_array = row["Skills (after screening)"].split(",")
    skills_array = [skill.strip() for skill in skills_array]
    ninja_level = row["Ninja level(chunin ,join ,kage )"]
    
    conn = get_database_connection()
    try:
        with conn.cursor() as curs:
            curs.execute("""
                INSERT INTO gig_workers (name, about, skills, rating, trust_score, ninja_level, task_experience, online_status, profile_pic)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                RETURNING id
            """, (row["Name"], row["About (description after screening)"], skills_array, row["Rating"], row["Trust Score"], ninja_level, row["Task Experience"], row["Online or not or schedule at this time each day"], ''))
            worker_id = curs.fetchone()[0]
            conn.commit()
        print(f"Gig worker added with ID: {worker_id}")
    except Exception as e:
        conn.rollback()
        print(f"Error adding gig worker: {e}")
    finally:
        conn.close()

# Main function
def main():
    file_path = '/Users/vishal/WORK/DATA_GEN/synthetic_profiles_1000.csv'
    
    with open(file_path, newline='', encoding='utf-8') as csvfile:
        reader = csv.DictReader(csvfile)
        for row in reader:
            if verify_data(row):
                insert_data(row)
            else:
                print(f"Invalid data: {row}")

if __name__ == "__main__":
    main()