import sqlite3
import psycopg2
import psycopg2.extras

SQLITE_DB_PATH = 'path_to_your_sqlite.db'
POSTGRES_CONFIG = {
    'host': 'localhost',
    'port': 5432,
    'dbname': 'your_db',
    'user': 'your_user',
    'password': 'your_password',
}

def fetch_all_sqlite(cursor, table):
    cursor.execute(f"SELECT * FROM {table}")
    return cursor.fetchall(), [desc[0] for desc in cursor.description]

def main():
    sqlite_conn = sqlite3.connect(SQLITE_DB_PATH)
    sqlite_conn.row_factory = sqlite3.Row
    sqlite_cur = sqlite_conn.cursor()

    pg_conn = psycopg2.connect(**POSTGRES_CONFIG)
    pg_cur = pg_conn.cursor()

    try:
        # --- DOCUMENTS ---
        pg_cur.execute("DELETE FROM documents")
        rows, columns = fetch_all_sqlite(sqlite_cur, "documents")
        for row in rows:
            pg_cur.execute("""
                INSERT INTO documents (id, ts, table_id, json_value, deleted, prev_ts)
                VALUES (%s, %s, %s, %s, %s, %s)
            """, (
                row["id"],
                row["ts"],
                row["table_id"],
                row["json_value"].encode("utf-8") if row["json_value"] else b'',
                bool(row["deleted"]),
                row["prev_ts"],
            ))

        # --- INDEXES ---
        pg_cur.execute("DELETE FROM indexes")
        rows, columns = fetch_all_sqlite(sqlite_cur, "indexes")
        for row in rows:
            pg_cur.execute("""
                INSERT INTO indexes (index_id, ts, key_prefix, key_sha256, deleted, table_id, document_id)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
            """, (
                row["index_id"],
                row["ts"],
                row["key"],
                row["key"],  # using 'key' for key_sha256 as well (you may customize this logic)
                bool(row["deleted"]),
                row["table_id"],
                row["document_id"],
            ))

        # --- PERSISTENCE_GLOBALS ---
        pg_cur.execute("DELETE FROM persistence_globals")
        rows, columns = fetch_all_sqlite(sqlite_cur, "persistence_globals")
        for row in rows:
            pg_cur.execute("""
                INSERT INTO persistence_globals (key, json_value)
                VALUES (%s, %s)
            """, (
                row["key"],
                row["json_value"].encode("utf-8"),
            ))

        # --- READ_ONLY ---
        pg_cur.execute("DELETE FROM read_only")
        rows, columns = fetch_all_sqlite(sqlite_cur, "read_only")
        for row in rows:
            pg_cur.execute("""
                INSERT INTO read_only (id)
                VALUES (%s)
            """, (
                row["id"],
            ))

        pg_conn.commit()
        print("Data transfer completed successfully.")
    except Exception as e:
        pg_conn.rollback()
        print("Error:", e)
    finally:
        sqlite_conn.close()
        pg_conn.close()

if __name__ == "__main__":
    main()