import sqlite3
import hashlib
from pymongo import MongoClient
from bson import ObjectId


def safe_str(value):
    if isinstance(value, ObjectId):
        return str(value)
    elif isinstance(value, list):
        return ", ".join(str(v) for v in value)
    elif isinstance(value, dict):
        return ", ".join(f"{k}: {v}" for k, v in value.items())
    return str(value)


def hash_dict(d):
    return hashlib.md5(str(sorted(d.items())).encode()).hexdigest()


def export_collection(
    mongo_collection, sqlite_table_name, cursor, embedded_doc_key=None
):
    first_doc = mongo_collection.find_one()
    if not first_doc:
        print(f"No documents found in {sqlite_table_name} collection.")
        return

    headers = [
        k for k, v in first_doc.items() if not isinstance(v, (list, dict)) or k == "_id"
    ]

    # Create table
    create_table_query = f"""
    CREATE TABLE {sqlite_table_name} (
        {', '.join([f'{header} TEXT' for header in headers])}
        {f', {embedded_doc_key}_id TEXT' if embedded_doc_key else ''}
    )
    """
    cursor.execute(create_table_query)

    # Insert data
    insert_query = f"""
    INSERT INTO {sqlite_table_name} ({', '.join(headers + ([f'{embedded_doc_key}_id'] if embedded_doc_key else []))})
    VALUES ({', '.join(['?' for _ in range(len(headers) + (1 if embedded_doc_key else 0))])})
    """
    for doc in mongo_collection.find():
        row = [safe_str(doc.get(header)) for header in headers]
        if embedded_doc_key:
            embedded_doc = doc.get(embedded_doc_key, {})
            if isinstance(embedded_doc, dict):
                embedded_doc_id = hash_dict(embedded_doc)
            else:
                embedded_doc_id = ""
            row.append(embedded_doc_id)
        cursor.execute(insert_query, row)

    # Process embedded document if exists
    if embedded_doc_key:
        process_embedded_doc(mongo_collection, embedded_doc_key, cursor)

    # Process arrays
    array_fields = [k for k, v in first_doc.items() if isinstance(v, list)]
    for array_field in array_fields:
        process_array_field(mongo_collection, array_field, cursor)


def process_embedded_doc(mongo_collection, embedded_doc_key, cursor):
    embedded_docs = {}
    for doc in mongo_collection.find():
        embedded_doc = doc.get(embedded_doc_key, {})
        if isinstance(embedded_doc, dict):
            embedded_doc_id = hash_dict(embedded_doc)
            if embedded_doc_id not in embedded_docs:
                embedded_docs[embedded_doc_id] = embedded_doc

    if embedded_docs:
        all_keys = set()
        for embedded_doc in embedded_docs.values():
            all_keys.update(embedded_doc.keys())

        create_embedded_table_query = f"""
        CREATE TABLE {embedded_doc_key} (
            id TEXT PRIMARY KEY,
            {', '.join([f'{k} TEXT' for k in sorted(all_keys)])}
        )
        """
        cursor.execute(create_embedded_table_query)

        insert_embedded_query = f"""
        INSERT INTO {embedded_doc_key} (id, {', '.join(sorted(all_keys))})
        VALUES ({', '.join(['?' for _ in range(len(all_keys) + 1)])})
        """
        for embedded_doc_id, embedded_doc in embedded_docs.items():
            row = [embedded_doc_id] + [
                safe_str(embedded_doc.get(k, "")) for k in sorted(all_keys)
            ]
            cursor.execute(insert_embedded_query, row)


def process_array_field(mongo_collection, array_field, cursor):
    all_keys = set()
    for doc in mongo_collection.find():
        for item in doc.get(array_field, []):
            if isinstance(item, dict):
                all_keys.update(item.keys())

    create_array_table_query = f"""
    CREATE TABLE {array_field} (
        parent_id TEXT,
        {', '.join([f'{k} TEXT' for k in sorted(all_keys)])}
    )
    """
    cursor.execute(create_array_table_query)

    insert_array_query = f"""
    INSERT INTO {array_field} (parent_id, {', '.join(sorted(all_keys))})
    VALUES ({', '.join(['?' for _ in range(len(all_keys) + 1)])})
    """
    for doc in mongo_collection.find():
        for item in doc.get(array_field, []):
            if isinstance(item, dict):
                row = [safe_str(doc["_id"])] + [
                    safe_str(item.get(k, "")) for k in sorted(all_keys)
                ]
            else:
                row = (
                    [safe_str(doc["_id"])]
                    + [safe_str(item)]
                    + [""] * (len(all_keys) - 1)
                )
            cursor.execute(insert_array_query, row)


# Connect to MongoDB
client = MongoClient("mongodb://localhost:27017")
db = client["your_database"]

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

# Export surveys collection (previously main_collection)
surveys_collection = db["your_surveys_collection"]
export_collection(surveys_collection, "surveys", cursor, "your_embedded_document_key")

# Export species collection
species_collection = db["species"]
export_collection(species_collection, "species", cursor)

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

print("Export to SQLite completed.")
