/**
 * Hello world example using @cloudflare/d1
 */

import { Database } from "@cloudflare/d1";

const reply = (...values: any) => new Response(JSON.stringify(values));

const actions = ["create", "insert", "query-first", "query-all", "query-raw", "query-exec", "batch", "dump.sqlite", "exception", "pragmas"];
const link = (l: string) => `<li><a href=/${l} target=_new>${l}</a></li>`;

export default {
    async fetch(request: Request, env: any): Promise<Response> {
        const db = new Database(env.D1);
        let url = new URL(request.url);
        let data;
        switch (url.pathname.slice(1).split("/")[0]) {
            /*
             * Creates new table, drops it if it exist first, uses db.batch() and two prepared statements
             */
            case "create":
                data = await db.batch([db.prepare("DROP TABLE IF EXISTS my_table"), db.prepare("CREATE TABLE my_table (cid INTEGER PRIMARY KEY, name TEXT NOT NULL, employees INTEGER)")]);
                break;

            /*
             * Populates the table with two entries using multiple rows INSERT using stmt.run()
             */
            case "insert":
                data = await db.prepare('INSERT INTO my_table (name, employees) VALUES ("microsoft", 182268),("amazon", 1100000),("facebook", 44942),("apple",154000),("cloudflare",2432)').run();
                break;

            /*
             * Queries data using stmt.first() and stmt.find(column)
             */
            case "query-first":
                var first = await db.prepare("SELECT * FROM my_table WHERE employees > ?1 AND employees < ?2").bind(1000, 50000).first();
                var name = await db.prepare("SELECT * FROM my_table WHERE employees > ?1 AND employees < ?2").bind(1000, 50000).first("name");
                return reply(first, name);

            /*
             * Queries data using stmt.all()
             */
            case "query-all":
                var { results } = await db.prepare("SELECT * FROM my_table WHERE employees > ?1 AND employees < ?2").bind(1000, 50000).all();
                return reply(results);

            /*
             * Queries data using stmt.raw()
             */
            case "query-raw":
                /* Querying our data using raw() */
                data = await db.prepare("SELECT * FROM my_table WHERE employees > ?1 AND employees < ?2").bind(1000, 50000).raw();
                break;

            /*
             * Queries data using db.exec()
             */
            case "query-exec":
                data = await db.exec('INSERT INTO my_table (name, employees) VALUES ("twitter", 3268)\nINSERT INTO my_table (name, employees) VALUES ("instagram", 9177)\n');
                break;

            /*
             * Queries data db.batch() and reusing the same prepared statement
             */
            case "batch":
                const ps = db.prepare("SELECT * FROM my_table WHERE employees > ?1");
                data = await db.batch([
                    ps.bind(1000),
                    ps.bind(10000)
                ]);
                break;

            /*
             * Dumps downloads SQLite3 compatible file using db.dump()
             */
            case "dump.sqlite":
                const dump = await db.dump();
                return new Response(dump, { headers: { "content-type": "application/octet-stream" } });

            /*
             * Creates a D1 exception
             */
            case "exception":
                try {
                    await db.exec("INSERTZ INTO my_table (name, employees) VALUES ()");
                } catch (e: any) {
                    return reply({
                        message: e.message,
                        cause: e.cause.message,
                    });
                }
                break;

            /*
             * D1 supports a few pragamas
             */
            case "pragmas":
                data = await db.batch([
                    db.prepare("PRAGMA table_list"),
                    db.prepare("PRAGMA table_info(my_table)"),
                ]);
                break;

            default:
                /* Page frame */
                return new Response(`<html><title>D1 hello world</title><body>${actions.map((l) => link(l)).join("")}</body></html>`, { headers: { "content-type": "text/html" } });
        }

        return reply(data);
    },
};