import sqlite3
import random

# Connect to the SQLite database
conn = sqlite3.connect('Databases/ecobuddy.sqlite')
cursor = conn.cursor()

# Check if we need to add any new categories
cursor.execute("SELECT id FROM ecoCategories")
existing_categories = [row[0] for row in cursor.fetchall()]

# Add two new categories
new_categories = [
    (16, "Urban Farms"),
    (17, "Rainwater Harvesting Systems")
]

for category in new_categories:
    if category[0] not in existing_categories:
        cursor.execute("INSERT INTO ecoCategories (id, name) VALUES (?, ?)", category)
        print(f"Added new category: {category[1]}")

# Get list of user IDs for contributors
cursor.execute("SELECT id FROM ecoUser")
user_ids = [row[0] for row in cursor.fetchall()]

# Define 10 new ecological facilities in the UK with accurate location data
new_facilities = [
    {
        "title": "Community Garden Hackney",
        "category": 12,  # Pollinator Gardens
        "description": "Urban garden with native plants to support local pollinators",
        "houseNumber": "45",
        "streetName": "Dalston Lane",
        "county": "Greater London",
        "town": "London",
        "postcode": "E8 3AH",
        "lng": -0.0612,
        "lat": 51.5476,
        "contributor": random.choice(user_ids),
        "status_comments": [
            "Recently expanded with new wildflower section",
            "Volunteer days every Saturday"
        ]
    },
    {
        "title": "Rooftop Solar Farm",
        "category": 8,  # Green Roofs
        "description": "Combined green roof and solar panel installation on commercial building",
        "houseNumber": "120",
        "streetName": "Deansgate",
        "county": "Greater Manchester",
        "town": "Manchester",
        "postcode": "M3 2QJ",
        "lng": -2.2484,
        "lat": 53.4808,
        "contributor": random.choice(user_ids),
        "status_comments": [
            "Generates power for the entire building"
        ]
    },
    {
        "title": "Edinburgh Tool Library",
        "category": 15,  # Community Tool Libraries
        "description": "Borrow tools instead of buying them - reducing waste and consumption",
        "houseNumber": "25",
        "streetName": "Leith Walk",
        "county": "Edinburgh",
        "town": "Edinburgh",
        "postcode": "EH6 8LN",
        "lng": -3.1752,
        "lat": 55.9677,
        "contributor": random.choice(user_ids),
        "status_comments": []
    },
    {
        "title": "Cardiff Bay Water Refill Station",
        "category": 9,  # Public Water Refill Stations
        "description": "Free water refill station to reduce plastic bottle usage",
        "houseNumber": "3",
        "streetName": "Mermaid Quay",
        "county": "Cardiff",
        "town": "Cardiff",
        "postcode": "CF10 5BZ",
        "lng": -3.1644,
        "lat": 51.4644,
        "contributor": random.choice(user_ids),
        "status_comments": [
            "Recently cleaned and maintained",
            "High usage during summer months"
        ]
    },
    {
        "title": "Bristol Urban Farm",
        "category": 16,  # Urban Farms (new category)
        "description": "Community-run urban farm providing local produce and education",
        "houseNumber": "18",
        "streetName": "Stapleton Road",
        "county": "Bristol",
        "town": "Bristol",
        "postcode": "BS5 0RA",
        "lng": -2.5677,
        "lat": 51.4635,
        "contributor": random.choice(user_ids),
        "status_comments": [
            "Open for volunteers Tuesday-Sunday"
        ]
    },
    {
        "title": "Newcastle Rainwater Collection System",
        "category": 17,  # Rainwater Harvesting Systems (new category)
        "description": "Public demonstration of rainwater harvesting for garden irrigation",
        "houseNumber": "55",
        "streetName": "Northumberland Street",
        "county": "Tyne and Wear",
        "town": "Newcastle upon Tyne",
        "postcode": "NE1 7DH",
        "lng": -1.6178,
        "lat": 54.9783,
        "contributor": random.choice(user_ids),
        "status_comments": []
    },
    {
        "title": "Brighton Beach Solar Bench",
        "category": 7,  # Solar-Powered Benches
        "description": "Solar-powered bench with USB charging ports and WiFi",
        "houseNumber": "",
        "streetName": "Kings Road",
        "county": "East Sussex",
        "town": "Brighton",
        "postcode": "BN1 2FN",
        "lng": -0.1426,
        "lat": 50.8214,
        "contributor": random.choice(user_ids),
        "status_comments": [
            "Popular spot for tourists",
            "One USB port currently not working"
        ]
    },
    {
        "title": "Leeds Community Compost Hub",
        "category": 6,  # Community Compost Bins
        "description": "Large-scale community composting facility for local residents",
        "houseNumber": "78",
        "streetName": "Woodhouse Lane",
        "county": "West Yorkshire",
        "town": "Leeds",
        "postcode": "LS2 9JT",
        "lng": -1.5491,
        "lat": 53.8067,
        "contributor": random.choice(user_ids),
        "status_comments": [
            "Recently expanded capacity"
        ]
    },
    {
        "title": "Glasgow EV Charging Hub",
        "category": 4,  # Public EV Charging Stations
        "description": "Multi-vehicle EV charging station with fast chargers",
        "houseNumber": "42",
        "streetName": "Buchanan Street",
        "county": "Glasgow",
        "town": "Glasgow",
        "postcode": "G1 3JX",
        "lng": -4.2526,
        "lat": 55.8621,
        "contributor": random.choice(user_ids),
        "status_comments": [
            "6 charging points available",
            "24/7 access"
        ]
    },
    {
        "title": "Oxford E-Waste Collection Center",
        "category": 13,  # E-Waste Collection Bins
        "description": "Dedicated facility for proper disposal and recycling of electronic waste",
        "houseNumber": "15",
        "streetName": "St Aldate's",
        "county": "Oxfordshire",
        "town": "Oxford",
        "postcode": "OX1 1BX",
        "lng": -1.2577,
        "lat": 51.7520,
        "contributor": random.choice(user_ids),
        "status_comments": []
    }
]

# Insert facilities into the database
for facility in new_facilities:
    cursor.execute("""
        INSERT INTO ecoFacilities 
        (title, category, description, houseNumber, streetName, county, town, postcode, lng, lat, contributor)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        facility["title"],
        facility["category"],
        facility["description"],
        facility["houseNumber"],
        facility["streetName"],
        facility["county"],
        facility["town"],
        facility["postcode"],
        facility["lng"],
        facility["lat"],
        facility["contributor"]
    ))
    
    # Get the ID of the inserted facility
    facility_id = cursor.lastrowid
    
    # Add status comments if any
    for comment in facility["status_comments"]:
        cursor.execute("""
            INSERT INTO ecoFacilityStatus (facilityId, statusComment)
            VALUES (?, ?)
        """, (facility_id, comment))
    
    print(f"Added facility: {facility['title']} in {facility['town']}")

# Commit the changes and close the connection
conn.commit()
conn.close()

print("\nSuccessfully added 10 new ecological facilities to the database.")