from pymongo import MongoClient
import sqlite3
import json
from datetime import datetime
import requests

# Connect to SQLite
sqlite_conn = sqlite3.connect('sbsheriff.sqlite')
sqlite_conn.row_factory = sqlite3.Row
sqlite_cursor = sqlite_conn.cursor()

# Connect to MongoDB
mongo_client = MongoClient('mongodb://sheriff:unnipus1213@192.168.1.109:27017/?retryWrites=true&serverSelectionTimeoutMS=5000&connectTimeoutMS=10000&authSource=stormbrigade&authMechanism=SCRAM-SHA-256')
mongo_db = mongo_client['stormbrigade']
mongo_collection = mongo_db['intel_screenshots']

# Fetch data from SQLite table
sqlite_cursor.execute("SELECT gID, name, castle_designs, attack_designs FROM players")
rows = sqlite_cursor.fetchall()


# Iterate over the rows and migrate data to MongoDB
for row in rows:

    username = row['name']
    guild_id = row['gID']
    castle_design_json = row['castle_designs']
    attack_design_json = row['attack_designs']

    # Convert house_history JSON to Python dictionary
    castle_dict = json.loads(castle_design_json)
    attack_dict = json.loads(attack_design_json)
    #print(castle_dict)
    # Iterate over house_history elements and upsert to MongoDB

    player_doc = mongo_db.players.find_one(filter={"in_game_name": {"$regex": f"^{username}$", "$options": "i"}})
    if player_doc is None:
        player_doc = {}
        r = requests.get(f'http://login.strongholdkingdoms.com/ajaxphp/username_search.php?term={username}').json()
        if len(r) > 0:
            player_doc["in_game_name"] = r[0]
        else:
            print(username, len(castle_dict))
            continue
    #print(player_doc)

    for filename in castle_dict.keys():
        filter_query = {
            "in_game_name": player_doc["in_game_name"],
            "added_by" : {
                "added_by_discord_id": 0,
                "added_by_discord_name": "Old Submission",
                "added_by_discord_server_id": 947398173805133834,
            },
            "type": "castle",
            "filename": filename
        }
        update_query = {
            "$set": {
                "filename": filename
            }
        }
        mongo_collection.update_one(filter_query, update_query, upsert=True)
    for filename in attack_dict.keys():
        filter_query = {
            "in_game_name": player_doc["in_game_name"],
            "added_by" : {
                "added_by_discord_id": 0,
                "added_by_discord_name": "Old Submission",
                "added_by_discord_server_id": 947398173805133834,
            },
            "type": "attack",
            "filename": filename
        }
        update_query = {
            "$set": {
                "filename": filename
            }
        }
        mongo_collection.update_one(filter_query, update_query, upsert=True)
        # Perform the upsert operation
        

# Close connections
sqlite_cursor.close()
sqlite_conn.close()
mongo_client.close()