import json
import sqlite3


def migrate(conn: sqlite3.Connection):
    columns = conn.execute("PRAGMA table_info(posts)")
    column_names = [col[1] for col in columns]
    if "extra_data" not in column_names:
        _ = conn.execute("""
            ALTER TABLE posts
            ADD COLUMN extra_data TEXT NULL
        """)

    # migrate old bsky identifiers from json to uri as id and cid in extra_data
    data = conn.execute("SELECT id, identifier FROM posts WHERE service = 'https://bsky.app';").fetchall()
    rewrites: list[tuple[str, str, int]] = []
    for row in data:
        if row[1][0] == '{' and row[1][-1] == '}':
            data = json.loads(row[1])
            rewrites.append((data['uri'], json.dumps({'cid': data['cid']}), row[0]))
    if rewrites:
        _ = conn.executemany("UPDATE posts SET identifier = ?, extra_data = ? WHERE id = ?;", rewrites)