import { createPool } from 'mysql2/promise';
import type { Pool, PoolConnection, RowDataPacket, ResultSetHeader } from 'mysql2/promise';
import { logger } from '../lib/logger';

const pool: Pool = createPool({
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '3306'),
  user: process.env.DB_USER || 'ranchlands',
  password: process.env.DB_PASSWORD || 'ranchlands',
  database: process.env.DB_NAME || 'ranchlands',
  waitForConnections: true,
  connectionLimit: parseInt(process.env.DATABASE_POOL_MAX || '10'),
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0,
  dateStrings: false,
  timezone: '+00:00',
  charset: 'utf8mb4',
});

export async function testConnection(): Promise<void> {
  const conn = await pool.getConnection();
  const [rows] = await conn.query<RowDataPacket[]>('SELECT NOW() as time');
  conn.release();
  logger.info(`MariaDB connected. Server time: ${(rows[0] as any).time}`);
}

export async function query<T = RowDataPacket>(
  sql: string,
  params?: any[]
): Promise<{ rows: T[]; rowCount: number }> {
  const start = Date.now();
  try {
    const [rows] = await pool.execute(sql, params ?? []);
    const rowArray = Array.isArray(rows) ? (rows as T[]) : [];
    const rowCount = (rows as ResultSetHeader).affectedRows ?? rowArray.length;
    logger.debug('query', { sql: sql.slice(0, 80), ms: Date.now() - start });
    return { rows: rowArray, rowCount };
  } catch (err) {
    logger.error('Query error', { sql: sql.slice(0, 80), params, err });
    throw err;
  }
}

export async function queryOne<T = RowDataPacket>(
  sql: string,
  params?: any[]
): Promise<T | null> {
  const { rows } = await query<T>(sql, params);
  return rows[0] ?? null;
}

export async function queryMany<T = RowDataPacket>(
  sql: string,
  params?: any[]
): Promise<T[]> {
  const { rows } = await query<T>(sql, params);
  return rows;
}

export async function exec(
  sql: string,
  params?: any[]
): Promise<ResultSetHeader> {
  const start = Date.now();
  try {
    const [result] = await pool.execute(sql, params ?? []);
    logger.debug('exec', { sql: sql.slice(0, 80), ms: Date.now() - start });
    return result as ResultSetHeader;
  } catch (err) {
    logger.error('Exec error', { sql: sql.slice(0, 80), err });
    throw err;
  }
}

export async function withTransaction<T>(
  fn: (client: PoolConnection) => Promise<T>
): Promise<T> {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();
    const result = await fn(conn);
    await conn.commit();
    return result;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
}

export default pool;