import * as mariadb from "mariadb";
import type { Pool, PoolConnection } from "mariadb";
import { getConfig } from "../config.js";

let pool: Pool | null = null;

const SCHEMA_STATEMENTS = [
  `CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(320) NOT NULL,
    name VARCHAR(160) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email_verified_at DATETIME(3) NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
    last_login_at DATETIME(3) NULL,
    UNIQUE KEY uq_users_email (email)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`,
  `CREATE TABLE IF NOT EXISTS auth_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    token_type VARCHAR(32) NOT NULL,
    expires_at DATETIME(3) NOT NULL,
    consumed_at DATETIME(3) NULL,
    created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    UNIQUE KEY uq_auth_tokens_hash (token_hash),
    KEY idx_auth_tokens_user_type (user_id, token_type),
    KEY idx_auth_tokens_expiry (expires_at),
    CONSTRAINT fk_auth_tokens_user
      FOREIGN KEY (user_id) REFERENCES users(id)
      ON DELETE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci`
];

function quoteIdentifier(value: string): string {
  return `\`${value.replace(/`/g, "``")}\``;
}

function getPool(): Pool {
  if (pool) return pool;

  const config = getConfig().database;
  const nextPool = mariadb.createPool({
    host: config.host,
    port: config.port,
    user: config.user,
    password: config.password,
    database: config.database,
    connectionLimit: config.connectionLimit,
    bigIntAsNumber: true
  });

  pool = nextPool;
  return nextPool;
}

export async function initializeDatabase(): Promise<void> {
  const config = getConfig().database;
  const connection = await mariadb.createConnection({
    host: config.host,
    port: config.port,
    user: config.user,
    password: config.password,
    bigIntAsNumber: true
  });

  try {
    await connection.query(
      `CREATE DATABASE IF NOT EXISTS ${quoteIdentifier(config.database)}
       CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`
    );
  } finally {
    await connection.end();
  }

  await withConnection(async (db) => {
    for (const statement of SCHEMA_STATEMENTS) {
      await db.query(statement);
    }
  });
}

export async function withConnection<T>(
  callback: (connection: PoolConnection) => Promise<T>
): Promise<T> {
  const connection = await getPool().getConnection();
  try {
    return await callback(connection);
  } finally {
    connection.release();
  }
}

export async function queryRows<T>(
  sql: string,
  params: unknown[] = []
): Promise<T[]> {
  return withConnection(async (connection) => {
    const rows = await connection.query(sql, params);
    return Array.isArray(rows) ? (rows as T[]) : [];
  });
}

export async function execute(sql: string, params: unknown[] = []): Promise<{
  affectedRows: number;
  insertId: number;
}> {
  return withConnection(async (connection) => {
    const result = (await connection.query(sql, params)) as {
      affectedRows?: number | bigint;
      insertId?: number | bigint;
    };
    return {
      affectedRows: Number(result.affectedRows ?? 0),
      insertId: Number(result.insertId ?? 0)
    };
  });
}

export async function closeDatabase(): Promise<void> {
  if (!pool) return;
  const currentPool = pool;
  pool = null;
  await currentPool.end();
}