import sqlite3


def migrate(conn: sqlite3.Connection):
    cursor = conn.cursor()

    old_posts = cursor.execute("SELECT * FROM posts;").fetchall()
    old_mappings = cursor.execute("SELECT * FROM mappings;").fetchall()

    _ = cursor.execute("DROP TABLE posts;")
    _ = cursor.execute("DROP TABLE mappings;")

    _ = cursor.execute("""
        CREATE TABLE posts (
            id          INTEGER UNIQUE PRIMARY KEY AUTOINCREMENT,
            user        TEXT NOT NULL,
            service     TEXT NOT NULL,
            identifier  TEXT NOT NULL,
            parent      INTEGER NULL REFERENCES posts(id),
            root        INTEGER NULL REFERENCES posts(id),
            reposted    INTEGER NULL REFERENCES posts(id),
            extra_data  TEXT NULL
        );
    """)

    _ = cursor.execute("""
        CREATE TABLE mappings (
            original INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
            mapped   INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
            UNIQUE(original, mapped)
        );
    """)

    for old_post in old_posts:
        _ = cursor.execute(
            """
            INSERT INTO posts (id, user, service, identifier, parent, root, reposted, extra_data)
            VALUES (:id, :user_id, :service, :identifier, :parent_id, :root_id, :reposted_id, :extra_data)
        """,
            dict(old_post),
        )

    for mapping in old_mappings:
        original, mapped = mapping["original_post_id"], mapping["mapped_post_id"]
        _ = cursor.execute(
            "INSERT OR IGNORE INTO mappings (original, mapped) VALUES (?, ?)",
            (original, mapped),
        )
        _ = cursor.execute(
            "INSERT OR IGNORE INTO mappings (original, mapped) VALUES (?, ?)",
            (mapped, original),
        )