/**
 * Database connection and setup
 */

const mysql = require('mysql2/promise');
const logger = require('./logger');

let pool = null;

/**
 * Create database connection pool
 */
function createDatabase() {
    try {
        pool = mysql.createPool({
            host: process.env.DB_HOST || 'localhost',
            user: process.env.DB_USER || 'root',
            password: process.env.DB_PASS || '',
            database: process.env.DB_NAME || 'mikhmon_agents',
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        });
        
        logger.info('Database pool created');
        
        // Initialize tables
        initializeTables();
        
        return pool;
    } catch (error) {
        logger.error('Database connection error:', error);
        return null;
    }
}

/**
 * Initialize required tables
 */
async function initializeTables() {
    try {
        const connection = await pool.getConnection();
        
        // Create wa_message_logs table
        await connection.query(`
            CREATE TABLE IF NOT EXISTS wa_message_logs (
                id INT AUTO_INCREMENT PRIMARY KEY,
                phone VARCHAR(20) NOT NULL,
                message TEXT NOT NULL,
                direction ENUM('incoming', 'outgoing') DEFAULT 'outgoing',
                status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
                error_message TEXT,
                sent_at TIMESTAMP NULL,
                received_at TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_phone (phone),
                INDEX idx_status (status),
                INDEX idx_created (created_at)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        `);
        
        // Create wa_sessions table
        await connection.query(`
            CREATE TABLE IF NOT EXISTS wa_sessions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                session_name VARCHAR(100) UNIQUE NOT NULL,
                phone_number VARCHAR(20),
                status ENUM('connected', 'disconnected', 'qr_ready') DEFAULT 'disconnected',
                last_connected TIMESTAMP NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        `);
        
        connection.release();
        logger.info('Database tables initialized');
    } catch (error) {
        logger.error('Failed to initialize tables:', error);
    }
}

/**
 * Get database connection
 */
function getDatabase() {
    return pool;
}

module.exports = {
    createDatabase,
    getDatabase
};
