// spellchecker: words sqlocal kysely insertable updateable

import { SQLocalKysely } from "sqlocal/kysely";
import { Generated, Insertable, Kysely, Migration, Migrator } from "kysely";

import { Mail } from "./mail.ts";

export class DbError extends Error {
}

const tableNames = {
  mail: {
    table: "mail",
    columns: {
      id: "id",
      messageId: "message_id",
      awsMessageId: "aws_message_id",
      date: "date",
      raw: "raw",
    },
  },
} as const;

interface MailTable {
  [tableNames.mail.columns.id]: Generated<number>;
  [tableNames.mail.columns.messageId]: string;
  [tableNames.mail.columns.awsMessageId]: string | null;
  [tableNames.mail.columns.date]: number | null;
  [tableNames.mail.columns.raw]: string;
}

interface Database {
  [tableNames.mail.table]: MailTable;
}

const migrations: Record<string, Migration> = {
  "0001-init": {
    // deno-lint-ignore no-explicit-any
    async up(db: Kysely<any>): Promise<void> {
      const names = tableNames.mail;

      await db.schema
        .createTable(names.table)
        .addColumn(
          names.columns.id,
          "integer",
          (col) => col.primaryKey().autoIncrement(),
        )
        .addColumn(
          names.columns.messageId,
          "text",
          (col) => col.notNull().unique(),
        )
        .addColumn(names.columns.awsMessageId, "text", (col) => col.unique())
        .addColumn(names.columns.date, "integer")
        .addColumn(names.columns.raw, "text", (col) => col.notNull())
        .execute();

      for (
        const column of [names.columns.messageId, names.columns.awsMessageId]
      ) {
        await db.schema
          .createIndex(`${names.table}_${column}`)
          .on(names.table)
          .column(column)
          .execute();
      }
    },

    // deno-lint-ignore no-explicit-any
    async down(db: Kysely<any>): Promise<void> {
      await db.schema.dropTable(tableNames.mail.table).execute();
    },
  },
};

export class DbService {
  private _sqlocal;
  private _db;
  private _migrator;

  constructor(public readonly path: string) {
    this._sqlocal = new SQLocalKysely({
      databasePath: path,
      onInit: (sql) => [
        // Though this can be executed only once when database is
        // created, re-calls should not affect performance.
        sql`PRAGMA journal_mode=WAL;`,
      ],
    });
    const db = new Kysely<Database>({ dialect: this._sqlocal.dialect });
    this._db = db;
    this._migrator = new Migrator({
      db,
      provider: {
        getMigrations(): Promise<Record<string, Migration>> {
          return Promise.resolve(migrations);
        },
      },
    });
  }

  async migrate(): Promise<void> {
    await this._migrator.migrateToLatest();
  }

  async addMail(mail: Insertable<MailTable> | Mail, options?: {
    allowNullAwsMessageId?: boolean;
  }): Promise<void> {
    if (mail instanceof Mail) {
      if (mail.messageId == null) {
        throw new DbError("Mail object has no message id.");
      }
      mail = {
        message_id: mail.messageId,
        aws_message_id: mail.awsMessageId,
        date: mail.simpleGetDate()?.getTime(),
        raw: mail.raw,
      };
    }
    if (
      mail.aws_message_id == null &&
      !(options?.allowNullAwsMessageId === true)
    ) {
      throw new DbError("Aws message id is missing but it is required.");
    }

    await this._db.insertInto(tableNames.mail.table).values(mail)
      .executeTakeFirstOrThrow();
  }

  async messageIdToAws(messageId: string): Promise<string | null> {
    const row = await this._db.selectFrom(tableNames.mail.table).where(
      tableNames.mail.columns.messageId,
      "=",
      messageId,
    ).select(tableNames.mail.columns.awsMessageId).executeTakeFirst();
    return row?.aws_message_id ?? null;
  }

  async messageIdFromAws(awsMessageId: string): Promise<string | null> {
    const row = await this._db.selectFrom(tableNames.mail.table).where(
      tableNames.mail.columns.awsMessageId,
      "=",
      awsMessageId,
    ).select(tableNames.mail.columns.messageId).executeTakeFirst();
    return row?.message_id ?? null;
  }
}