import mysql, { Pool, PoolConnection, RowDataPacket } from 'mysql2/promise';
import { logger } from './logger';
import { config } from 'dotenv';

config();

type DbRow<T> = T & RowDataPacket;

class Database {
  private pool: Pool;

  constructor() {
    this.pool = mysql.createPool({
      host: process.env.DB_HOST,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0,
    });
  }

  // Execute a query, automatically handles connections and errors
  public async query<T extends object>(query: string, params: any[] = []): Promise<DbRow<T>[]> {
    let connection: PoolConnection | undefined;
    const start = Date.now(); // Waktu mulai eksekusi query
    try {
      connection = await this.pool.getConnection();
      const [rows] = await connection.query<DbRow<T>[]>(query, params);
      const duration = Date.now() - start; // Menghitung waktu eksekusi
      logger.info('Query executed', { query, params, duration: `${duration} ms` });
      return rows;
    } catch (error) {
      logger.error('Database Query Error', { error });
      throw new Error('Failed to execute query');
    } finally {
      if (connection) connection.release();
    }
  }

  // Execute a query but only return a single row or null if no rows found
  public async queryOne<T extends object>(query: string, params: any[] = []): Promise<DbRow<T> | null> {
    const rows = await this.query<T>(query, params);
    return rows.length > 0 ? rows[0] : null;
  }

  // Begin a transaction, allowing multiple queries in one transaction
  public async transaction<T>(callback: (conn: PoolConnection) => Promise<T>): Promise<T> {
    const connection = await this.pool.getConnection();
    await connection.beginTransaction();

    try {
      const result = await callback(connection);
      await connection.commit();
      return result;
    } catch (error) {
      await connection.rollback();
      logger.error('Transaction failed', { error });
      throw new Error('Transaction failed');
    } finally {
      connection.release();
    }
  }

  // Clean up connections (for testing or app shutdown)
  public async close(): Promise<void> {
    await this.pool.end();
  }
}

export const db = new Database();

process.on('SIGINT', async () => {
  try {
    await db.close();
    logger.info('Database connections closed gracefully');
  } catch (err) {
    logger.error('Error closing database connections', err);
  } finally {
    process.exit();
  }
});
