#!/usr/bin/env bun

import { SQL } from "bun";

const lb = new SQL("sqlite://./plugins/LogBlock/log.db");
const co = new SQL("sqlite://./plugins/CoreProtect/database.db");

const lbLogs = <LBBlockLog[]>await lb`SELECT * FROM block_log ORDER BY id ASC`;

interface LBBlockLog {
    id: number;
    timestamp: number; // long of time millis

    player: string; // uuid
    action: "BREAK" | "PLACE";

    world: string; // world name
    x: number;
    y: number;
    z: number;

    block_type: string; // bukkit material string
}

// co_user
interface COUser {
    id: number;
    user: string; // username
    uuid: string;
}

// co_world
interface COWorld {
    id: number;
    world: string;
}

// co_material_map
interface COMaterialMap {
    id: number;
    material: string; // minecraft:namespaced_material_id
}

// co_block
interface COBlock {
    time: number;

    user: number; // we store user id as number
    wid: number; // we store world id as number
    type: number; // we store material id from co_material_map

    x: number;
    y: number;
    z: number;

    action: number; // interaction (we only have place/break, respectively 1/0)
    data: 0;
    rolled_back: 0;
}

const coNewUsers: COUser[] = [];
const coNewWorlds: COWorld[] = [];
const coNewMaterials: COMaterialMap[] = [];

const userStartId =
    (await co`SELECT id FROM co_user ORDER BY id DESC LIMIT 1`)?.[0]?.id ?? 0;
const worldStartId =
    (await co`SELECT id FROM co_world ORDER BY id DESC LIMIT 1`)?.[0]?.id ?? 0;
const materialStartId =
    (await co`SELECT id FROM co_material_map ORDER BY id DESC LIMIT 1`)?.[0]
        ?.id ?? 0;

async function getWorld(name: string): Promise<COWorld> {
    const [existingWorld] =
        await co`SELECT * FROM co_world WHERE world = ${name}`;
    const knownWorld =
        existingWorld ?? coNewWorlds.find((world) => world.world == name);
    if (knownWorld) return knownWorld;

    const newWorld = {
        id: worldStartId + 1 + coNewWorlds.length,
        world: name,
    };
    coNewWorlds.push(newWorld);
    return newWorld;
}

async function getMaterial(material: string): Promise<COMaterialMap> {
    const [existingMaterial] =
        await co`SELECT * FROM co_material_map WHERE material = ${material}`;
    const knownMaterial =
        existingMaterial ??
        coNewMaterials.find((materialMap) => materialMap.material == material);
    if (knownMaterial) return knownMaterial;

    const newMaterial = {
        id: materialStartId + 1 + coNewMaterials.length,
        material: material,
    };
    coNewMaterials.push(newMaterial);
    return newMaterial;
}

async function getUser(uuid: string): Promise<COUser> {
    const [existingUser] = await co`SELECT * FROM co_user WHERE uuid = ${uuid}`;
    const knownUser =
        <COUser>existingUser ?? coNewUsers.find((user) => user.uuid == uuid);
    if (knownUser) return knownUser;

    let res: Response;
    console.log(`Requesting name for UUID ${uuid}`);
    res = await fetch(`https://api.mojang.com/user/profile/${uuid}`);
    if (!res.ok) {
        throw new Error(
            `Failed to fetch username for UUID ${uuid}: ${res.status} ${res.statusText}`,
        );
    }

    try {
        const json = <{ name: string; id: string }>await res.json();
        const newUser = {
            id: userStartId + 1 + coNewUsers.length,
            uuid: uuid,
            user: json.name,
        };
        coNewUsers.push(newUser);
        return newUser;
    } catch (e) {
        console.log(res);
        throw e;
    }
}

async function translateLog(log: LBBlockLog): Promise<COBlock> {
    let user = (await getUser(log.player)).id;
    let wid = (await getWorld(log.world)).id;
    let type = (await getMaterial(`minecraft:${log.block_type.toLowerCase()}`))
        .id;

    return {
        action: log.action == "PLACE" ? 1 : 0, // co action: 1 = place, 0 = break

        user,
        wid,
        type,

        x: log.x,
        y: log.y,
        z: log.z,

        data: 0,
        time: Math.floor(log.timestamp / 1000), // translate millis to seconds
        rolled_back: 0,
    };
}

let migratedLogCount = 0;
await co.transaction(async (cp) => {
    for (const orig_log of lbLogs) {
        const log = await translateLog(orig_log);

        const existingLogs =
            await cp`SELECT * FROM co_block WHERE time = ${log.time} AND user = ${log.user} AND wid = ${log.wid} AND x = ${log.x} AND y = ${log.y} AND z = ${log.z} AND action = ${log.action} AND type = ${log.type}`;
        if (existingLogs.length > 0) continue;

        await cp`INSERT INTO co_block (time, user, wid, x, y, z, type, data, action, rolled_back)
                VALUES (${log.time}, ${log.user}, ${log.wid}, ${log.x}, ${log.y}, ${log.z}, ${log.type}, ${log.data}, ${log.action}, ${log.rolled_back})`;
        migratedLogCount++;
    }

    for (const coWorld of coNewWorlds) {
        await cp`INSERT INTO co_world (id, world) VALUES (${coWorld.id}, ${coWorld.world})`;
    }
    for (const coUser of coNewUsers) {
        await cp`INSERT INTO co_user (id, user, uuid) VALUES (${coUser.id}, ${coUser.user}, ${coUser.uuid})`;
    }
    for (const coMaterial of coNewMaterials) {
        await cp`INSERT INTO co_material_map VALUES (${coMaterial.id}, ${coMaterial.material})`;
    }
});

console.info("OK: migrated");
console.info("\tusers: ", coNewUsers.length);
console.info("\tmaterials: ", coNewMaterials.length);
console.info("\tlogs: ", migratedLogCount);