const fs = require('fs');
const db = require('../config/database');
const CalculationService = require('./CalculationService');

const ImportService = {
    parseCSV: (filePath) => {
        const content = fs.readFileSync(filePath, 'utf8');
        const lines = content.split(/\r?\n/).filter(line => line.trim() !== '');
        const headers = lines[0].split(',').map(h => h.trim());
        const data = [];

        for (let i = 1; i < lines.length; i++) {
            const values = lines[i].split(',').map(v => v.trim());
            if (values.length === headers.length) {
                const row = {};
                headers.forEach((header, index) => {
                    row[header] = values[index];
                });
                data.push(row);
            }
        }
        return data;
    },

    importClientes: (filePath) => {
        const data = ImportService.parseCSV(filePath);
        let count = 0;
        let errors = [];

        const stmt = db.prepare(`
            INSERT INTO Clientes (Nombre, Telefono, Email, Direccion, Ciudad, Pais, TipoCliente, Estado)
            VALUES (@Nombre, @Telefono, @Email, @Direccion, @Ciudad, @Pais, @TipoCliente, @Estado)
        `);

        const accountStmt = db.prepare(`
            INSERT INTO CuentaRP (ClienteID) VALUES (?)
        `);

        db.transaction(() => {
            data.forEach((row, index) => {
                try {
                    const info = stmt.run(row);
                    accountStmt.run(info.lastInsertRowid);
                    count++;
                } catch (err) {
                    errors.push(`Row ${index + 2}: ${err.message}`);
                }
            });
        })();

        return { count, errors };
    },

    importOrdenes: (filePath) => {
        const data = ImportService.parseCSV(filePath);
        let count = 0;
        let errors = [];

        const stmt = db.prepare(`
            INSERT INTO OrdenesRP (ClienteID, NumeroOrden, Fecha, FechaVencimiento, TipoOrden, Subtotal, Impuestos, Total, Estado, Balance)
            VALUES (@ClienteID, @NumeroOrden, @Fecha, @FechaVencimiento, @TipoOrden, @Subtotal, @Impuestos, @Total, @Estado, @Total)
        `);
        // Note: Balance initialized to Total. Should be adjusted if Paid? Assuming import is for history too?
        // If State is 'Completada', is it paid? Not necessarily. 
        // For import simplified: Balance = Total unless specified. CSV doesn't have Balance.

        const itemStmt = db.prepare(`
            INSERT INTO OrdenItemsRP (OrdenID, Descripcion, Cantidad, PrecioUnitario, Subtotal)
            VALUES (?, 'Imported Item', 1, ?, ?)
        `);

        db.transaction(() => {
            data.forEach((row, index) => {
                try {
                    // Check if Cliente exists
                    const client = db.prepare('SELECT ClienteID FROM Clientes WHERE ClienteID = ?').get(row.ClienteID);
                    if (!client) throw new Error(`ClienteID ${row.ClienteID} not found`);

                    // Get Account ID
                    const account = db.prepare('SELECT CuentaID FROM CuentaRP WHERE ClienteID = ?').get(row.ClienteID);

                    const info = stmt.run(row);
                    const ordenId = info.lastInsertRowid;

                    // Update Order with AccountID
                    if (account) {
                        db.prepare('UPDATE OrdenesRP SET CuentaID = ? WHERE OrdenID = ?').run(account.CuentaID, ordenId);
                    }

                    // Create Dummy Item for Total (since CSV is Order Header only potentially? Or has items?)
                    // Prompt CSV example: "Ordenes.csv" has Subtotal, Impuestos, Total.
                    // It does NOT have items. So we create a dummy item to balance the OrderItems table? 
                    // Or we assume items are imported separately? 
                    // Prompt says "Ordenes.csv". Doesn't mention "OrdenItems.csv".
                    // So we effectively create a single item representing the order content.
                    itemStmt.run(ordenId, row.Subtotal, row.Subtotal);

                    count++;
                } catch (err) {
                    errors.push(`Row ${index + 2}: ${err.message}`);
                }
            });
        })();

        return { count, errors };
    },

    importTransacciones: (filePath) => {
        const data = ImportService.parseCSV(filePath);
        let count = 0;
        let errors = [];

        const stmt = db.prepare(`
            INSERT INTO TransaccionesRP (ClienteID, CuentaID, OrdenID, Tipo, Monto, Fecha, MetodoPago, Referencia, Descripcion)
            VALUES (@ClienteID, @CuentaID, @OrdenID, @Tipo, @Monto, @Fecha, @MetodoPago, @Referencia, @Descripcion)
        `);

        db.transaction(() => {
            data.forEach((row, index) => {
                try {
                    // Check IDs
                    const client = db.prepare('SELECT ClienteID FROM Clientes WHERE ClienteID = ?').get(row.ClienteID);
                    if (!client) throw new Error(`ClienteID ${row.ClienteID} not found`);

                    stmt.run(row);

                    // Recalculate Account
                    if (row.CuentaID) {
                        CalculationService.recalculateAccount(row.CuentaID);
                    }

                    // Recalculate Order Balance if related
                    if (row.OrdenID && row.OrdenID !== '') {
                        CalculationService.recalculateOrderBalance(row.OrdenID);
                    }

                    count++;
                } catch (err) {
                    errors.push(`Row ${index + 2}: ${err.message}`);
                }
            });
        })();

        return { count, errors };
    }
};

module.exports = ImportService;
