import sqlite3 from 'sqlite3';
import config from 'config';

export const db = new sqlite3.Database(config.get('db.sqlite'));

export async function initTablesIfNotExists(db: sqlite3.Database) {
    const run = (sql: string): Promise<void> => {
        return new Promise((resolve, reject) => {
            db.run(sql, (e) => {
                e ? reject(e) : resolve();
            });
        })
    };

    await run(
        `CREATE TABLE IF NOT EXISTS documents (
            id TEXT PRIMARY KEY,
            type TEXT NOT NULL,
            version INTEGER NOT NULL,
            snapshot_version INTEGER NOT NULL
        )`
    );

    await run(
        `CREATE TABLE IF NOT EXISTS operations (
            doc_id TEXT NOT NULL,
            version INTEGER NOT NULL,
            operation TEXT NOT NULL,
            PRIMARY KEY (doc_id, version),
            FOREIGN KEY (doc_id) REFERENCES documents (id) ON DELETE CASCADE
        )`
    );

    await run(
        `CREATE TABLE IF NOT EXISTS snapshot_fragments (
            doc_id TEXT NOT NULL,
            fragment_id TEXT NOT NULL,
            data TEXT NOT NULL,
            PRIMARY KEY (doc_id, fragment_id),
            FOREIGN KEY (doc_id) REFERENCES documents (id) ON DELETE CASCADE
        )`
    );

    // users
    await run(`
    CREATE TABLE IF NOT EXISTS users (
        id TEXT PRIMARY KEY,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL
    )`);

    // documents_ex
    await run(`
    CREATE TABLE IF NOT EXISTS documents_ex (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        type TEXT NOT NULL,
        create_user_id TEXT NOT NULL,
        last_modify_user_id TEXT NOT NULL,
        last_modify_date DATETIME NOT NULL,
        FOREIGN KEY (create_user_id) REFERENCES users(id)
    )`);

    // document_user_role
    await run(`
    CREATE TABLE IF NOT EXISTS document_user_role (
        user_id TEXT NOT NULL,
        doc_id TEXT NOT NULL,
        role TEXT NOT NULL,
        PRIMARY KEY (user_id, doc_id)
        FOREIGN KEY (user_id) REFERENCES users(id)
        FOREIGN KEY (doc_id) REFERENCES documents(id)
    )`);

    // ops_ex
    await run(`
    CREATE TABLE IF NOT EXISTS ops_ex (
        user_id TEXT NOT NULL,
        doc_id TEXT NOT NULL,
        version INTEGER NOT NULL,
        date DATETIME NOT NULL,
        PRIMARY KEY (doc_id, version)
        FOREIGN KEY (doc_id) REFERENCES documents(id)
    )`);
}