const getContactoColumns = (db) => {
    const columns = db.prepare('PRAGMA table_info(Contactos)').all();
    return new Set(columns.map((column) => column.name));
};

const buildContactoInsert = (db, valuesByColumn) => {
    const availableColumns = getContactoColumns(db);
    const requiredColumns = [
        'full_name',
        'mobile_phone',
        'city',
        'state',
        'origin_type',
        'referred_by_type',
        'referred_by_id',
        'relationship_to_referrer',
        'assigned_to_user_id'
    ];
    const missingRequired = requiredColumns.filter((column) => !availableColumns.has(column));

    if (missingRequired.length > 0) {
        throw new Error(`Esquema de Contactos incompleto: falta ${missingRequired.join(', ')}`);
    }

    const columns = Object.keys(valuesByColumn).filter((column) => availableColumns.has(column));
    const placeholders = columns.map(() => '?').join(', ');
    const sql = `INSERT INTO Contactos (${columns.join(', ')}) VALUES (${placeholders})`;
    const values = columns.map((column) => valuesByColumn[column]);

    return { sql, values };
};

const buildContactoUpdate = (db, valuesByColumn) => {
    const availableColumns = getContactoColumns(db);
    const requiredColumns = [
        'full_name',
        'mobile_phone',
        'city',
        'state',
        'origin_type',
        'referred_by_type',
        'referred_by_id',
        'relationship_to_referrer',
        'assigned_to_user_id'
    ];
    const missingRequired = requiredColumns.filter((column) => !availableColumns.has(column));

    if (missingRequired.length > 0) {
        throw new Error(`Esquema de Contactos incompleto: falta ${missingRequired.join(', ')}`);
    }

    const columns = Object.keys(valuesByColumn).filter((column) => availableColumns.has(column));
    const assignments = columns.map((column) => `${column} = ?`).join(', ');
    const sql = `UPDATE Contactos SET ${assignments}, UpdatedAt = CURRENT_TIMESTAMP WHERE ContactoID = ?`;
    const values = columns.map((column) => valuesByColumn[column]);

    return { sql, values };
};

module.exports = {
    buildContactoInsert,
    buildContactoUpdate,
};
