import { getConnection } from '../db/connection.js';

export const createTables = async (req, res) => {
    let connection;
    const { table_prefix, research_name, logo, icon, screen_img, description } = req.body;

    if (!table_prefix, !research_name, !logo, !icon, !screen_img, !description) {
        return res.status(401).json({ msg: "Debes enviar todos los valores para para crear las tablas en la base de datos" });
    }

    try {
        if (!/^[a-zA-Z0-9_]+$/.test(table_prefix)) {
            return res.status(400).json({ error: "Prefijo inválido" });
        }

        connection = await getConnection();

        // Verificar si ya existe al menos una tabla con el prefijo
        const [exist_in] = await connection.query(
            `SELECT 1 
             FROM information_schema.tables 
             WHERE table_schema = DATABASE()
               AND table_name LIKE ? 
             LIMIT 1`,
            [`${table_prefix}_%`]
        );

        if (exist_in.length > 0) {
            return res.status(409).json({
                msg: `Ya existen tablas con el prefijo '${table_prefix}'`
            });
        }

        await connection.query(`
            CREATE TABLE IF NOT EXISTS ${table_prefix}_categories (
                id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL
            )
        `);

        await connection.query(`
            CREATE TABLE IF NOT EXISTS ${table_prefix}_subcategories (
                id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                category_id INT NOT NULL
            )
        `);

        await connection.query(`
            CREATE TABLE IF NOT EXISTS ${table_prefix}_region_reports (
                id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                year INT NOT NULL,
                month INT NOT NULL,
                category_id INT NOT NULL,
                subcategory_id INT NOT NULL,
                company_id INT NOT NULL,
                region INT NOT NULL,
                produccion DECIMAL(18,2) DEFAULT 0,
                ventas_pesos DECIMAL(18,2) DEFAULT 0,
                ventas_volumen DECIMAL(18,2) DEFAULT 0
            )
        `);

        await connection.query(`
            CREATE TABLE IF NOT EXISTS ${table_prefix}_country_reports (
                id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                year INT NOT NULL,
                month INT NOT NULL,
                category_id INT NOT NULL,
                subcategory_id INT NOT NULL,
                company_id INT NOT NULL,
                produccion DECIMAL(18,2) DEFAULT 0
            )
        `);

        await connection.query(`
            CREATE TABLE IF NOT EXISTS  ${table_prefix}_report_history (
                id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                month INT NOT NULL,
                year INT NOT NULL,
                company_nit VARCHAR(20) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);

        await connection.query(`
            CREATE TABLE IF NOT EXISTS ${table_prefix}_configurations (
                id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                config VARCHAR(255) NOT NULL,
                value VARCHAR(255) NOT NULL
            )
        `);

        await connection.query(
            `INSERT INTO ${table_prefix}_configurations (config, value)
                VALUES 
                    ('research_name', ?),
                    ('logo', ?),
                    ('icon', ?),
                    ('screen_img', ?),
                    ('description', ?)`,
            [research_name, logo, icon, screen_img, description]
        );



        res.status(201).json({ message: "Tablas creadas correctamente" });
    } catch (error) {
        console.error('Error creando tablas:', error);
        res.status(500).json({ error: "Error creando tablas", details: error.message });
    } finally {
        if (connection) connection.release();
    }
};
export const deleteTables = async (req, res) => {
    let connection;
    const { table_prefix } = req.body;

    if (!table_prefix) {
        return res.status(401).json({ msg: "Debes enviar un prefijo para eliminar las tablas" });
    }

    try {
        if (!/^[a-zA-Z0-9_]+$/.test(table_prefix)) {
            return res.status(400).json({ error: "Prefijo inválido" });
        }

        connection = await getConnection();

        // Obtener todas las tablas que empiecen con el prefijo
        const [tables] = await connection.query(
            `SELECT table_name 
             FROM information_schema.tables 
             WHERE table_schema = DATABASE()
               AND table_name LIKE ?`,
            [`${table_prefix}_%`]
        );

        if (tables.length === 0) {
            return res.status(404).json({
                msg: `No existen tablas con el prefijo '${table_prefix}'`
            });
        }

        // Para evitar errores de FK, eliminamos en orden inverso al de creación
        const orderedTables = [
            `${table_prefix}_reports`,
            `${table_prefix}_subcategories`,
            `${table_prefix}_categories`,
            `${table_prefix}_configurations`
        ];

        for (const table of orderedTables) {
            await connection.query(`DROP TABLE IF EXISTS \`${table}\``);
        }

        res.json({
            msg: `Todas las tablas con el prefijo '${table_prefix}' fueron eliminadas correctamente`
        });

    } catch (error) {
        console.error('Error eliminando tablas:', error);
        res.status(500).json({ error: "Error eliminando tablas", details: error.message });
    } finally {
        if (connection) connection.release();
    }
};
export const getConfigurations = async (req, res) => {
    const { table_prefix } = req.body;
    let connection;

    if (!table_prefix) {
        return res.status(401).json({ msg: "Debes enviar un prefijo de tabla" });
    }

    try {
        if (!/^[a-zA-Z0-9_]+$/.test(table_prefix)) {
            return res.status(400).json({ error: "Prefijo inválido" });
        }

        connection = await getConnection();
        const [configurations] = await connection.query(
            `SELECT * FROM ${table_prefix}_configurations`
        );

        return res.json({ configurations });

    } catch (error) {
        console.error("Error obteniendo configuraciones:", error);
        return res.status(500).json({ error: "Error obteniendo configuraciones" });
    } finally {
        if (connection) connection.release();
    }
};
export const getAllResearch = async (req, res) => {
    let connection;

    try {
        connection = await getConnection();

        // Obtener todas las tablas que terminen en _configurations
        const [tables] = await connection.query(`SHOW TABLES LIKE '%_configurations';`);

        const researches = [];

        for (const tableObj of tables) {
            const tableName = Object.values(tableObj)[0];

            // Seleccionar todos los registros de la tabla
            const [rows] = await connection.query(`SELECT * FROM \`${tableName}\``);

            researches.push({
                table: tableName,
                data: rows
            });
        }

        return res.json({ researches });

    } catch (error) {
        console.error("Error obteniendo todas las investigaciones:", error);
        return res.status(500).json({ error: "Error obteniendo todas las investigaciones" });
    } finally {
        if (connection) connection.release();
    }
};

