// spellchecker: words sqlocal kysely insertable updateable

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

const tableNames = {
  mail: {
    table: "mail",
    columns: {
      id: "id",
      messageId: "message_id",
      awsMessageId: "aws_message_id",
      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.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.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("database.sqlite3");
    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>): Promise<void> {
    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;
  }
}