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

const runMigrations = () => {
    console.log('Starting migrations...');

    // Get all .sql files in sorting order
    const migrationFiles = fs.readdirSync(__dirname)
        .filter(file => file.endsWith('.sql'))
        .sort();

    const columnExists = (table, column) => {
        const info = db.prepare(`PRAGMA table_info(${table})`).all();
        return info.some((col) => col.name === column);
    };

    const tableExists = (table) => {
        const row = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name = ?").get(table);
        return Boolean(row);
    };

    const foreignKeyReferences = (table, refTable) => {
        const rows = db.prepare(`PRAGMA foreign_key_list(${table})`).all();
        return rows.some((row) => row.table === refTable);
    };

    const addColumnIfMissing = (table, column, definition) => {
        if (!columnExists(table, column)) {
            db.exec(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);
        }
    };

    const contactosSchemaUpToDate = () => {
        if (!tableExists('Contactos')) return false;
        const info = db.prepare('PRAGMA table_info(Contactos)').all();
        const required = [
            'full_name',
            'mobile_phone',
            'city',
            'state',
            'origin_type',
            'referred_by_type',
            'referred_by_id',
            'relationship_to_referrer',
            'assigned_to_user_id',
            'contact_status',
            'contact_allowed',
            'address1',
            'zip_code',
            'country',
            'NombreCompleto',
            'Telefono',
            'Pais',
            'Convertido'
        ];

        return required.every((column) => info.some((col) => col.name === column));
    };

    const ensureUsuariosSchema = () => {
        if (!tableExists('Usuarios')) return;

        addColumnIfMissing('Usuarios', 'Codigo', "TEXT");
        addColumnIfMissing('Usuarios', 'Password', "TEXT");
        addColumnIfMissing('Usuarios', 'Rol', "TEXT DEFAULT 'VENDEDOR'");
        addColumnIfMissing('Usuarios', 'Activo', "INTEGER DEFAULT 1");
        addColumnIfMissing('Usuarios', 'Telefono', 'TEXT');
        addColumnIfMissing('Usuarios', 'MetaMensual', 'DECIMAL(10,2)');
        addColumnIfMissing('Usuarios', 'UltimoAcceso', 'DATETIME');
        addColumnIfMissing('Usuarios', 'UpdatedAt', 'DATETIME');

        db.exec("UPDATE Usuarios SET Codigo = COALESCE(Codigo, 'USR' || UsuarioID) WHERE Codigo IS NULL OR Codigo = ''");
        db.exec("UPDATE Usuarios SET Password = COALESCE(Password, '') WHERE Password IS NULL");
    };

    const ensureContactosSourceFields = () => {
        if (!tableExists('Contactos')) return;
        addColumnIfMissing('Contactos', 'source', 'TEXT');
        addColumnIfMissing('Contactos', 'source_name', 'TEXT');
        addColumnIfMissing('Contactos', 'assigned_to_user_id', 'INTEGER');
    };

    const ensureOportunidadesSourceFields = () => {
        if (!tableExists('Oportunidades')) return;
        addColumnIfMissing('Oportunidades', 'source', 'TEXT');
        addColumnIfMissing('Oportunidades', 'source_name', 'TEXT');
        addColumnIfMissing('Oportunidades', 'assigned_to_user_id', 'INTEGER');
    };

    ensureContactosSourceFields();
    ensureOportunidadesSourceFields();

    for (const file of migrationFiles) {
        console.log(`Running translation: ${file}`);
        const filePath = path.join(__dirname, file);
        let sql = fs.readFileSync(filePath, 'utf8');

        if (file === '005_auth_tables.sql') {
            ensureUsuariosSchema();
        }

        if (sql.includes('ALTER TABLE Clientes ADD COLUMN LifecycleStage')) {
            if (columnExists('Clientes', 'LifecycleStage')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Clientes ADD COLUMN LifecycleStage[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN EstadoCierre')) {
            if (columnExists('Oportunidades', 'EstadoCierre')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN EstadoCierre[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN ProximoContactoFecha')) {
            if (columnExists('Oportunidades', 'ProximoContactoFecha')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN ProximoContactoFecha[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN MotivoNoInteresado')) {
            if (columnExists('Oportunidades', 'MotivoNoInteresado')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN MotivoNoInteresado[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Clientes ADD COLUMN EstadoProvincia')) {
            if (columnExists('Clientes', 'EstadoProvincia')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Clientes ADD COLUMN EstadoProvincia[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Clientes ADD COLUMN Zipcode')) {
            if (columnExists('Clientes', 'Zipcode')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Clientes ADD COLUMN Zipcode[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Clientes ADD COLUMN Pais')) {
            if (columnExists('Clientes', 'Pais')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Clientes ADD COLUMN Pais[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Usuarios ADD COLUMN ResetToken')) {
            if (columnExists('Usuarios', 'ResetToken')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Usuarios ADD COLUMN ResetToken[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Usuarios ADD COLUMN ResetTokenExpiry')) {
            if (columnExists('Usuarios', 'ResetTokenExpiry')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Usuarios ADD COLUMN ResetTokenExpiry[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades RENAME TO Oportunidades_old') && !sql.includes('-- CONTACTOS_FORM_FIELDS')) {
            if (columnExists('Oportunidades', 'ClienteID') && tableExists('Contactos')) {
                sql = '';
            }
        }

        if (sql.includes('ALTER TABLE Programas RENAME TO Programas_old')) {
            if (tableExists('Programas') && tableExists('Contactos') && foreignKeyReferences('Programas', 'Contactos')) {
                sql = '';
            } else if (!tableExists('Programas') && tableExists('Programas_old')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Programas RENAME TO Programas_old;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN ReferidoPor')) {
            if (columnExists('Oportunidades', 'ReferidoPor')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN ReferidoPor[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN EstadoCivil')) {
            if (columnExists('Oportunidades', 'EstadoCivil')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN EstadoCivil[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN NombrePareja')) {
            if (columnExists('Oportunidades', 'NombrePareja')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN NombrePareja[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN TrabajaActualmente')) {
            if (columnExists('Oportunidades', 'TrabajaActualmente')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN TrabajaActualmente[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN MejorHoraContacto')) {
            if (columnExists('Oportunidades', 'MejorHoraContacto')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN MejorHoraContacto[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN RomperHielo')) {
            if (columnExists('Oportunidades', 'RomperHielo')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN RomperHielo[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN RegaloVisitaEntregado')) {
            if (columnExists('Oportunidades', 'RegaloVisitaEntregado')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN RegaloVisitaEntregado[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN DemoCompletada')) {
            if (columnExists('Oportunidades', 'DemoCompletada')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN DemoCompletada[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN ProgramaVisitaID')) {
            if (columnExists('Oportunidades', 'ProgramaVisitaID')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN ProgramaVisitaID[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN EstadoLugar')) {
            if (columnExists('ProgramasReferidosCRM', 'EstadoLugar')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN EstadoLugar[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasCRM ADD COLUMN RegaloElegido')) {
            if (columnExists('ProgramasCRM', 'RegaloElegido')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasCRM ADD COLUMN RegaloElegido[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasCRM ADD COLUMN RegaloElegidoOtro')) {
            if (columnExists('ProgramasCRM', 'RegaloElegidoOtro')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasCRM ADD COLUMN RegaloElegidoOtro[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN NombrePareja')) {
            if (columnExists('ProgramasReferidosCRM', 'NombrePareja')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN NombrePareja[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN Direccion')) {
            if (columnExists('ProgramasReferidosCRM', 'Direccion')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN Direccion[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN Ciudad')) {
            if (columnExists('ProgramasReferidosCRM', 'Ciudad')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN Ciudad[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN Zipcode')) {
            if (columnExists('ProgramasReferidosCRM', 'Zipcode')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN Zipcode[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN Relacion')) {
            if (columnExists('ProgramasReferidosCRM', 'Relacion')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN Relacion[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN TrabajaActualmente')) {
            if (columnExists('ProgramasReferidosCRM', 'TrabajaActualmente')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN TrabajaActualmente[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN MejorHoraContacto')) {
            if (columnExists('ProgramasReferidosCRM', 'MejorHoraContacto')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN MejorHoraContacto[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN PropietarioCasa')) {
            if (columnExists('ProgramasReferidosCRM', 'PropietarioCasa')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN PropietarioCasa[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN ConoceRoyalPrestige')) {
            if (columnExists('ProgramasReferidosCRM', 'ConoceRoyalPrestige')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN ConoceRoyalPrestige[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN Prioridad')) {
            if (columnExists('ProgramasReferidosCRM', 'Prioridad')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN Prioridad[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN PrioridadNota')) {
            if (columnExists('ProgramasReferidosCRM', 'PrioridadNota')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN PrioridadNota[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN Notas')) {
            if (columnExists('ProgramasReferidosCRM', 'Notas')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN Notas[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN EstadoCivil')) {
            if (columnExists('ProgramasReferidosCRM', 'EstadoCivil')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN EstadoCivil[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE ProgramasReferidosCRM ADD COLUMN CreatedOpportunityID')) {
            if (columnExists('ProgramasReferidosCRM', 'CreatedOpportunityID')) {
                sql = sql.replace(/\n?\s*ALTER TABLE ProgramasReferidosCRM ADD COLUMN CreatedOpportunityID[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN ProgramaID')) {
            if (columnExists('Oportunidades', 'ProgramaID')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN ProgramaID[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN ProgramaTipo')) {
            if (columnExists('Oportunidades', 'ProgramaTipo')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN ProgramaTipo[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Oportunidades ADD COLUMN ReferidoPorId')) {
            if (columnExists('Oportunidades', 'ReferidoPorId')) {
                sql = sql.replace(/\n?\s*ALTER TABLE Oportunidades ADD COLUMN ReferidoPorId[^;]*;\s*/i, '\n');
            }
        }

        if (sql.includes('ALTER TABLE Contactos RENAME TO Contactos_old')) {
            if (contactosSchemaUpToDate()) {
                sql = '';
            }
        }

        if (sql.includes('-- FIX_CONTACTOS_FK')) {
            const oportunidadesOk = tableExists('Oportunidades') && !foreignKeyReferences('Oportunidades', 'Contactos_old');
            const programasOk = tableExists('Programas') && !foreignKeyReferences('Programas', 'Contactos_old');

            if (oportunidadesOk && programasOk) {
                sql = '';
            }
        }

        if (sql.includes('-- CONTACTOS_FORM_FIELDS') || sql.includes('-- CONTACTOS_ROYAL_PRESTIGE')) {
            const oportunidadesOk = tableExists('Oportunidades') && !foreignKeyReferences('Oportunidades', 'Contactos_old');
            const programasOk = tableExists('Programas') && !foreignKeyReferences('Programas', 'Contactos_old');

            if (contactosSchemaUpToDate() && oportunidadesOk && programasOk) {
                sql = '';
            }
        }

        try {
            db.exec(sql);
            console.log(`✓ Success: ${file}`);
        } catch (err) {
            console.error(`✗ Error in ${file}:`, err.message);
            process.exit(1);
        }
    }

    ensureContactosSourceFields();
    ensureOportunidadesSourceFields();

    console.log('All migrations completed successfully.');
};

runMigrations();
