import pymongo
import sqlite3
from bson import ObjectId

# Connect to MongoDB
mongo_client = pymongo.MongoClient("mongodb://localhost:27017/")
mongo_db = mongo_client["vielfalter"]
surveys_collection = mongo_db["surveys"]
species_collection = mongo_db["species"]

# Connect to SQLite
sqlite_conn = sqlite3.connect("vielfalter.db")
cursor = sqlite_conn.cursor()

# Create surveys table
cursor.execute("""
CREATE TABLE IF NOT EXISTS surveys (
    id TEXT PRIMARY KEY,
    date TEXT,
    start TEXT,
    end TEXT,
    notes TEXT
)
""")

# Create speciesList table
cursor.execute("""
CREATE TABLE IF NOT EXISTS speciesList (
    survey_id TEXT,
    species_id TEXT,
    count INTEGER,
    FOREIGN KEY (survey_id) REFERENCES surveys(id)
)
""")

# Create sites table
cursor.execute("""
CREATE TABLE IF NOT EXISTS sites (
    id TEXT PRIMARY KEY,
    name TEXT,
    community TEXT,
    nuts2 TEXT
)
""")

# Create species table
cursor.execute("""
CREATE TABLE IF NOT EXISTS species (
    id TEXT PRIMARY KEY,
    german TEXT,
    latin TEXT
)
""")


# Function to insert or ignore site
def insert_site(site):
    cursor.execute(
        """
    INSERT OR IGNORE INTO sites (id, name, community, nuts2)
    VALUES (?, ?, ?, ?)
    """,
        (str(site["_id"]), site.get("name"), site.get("community"), site.get("nuts2")),
    )


# Export surveys and speciesList
for survey in surveys_collection.find():
    cursor.execute(
        """
    INSERT INTO surveys (id, date, start, end, notes)
    VALUES (?, ?, ?, ?, ?)
    """,
        (
            str(survey["_id"]),
            survey.get("date"),
            survey.get("start"),
            survey.get("end"),
            survey.get("notes"),
        ),
    )

    if "site" in survey:
        insert_site(survey["site"])

    if "speciesList" in survey:
        for species in survey["speciesList"]:
            cursor.execute(
                """
            INSERT INTO speciesList (survey_id, species_id, count)
            VALUES (?, ?, ?)
            """,
                (str(survey["_id"]), str(species["id"]), species.get("count")),
            )

# Export species
for species in species_collection.find():
    cursor.execute(
        """
    INSERT INTO species (id, german, latin)
    VALUES (?, ?, ?)
    """,
        (str(species["_id"]), species.get("german"), species.get("latin")),
    )

# Commit changes and close connections
sqlite_conn.commit()
sqlite_conn.close()
mongo_client.close()

print("Export completed successfully.")
