// MySQL Database Configuration for localhost and cPanel
import mysql from 'mysql2/promise';

// Database configuration interface
export interface DatabaseConfig {
  host: string;
  user: string;
  password: string;
  database: string;
  port?: number;
  ssl?: any;
  connectionLimit?: number;
  acquireTimeout?: number;
  timeout?: number;
}

// Environment-based configuration
const getConfig = (): DatabaseConfig => {
  const isProduction = process.env.NODE_ENV === 'production';
  
  if (isProduction) {
    // cPanel/Production configuration
    return {
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || '',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || '',
      port: parseInt(process.env.DB_PORT || '3306'),
      ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
      connectionLimit: 10,
      acquireTimeout: 60000,
      timeout: 60000,
    };
  } else {
    // Localhost/Development configuration
    return {
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || 'textile_manager',
      port: parseInt(process.env.DB_PORT || '3306'),
      connectionLimit: 5,
      acquireTimeout: 60000,
      timeout: 60000,
    };
  }
};

// Create connection pool
let pool: mysql.Pool | null = null;

export const createPool = (): mysql.Pool => {
  if (!pool) {
    const config = getConfig();
    console.log(`Connecting to database: ${config.host}:${config.port}/${config.database}`);
    
    pool = mysql.createPool({
      host: config.host,
      user: config.user,
      password: config.password,
      database: config.database,
      port: config.port,
      ssl: config.ssl,
      waitForConnections: true,
      connectionLimit: config.connectionLimit,
      queueLimit: 0,
      acquireTimeout: config.acquireTimeout,
      timeout: config.timeout,
      reconnect: true,
      // Additional MySQL settings
      charset: 'utf8mb4',
      timezone: '+00:00',
      supportBigNumbers: true,
      bigNumberStrings: true,
    });

    // Handle pool events
    pool.on('connection', (connection) => {
      console.log('New MySQL connection established as id ' + connection.threadId);
    });

    pool.on('error', (err) => {
      console.error('MySQL pool error:', err);
      if (err.code === 'PROTOCOL_CONNECTION_LOST') {
        console.log('Recreating MySQL pool...');
        pool = null;
        createPool();
      }
    });
  }
  
  return pool;
};

// Get database connection
export const getConnection = async (): Promise<mysql.PoolConnection> => {
  const pool = createPool();
  return await pool.getConnection();
};

// Execute query with connection management
export const executeQuery = async <T = any>(
  query: string, 
  params: any[] = []
): Promise<T[]> => {
  const connection = await getConnection();
  
  try {
    const [rows] = await connection.execute(query, params);
    return rows as T[];
  } catch (error) {
    console.error('Database query error:', error);
    console.error('Query:', query);
    console.error('Params:', params);
    throw error;
  } finally {
    connection.release();
  }
};

// Execute query and return single result
export const executeQuerySingle = async <T = any>(
  query: string, 
  params: any[] = []
): Promise<T | null> => {
  const results = await executeQuery<T>(query, params);
  return results.length > 0 ? results[0] : null;
};

// Execute insert and return inserted ID
export const executeInsert = async (
  query: string, 
  params: any[] = []
): Promise<number> => {
  const connection = await getConnection();
  
  try {
    const [result] = await connection.execute(query, params);
    return (result as any).insertId;
  } catch (error) {
    console.error('Database insert error:', error);
    console.error('Query:', query);
    console.error('Params:', params);
    throw error;
  } finally {
    connection.release();
  }
};

// Execute update/delete and return affected rows
export const executeUpdate = async (
  query: string, 
  params: any[] = []
): Promise<number> => {
  const connection = await getConnection();
  
  try {
    const [result] = await connection.execute(query, params);
    return (result as any).affectedRows;
  } catch (error) {
    console.error('Database update error:', error);
    console.error('Query:', query);
    console.error('Params:', params);
    throw error;
  } finally {
    connection.release();
  }
};

// Transaction support
export const executeTransaction = async <T>(
  callback: (connection: mysql.PoolConnection) => Promise<T>
): Promise<T> => {
  const connection = await getConnection();
  
  try {
    await connection.beginTransaction();
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    console.error('Transaction error:', error);
    throw error;
  } finally {
    connection.release();
  }
};

// Test database connection
export const testConnection = async (): Promise<boolean> => {
  try {
    const result = await executeQuerySingle('SELECT 1 as test');
    return result?.test === 1;
  } catch (error) {
    console.error('Database connection test failed:', error);
    return false;
  }
};

// Close all connections (for graceful shutdown)
export const closePool = async (): Promise<void> => {
  if (pool) {
    await pool.end();
    pool = null;
    console.log('MySQL pool closed');
  }
};

// Database health check
export const healthCheck = async (): Promise<{
  status: 'healthy' | 'unhealthy';
  details: {
    connected: boolean;
    poolConnections?: number;
    error?: string;
  };
}> => {
  try {
    const connected = await testConnection();
    
    if (!connected) {
      return {
        status: 'unhealthy',
        details: {
          connected: false,
          error: 'Cannot execute test query'
        }
      };
    }

    const poolInfo = pool ? {
      poolConnections: (pool as any)._allConnections?.length || 0
    } : {};

    return {
      status: 'healthy',
      details: {
        connected: true,
        ...poolInfo
      }
    };
  } catch (error: any) {
    return {
      status: 'unhealthy',
      details: {
        connected: false,
        error: error.message
      }
    };
  }
};

// Utility functions for common patterns
export const buildWhereClause = (
  conditions: Record<string, any>,
  startIndex = 1
): { clause: string; params: any[] } => {
  const keys = Object.keys(conditions).filter(key => conditions[key] !== undefined);
  
  if (keys.length === 0) {
    return { clause: '', params: [] };
  }
  
  const clause = 'WHERE ' + keys.map(key => `${key} = ?`).join(' AND ');
  const params = keys.map(key => conditions[key]);
  
  return { clause, params };
};

export const buildUpdateClause = (
  data: Record<string, any>
): { clause: string; params: any[] } => {
  const keys = Object.keys(data).filter(key => data[key] !== undefined);
  
  if (keys.length === 0) {
    throw new Error('No data provided for update');
  }
  
  const clause = keys.map(key => `${key} = ?`).join(', ');
  const params = keys.map(key => data[key]);
  
  return { clause, params };
};

export const buildInsertClause = (
  data: Record<string, any>
): { columns: string; placeholders: string; params: any[] } => {
  const keys = Object.keys(data).filter(key => data[key] !== undefined);
  
  if (keys.length === 0) {
    throw new Error('No data provided for insert');
  }
  
  const columns = keys.join(', ');
  const placeholders = keys.map(() => '?').join(', ');
  const params = keys.map(key => data[key]);
  
  return { columns, placeholders, params };
};

// Pagination helper
export const buildPaginationClause = (
  page: number = 1,
  limit: number = 10,
  orderBy: string = 'id',
  orderDirection: 'ASC' | 'DESC' = 'DESC'
): { clause: string; offset: number } => {
  const offset = (page - 1) * limit;
  const clause = `ORDER BY ${orderBy} ${orderDirection} LIMIT ${limit} OFFSET ${offset}`;
  
  return { clause, offset };
};

// Search helper
export const buildSearchClause = (
  searchTerm: string,
  searchFields: string[]
): { clause: string; params: any[] } => {
  if (!searchTerm || searchFields.length === 0) {
    return { clause: '', params: [] };
  }
  
  const clause = searchFields.map(field => `${field} LIKE ?`).join(' OR ');
  const params = searchFields.map(() => `%${searchTerm}%`);
  
  return { clause: `(${clause})`, params };
};