marsalva/api
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
api/server.js
marsalva fc9945ad32 Actualizar server.js
2026-04-04 16:41:08 +00:00

5673 lines
261 KiB
JavaScript
Raw Permalink Blame History

import express from "express";
import cors from "cors";
import bcrypt from "bcryptjs";
import jwt from "jsonwebtoken";
import pg from "pg";
import crypto from "crypto";
import OpenAI from "openai";
import Stripe from "stripe";
const { Pool } = pg;
const app = express();
// 🛑 SEMÁFORO IA: Guarda los IDs de los servicios que están siendo procesados
const candadosIA = new Set();
// Configuración de CORS Profesional
const corsOptions = {
origin: [
'https://web.integrarepara.es',
'https://portal.integrarepara.es',
'https://app.integrarepara.es',
'http://localhost:3000',
'http://127.0.0.1:3000'
],
methods: ['GET', 'POST', 'PUT', 'PATCH', 'DELETE', 'OPTIONS'],
allowedHeaders: ['Content-Type', 'Authorization'],
credentials: true,
optionsSuccessStatus: 200
};
app.use(cors(corsOptions));
// Habilitar pre-flight para todas las rutas
app.options('*', cors(corsOptions));
// ==========================================
// 💳 WEBHOOK STRIPE (DEBE IR ANTES DE express.json)
// ==========================================
app.post("/webhook/stripe", express.raw({ type: "application/json" }), async (req, res) => {
let event;
try {
if (!STRIPE_WEBHOOK_SECRET) {
console.error("❌ STRIPE_WEBHOOK_SECRET no configurado.");
return res.status(500).send("Webhook secret no configurado");
}
const signature = req.headers["stripe-signature"];
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY || "sk_test_dummy", { apiVersion: "2023-10-16" });
event = stripe.webhooks.constructEvent(req.body, signature, STRIPE_WEBHOOK_SECRET);
} catch (err) {
console.error("❌ Firma webhook Stripe inválida:", err.message);
return res.status(400).send(`Webhook Error: ${err.message}`);
}
const insertarEventoPago = async ({
subscriptionId = null,
companyId = null,
stripeInvoiceId = null,
stripePaymentIntentId = null,
stripeCheckoutSessionId = null,
stripeEventId = null,
amount = 0,
currency = "eur",
status = "pendiente",
eventType = null,
paidAt = null
}) => {
try {
await pool.query(`
INSERT INTO protection_payment_events (
subscription_id,
company_id,
stripe_invoice_id,
stripe_payment_intent_id,
stripe_checkout_session_id,
stripe_event_id,
amount,
currency,
status,
event_type,
paid_at
)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
`, [
subscriptionId,
companyId,
stripeInvoiceId,
stripePaymentIntentId,
stripeCheckoutSessionId,
stripeEventId,
amount,
currency,
status,
eventType,
paidAt
]);
} catch (e) {
console.error("⚠️ Error insertando protection_payment_events:", e.message);
}
};
try {
switch (event.type) {
// =====================================================
// 1. CHECKOUT COMPLETADO
// =====================================================
case "checkout.session.completed": {
const session = event.data.object;
const metadata = session.metadata || {};
const paymentType = metadata.type;
// 🟢 PLAN DE PROTECCIÓN
if (paymentType === "protection_plan") {
const subscriptionId = parseInt(metadata.subscription_id, 10);
const ownerId = parseInt(metadata.owner_id, 10);
if (!subscriptionId || !ownerId) {
console.warn("⚠️ checkout.session.completed sin metadata válida para protection_plan");
break;
}
await pool.query(`
UPDATE protection_subscriptions
SET
payment_status = 'pagado',
status = 'activo',
stripe_customer_id = COALESCE($1, stripe_customer_id),
stripe_subscription_id = COALESCE($2, stripe_subscription_id),
started_at = COALESCE(started_at, NOW()),
last_payment_at = NOW(),
updated_at = NOW()
WHERE id = $3 AND company_id = $4
`, [
session.customer ? String(session.customer) : null,
session.subscription ? String(session.subscription) : null,
subscriptionId,
ownerId
]);
await pool.query(`
INSERT INTO protection_activity (company_id, type, description)
VALUES ($1, 'cobro', $2)
`, [
ownerId,
`Alta confirmada por Stripe. Suscripción #${subscriptionId} activada correctamente.`
]);
await insertarEventoPago({
subscriptionId,
companyId: ownerId,
stripeCheckoutSessionId: session.id || null,
stripePaymentIntentId: session.payment_intent || null,
stripeEventId: event.id,
amount: ((session.amount_total || 0) / 100),
currency: session.currency || "eur",
status: "paid",
eventType: event.type,
paidAt: new Date()
});
console.log(`✅ [STRIPE] Alta plan activada. Sub ID ${subscriptionId}`);
}
// 🔵 PRESUPUESTO NORMAL
else {
const budgetId = session.metadata?.budget_id;
const ownerId = session.metadata?.owner_id;
const amountTotal = ((session.amount_total || 0) / 100).toFixed(2);
if (!budgetId || !ownerId) {
console.warn("⚠️ checkout.session.completed de presupuesto sin metadata suficiente");
break;
}
await pool.query(
"UPDATE budgets SET status = 'paid' WHERE id = $1 AND owner_id = $2",
[budgetId, ownerId]
);
const sq = await pool.query(`
SELECT id, raw_data
FROM scraped_services
WHERE service_ref = $1 AND owner_id = $2
`, [`PRE-${budgetId}`, ownerId]);
if (sq.rowCount > 0) {
const serviceId = sq.rows[0].id;
let rawData = sq.rows[0].raw_data || {};
rawData.is_paid = true;
await pool.query(
"UPDATE scraped_services SET raw_data = $1 WHERE id = $2",
[JSON.stringify(rawData), serviceId]
);
await pool.query(`
INSERT INTO service_financials (scraped_id, amount, payment_method, is_paid)
VALUES ($1, $2, 'Tarjeta (Stripe)', true)
ON CONFLICT (scraped_id)
DO UPDATE SET
amount = EXCLUDED.amount,
is_paid = true,
payment_method = 'Tarjeta (Stripe)',
updated_at = NOW()
`, [serviceId, amountTotal]);
await pool.query(`
INSERT INTO scraped_service_logs (scraped_id, user_name, action, details)
VALUES ($1, $2, $3, $4)
`, [
serviceId,
"Stripe API",
"Pago Confirmado",
`El cliente ha abonado ${amountTotal}€ por pasarela segura.`
]);
}
const ownerQ = await pool.query("SELECT phone FROM users WHERE id = $1", [ownerId]);
if (ownerQ.rowCount > 0) {
const msgWa = `💰 *¡PAGO RECIBIDO (STRIPE)!*\n\nSe acaba de confirmar el pago con tarjeta del presupuesto *PRE-${budgetId}* por un importe de *${amountTotal}€*.\n\nEl sistema lo ha marcado como pagado automáticamente.`;
sendWhatsAppAuto(ownerQ.rows[0].phone, msgWa, `cliente_${ownerId}`, false).catch(console.error);
}
console.log(`✅ [STRIPE] Presupuesto PRE-${budgetId} pagado`);
}
break;
}
// =====================================================
// 2. FACTURA PAGADA (RENOVACIONES Y COBROS REALES)
// =====================================================
case "invoice.paid": {
const invoice = event.data.object;
const stripeSubscriptionId = invoice.subscription ? String(invoice.subscription) : null;
if (!stripeSubscriptionId) break;
const subQ = await pool.query(`
SELECT id, company_id
FROM protection_subscriptions
WHERE stripe_subscription_id = $1
ORDER BY created_at DESC
LIMIT 1
`, [stripeSubscriptionId]);
if (subQ.rowCount === 0) {
console.warn(`⚠️ invoice.paid sin subscription local para ${stripeSubscriptionId}`);
break;
}
const localSub = subQ.rows[0];
const periodStartUnix =
invoice.lines?.data?.[0]?.period?.start || null;
const periodEndUnix =
invoice.lines?.data?.[0]?.period?.end || null;
await pool.query(`
UPDATE protection_subscriptions
SET
payment_status = 'pagado',
status = 'activo',
stripe_customer_id = COALESCE($1, stripe_customer_id),
current_period_start = COALESCE(to_timestamp($2), current_period_start),
current_period_end = COALESCE(to_timestamp($3), current_period_end),
renewal_date = CASE
WHEN $3 IS NOT NULL THEN to_timestamp($3)::date
ELSE renewal_date
END,
last_payment_at = NOW(),
updated_at = NOW()
WHERE id = $4
`, [
invoice.customer ? String(invoice.customer) : null,
periodStartUnix,
periodEndUnix,
localSub.id
]);
await pool.query(`
INSERT INTO protection_activity (company_id, type, description)
VALUES ($1, 'cobro', $2)
`, [
localSub.company_id,
`Cobro mensual confirmado por Stripe para la suscripción #${localSub.id}.`
]);
await insertarEventoPago({
subscriptionId: localSub.id,
companyId: localSub.company_id,
stripeInvoiceId: invoice.id || null,
stripePaymentIntentId: invoice.payment_intent || null,
stripeEventId: event.id,
amount: ((invoice.amount_paid || 0) / 100),
currency: invoice.currency || "eur",
status: "paid",
eventType: event.type,
paidAt: new Date()
});
console.log(`✅ [STRIPE] invoice.paid procesado para sub local ${localSub.id}`);
break;
}
// =====================================================
// 3. FACTURA FALLIDA
// =====================================================
case "invoice.payment_failed": {
const invoice = event.data.object;
const stripeSubscriptionId = invoice.subscription ? String(invoice.subscription) : null;
if (!stripeSubscriptionId) break;
const subQ = await pool.query(`
SELECT id, company_id
FROM protection_subscriptions
WHERE stripe_subscription_id = $1
ORDER BY created_at DESC
LIMIT 1
`, [stripeSubscriptionId]);
if (subQ.rowCount === 0) break;
const localSub = subQ.rows[0];
await pool.query(`
UPDATE protection_subscriptions
SET
payment_status = 'impagado',
status = 'suspendido',
updated_at = NOW()
WHERE id = $1
`, [localSub.id]);
await pool.query(`
INSERT INTO protection_activity (company_id, type, description)
VALUES ($1, 'cobro', $2)
`, [
localSub.company_id,
`Pago fallido detectado por Stripe en la suscripción #${localSub.id}.`
]);
await insertarEventoPago({
subscriptionId: localSub.id,
companyId: localSub.company_id,
stripeInvoiceId: invoice.id || null,
stripePaymentIntentId: invoice.payment_intent || null,
stripeEventId: event.id,
amount: ((invoice.amount_due || 0) / 100),
currency: invoice.currency || "eur",
status: "failed",
eventType: event.type,
paidAt: null
});
console.log(`⚠️ [STRIPE] invoice.payment_failed para sub local ${localSub.id}`);
break;
}
// =====================================================
// 4. SUSCRIPCIÓN ACTUALIZADA
// =====================================================
case "customer.subscription.updated": {
const subscription = event.data.object;
const stripeSubscriptionId = subscription.id ? String(subscription.id) : null;
if (!stripeSubscriptionId) break;
await pool.query(`
UPDATE protection_subscriptions
SET
stripe_customer_id = COALESCE($1, stripe_customer_id),
current_period_start = COALESCE(to_timestamp($2), current_period_start),
current_period_end = COALESCE(to_timestamp($3), current_period_end),
renewal_date = CASE
WHEN $3 IS NOT NULL THEN to_timestamp($3)::date
ELSE renewal_date
END,
cancel_at_period_end = COALESCE($4, cancel_at_period_end),
status = CASE
WHEN $5 = 'active' THEN 'activo'
WHEN $5 IN ('past_due', 'unpaid') THEN 'suspendido'
WHEN $5 IN ('canceled', 'incomplete_expired') THEN 'cancelado'
ELSE status
END,
payment_status = CASE
WHEN $5 = 'active' THEN 'pagado'
WHEN $5 IN ('past_due', 'unpaid') THEN 'impagado'
ELSE payment_status
END,
updated_at = NOW()
WHERE stripe_subscription_id = $6
`, [
subscription.customer ? String(subscription.customer) : null,
subscription.current_period_start || null,
subscription.current_period_end || null,
subscription.cancel_at_period_end ?? false,
subscription.status || null,
stripeSubscriptionId
]);
console.log(`🔄 [STRIPE] customer.subscription.updated ${stripeSubscriptionId}`);
break;
}
// =====================================================
// 5. SUSCRIPCIÓN CANCELADA / TERMINADA
// =====================================================
case "customer.subscription.deleted": {
const subscription = event.data.object;
const stripeSubscriptionId = subscription.id ? String(subscription.id) : null;
if (!stripeSubscriptionId) break;
await pool.query(`
UPDATE protection_subscriptions
SET
status = 'cancelado',
cancel_at_period_end = false,
cancelled_at = NOW(),
ended_at = NOW(),
updated_at = NOW()
WHERE stripe_subscription_id = $1
`, [stripeSubscriptionId]);
console.log(`🛑 [STRIPE] customer.subscription.deleted ${stripeSubscriptionId}`);
break;
}
default:
console.log(`Evento Stripe ignorado: ${event.type}`);
break;
}
return res.json({ received: true });
} catch (e) {
console.error("❌ Error grave procesando Webhook de Stripe:", e.message);
return res.status(500).send(`Webhook Error: ${e.message}`);
}
});
// Límites de subida para logotipos en Base64
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ limit: '10mb', extended: true }));
// VARIABLES DE ENTORNO
// --- 1. VARIABLES DE ENTORNO ---
const {
DATABASE_URL,
JWT_SECRET,
EVOLUTION_BASE_URL,
EVOLUTION_API_KEY,
EVOLUTION_INSTANCE,
OPENAI_API_KEY, // 🔔 LEER LLAVE
OPENAI_MODEL, // 🔔 LEER MODELO
STRIPE_WEBHOOK_SECRET
} = process.env;
// --- 2. INICIALIZACIÓN GLOBAL DEL MOTOR IA (ESTO ES LO QUE TE FALTABA) ---
const openai = new OpenAI({
apiKey: OPENAI_API_KEY,
});
// --- 3. DIAGNÓSTICO DE INICIO ---
console.log("------------------------------------------------");
console.log("🚀 VERSIÓN COMPLETA - INTEGRA REPARA SAAS");
console.log("------------------------------------------------");
if (!STRIPE_WEBHOOK_SECRET) {
console.error("⚠️ AVISO: Falta STRIPE_WEBHOOK_SECRET en variables de entorno.");
} else {
console.log("✅ Stripe Webhook Secret detectado.");
}
if (!OPENAI_API_KEY) {
console.error("⚠️ AVISO: Falta OPENAI_API_KEY en variables de entorno.");
} else {
console.log("✅ OpenAI API Key detectada. IA lista para operar.");
}
if (!EVOLUTION_BASE_URL) console.error("⚠️ AVISO: Falta EVOLUTION_BASE_URL");
else console.log("✅ Evolution URL:", EVOLUTION_BASE_URL);
if (!EVOLUTION_INSTANCE) console.error("⚠️ AVISO: Falta EVOLUTION_INSTANCE");
else console.log("✅ Instancia Notificaciones:", EVOLUTION_INSTANCE);
console.log("------------------------------------------------");
if (!DATABASE_URL || !JWT_SECRET) process.exit(1);
const pool = new Pool({ connectionString: DATABASE_URL, ssl: false });
// ==========================================
// 💰 CONFIGURACIÓN DE PLANES (SAAS)
// ==========================================
const PLAN_LIMITS = {
'free': { name: 'Básico Gratuito', whatsapp_enabled: false, templates_enabled: false, automation_enabled: false },
'standard': { name: 'Estándar', whatsapp_enabled: true, templates_enabled: true, automation_enabled: false },
'pro': { name: 'Profesional', whatsapp_enabled: true, templates_enabled: true, automation_enabled: true }
};
// ==========================================
// 🧠 AUTO-ACTUALIZACIÓN DB
// ==========================================
async function autoUpdateDB() {
const client = await pool.connect();
try {
console.log("🔄 Verificando estructura DB...");
await client.query(`
-- USUARIOS
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
phone TEXT NOT NULL,
email TEXT NOT NULL,
dni TEXT,
address TEXT,
password_hash TEXT NOT NULL,
is_verified BOOLEAN DEFAULT FALSE,
owner_id INT,
role TEXT DEFAULT 'operario',
company_slug TEXT UNIQUE,
plan_tier TEXT DEFAULT 'free',
subscription_status TEXT DEFAULT 'active',
paid_providers_count INT DEFAULT 0,
zones JSONB DEFAULT '[]',
status TEXT DEFAULT 'active',
created_at TIMESTAMP DEFAULT NOW()
);
-- COLA DEL ROBOT
CREATE TABLE IF NOT EXISTS robot_queue (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
provider TEXT DEFAULT 'homeserve',
service_number TEXT NOT NULL,
new_status TEXT NOT NULL,
appointment_date TEXT,
observation TEXT,
inform_client BOOLEAN DEFAULT FALSE,
status TEXT DEFAULT 'PENDING', -- PENDING, RUNNING, DONE, FAILED
error_msg TEXT,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS login_codes (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
phone TEXT NOT NULL,
code_hash TEXT NOT NULL,
purpose TEXT DEFAULT 'register_verify',
consumed_at TIMESTAMP,
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- CONFIGURACIÓN NEGOCIO
CREATE TABLE IF NOT EXISTS guilds (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS user_guilds (
user_id INT REFERENCES users(id) ON DELETE CASCADE,
guild_id INT REFERENCES guilds(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, guild_id)
);
CREATE TABLE IF NOT EXISTS companies (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
cif TEXT,
email TEXT,
phone TEXT,
address TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
-- CLIENTES (CRM)
CREATE TABLE IF NOT EXISTS clients (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
full_name TEXT NOT NULL,
phone TEXT NOT NULL,
email TEXT,
addresses JSONB DEFAULT '[]',
notes TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
-- ESTADOS Y PLANTILLAS
CREATE TABLE IF NOT EXISTS service_statuses (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
color TEXT DEFAULT 'gray',
is_default BOOLEAN DEFAULT FALSE,
is_final BOOLEAN DEFAULT FALSE,
is_system BOOLEAN DEFAULT FALSE, -- AÑADIDO: Identificador de estados imborrables
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS message_templates (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
type TEXT NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT NOW(),
UNIQUE(owner_id, type)
);
-- ZONAS
CREATE TABLE IF NOT EXISTS zones (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
owner_id INT,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS user_zones (
user_id INT REFERENCES users(id) ON DELETE CASCADE,
zone_id INT REFERENCES zones(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, zone_id)
);
-- 🤖 ROBOTS / PROVEEDORES
CREATE TABLE IF NOT EXISTS provider_credentials (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
provider TEXT NOT NULL,
username TEXT NOT NULL,
password_hash TEXT NOT NULL,
last_sync TIMESTAMP,
status TEXT DEFAULT 'active',
UNIQUE(owner_id, provider)
);
CREATE TABLE IF NOT EXISTS scraped_services (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
provider TEXT NOT NULL,
service_ref TEXT NOT NULL,
raw_data JSONB,
status TEXT DEFAULT 'pending',
automation_status TEXT DEFAULT 'manual',
created_at TIMESTAMP DEFAULT NOW(),
UNIQUE(owner_id, provider, service_ref)
);
-- 🗺️ TABLA DE MAPEO DE VARIABLES
CREATE TABLE IF NOT EXISTS variable_mappings (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
provider TEXT NOT NULL,
original_key TEXT NOT NULL,
target_key TEXT,
is_ignored BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT NOW(),
UNIQUE(owner_id, provider, original_key)
);
-- SERVICIOS (PRINCIPAL)
CREATE TABLE IF NOT EXISTS services (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
client_id INT REFERENCES clients(id) ON DELETE SET NULL,
status_id INT REFERENCES service_statuses(id) ON DELETE SET NULL,
guild_id INT REFERENCES guilds(id) ON DELETE SET NULL,
assigned_to INT REFERENCES users(id) ON DELETE SET NULL,
title TEXT,
description TEXT,
contact_phone TEXT,
contact_name TEXT,
address TEXT,
email TEXT,
scheduled_date DATE DEFAULT CURRENT_DATE,
scheduled_time TIME DEFAULT CURRENT_TIME,
duration_minutes INT DEFAULT 30,
is_urgent BOOLEAN DEFAULT FALSE,
is_company BOOLEAN DEFAULT FALSE,
company_id INT REFERENCES companies(id) ON DELETE SET NULL,
company_ref TEXT,
internal_notes TEXT,
client_notes TEXT,
import_source TEXT,
provider_data JSONB DEFAULT '{}',
closed_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS service_logs (
id SERIAL PRIMARY KEY,
service_id INT REFERENCES services(id) ON DELETE CASCADE,
user_id INT REFERENCES users(id) ON DELETE SET NULL,
old_status_id INT REFERENCES service_statuses(id),
new_status_id INT REFERENCES service_statuses(id),
comment TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
-- TABLA PARA ASIGNACIÓN AUTOMÁTICA
CREATE TABLE IF NOT EXISTS assignment_pings (
id SERIAL PRIMARY KEY,
scraped_id INT NOT NULL,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
token TEXT UNIQUE NOT NULL,
status TEXT DEFAULT 'pending',
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
-- ==========================================
-- 🛡️ MÓDULO SAAS: PLANES DE PROTECCIÓN
-- ==========================================
CREATE TABLE IF NOT EXISTS protection_config (
company_id INT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
name TEXT,
email TEXT,
phone TEXT,
auto_renew BOOLEAN DEFAULT TRUE,
pre_notice BOOLEAN DEFAULT TRUE,
billing_method TEXT DEFAULT 'stripe',
contract_text TEXT,
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS protection_plans (
id SERIAL PRIMARY KEY,
company_id INT REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
type TEXT NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00,
renewal_price DECIMAL(10,2) DEFAULT 0.00,
urgencies_limit INT DEFAULT 0,
bricos_limit INT DEFAULT 0,
coverages TEXT,
billing_interval TEXT DEFAULT 'month',
billing_interval_count INT DEFAULT 1,
stripe_price_id TEXT,
is_active BOOLEAN DEFAULT TRUE,
updated_at TIMESTAMP DEFAULT NOW(),
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS protection_subscriptions (
id SERIAL PRIMARY KEY,
company_id INT REFERENCES users(id) ON DELETE CASCADE,
plan_id INT REFERENCES protection_plans(id) ON DELETE RESTRICT,
client_name TEXT NOT NULL,
client_dni TEXT,
client_phone TEXT,
payment_status TEXT DEFAULT 'impagado',
status TEXT DEFAULT 'pendiente_pago',
custom_discount INT DEFAULT 0,
bricos_used INT DEFAULT 0,
urgencies_used INT DEFAULT 0,
renewal_date DATE,
contract_pdf_url TEXT,
stripe_session_id TEXT,
stripe_subscription_id TEXT,
stripe_customer_id TEXT,
stripe_price_id TEXT,
started_at TIMESTAMP,
current_period_start TIMESTAMP,
current_period_end TIMESTAMP,
last_payment_at TIMESTAMP,
cancel_at_period_end BOOLEAN DEFAULT FALSE,
cancelled_at TIMESTAMP,
ended_at TIMESTAMP,
updated_at TIMESTAMP DEFAULT NOW(),
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS protection_payment_events (
id SERIAL PRIMARY KEY,
subscription_id INT REFERENCES protection_subscriptions(id) ON DELETE CASCADE,
company_id INT REFERENCES users(id) ON DELETE CASCADE,
stripe_invoice_id TEXT,
stripe_payment_intent_id TEXT,
stripe_checkout_session_id TEXT,
stripe_event_id TEXT,
amount DECIMAL(10,2) DEFAULT 0.00,
currency TEXT DEFAULT 'eur',
status TEXT NOT NULL,
event_type TEXT,
paid_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW()
);
-- 💬 CHAT Y NOTAS INTERNAS
CREATE TABLE IF NOT EXISTS service_communications (
id SERIAL PRIMARY KEY,
scraped_id INT REFERENCES scraped_services(id) ON DELETE CASCADE,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
sender_id INT REFERENCES users(id) ON DELETE SET NULL,
sender_name TEXT NOT NULL,
sender_role TEXT,
message TEXT NOT NULL,
is_internal BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT NOW()
);
`);
// PARCHE DE ACTUALIZACIÓN
await client.query(`
DO $$ BEGIN
-- ==========================================
-- 🛡️ PARCHES MÓDULO PROTECCIÓN
-- ==========================================
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_plans' AND column_name='billing_interval') THEN
ALTER TABLE protection_plans ADD COLUMN billing_interval TEXT DEFAULT 'month';
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_plans' AND column_name='billing_interval_count') THEN
ALTER TABLE protection_plans ADD COLUMN billing_interval_count INT DEFAULT 1;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_plans' AND column_name='stripe_price_id') THEN
ALTER TABLE protection_plans ADD COLUMN stripe_price_id TEXT;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_plans' AND column_name='is_active') THEN
ALTER TABLE protection_plans ADD COLUMN is_active BOOLEAN DEFAULT TRUE;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_plans' AND column_name='updated_at') THEN
ALTER TABLE protection_plans ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='stripe_session_id') THEN
ALTER TABLE protection_subscriptions ADD COLUMN stripe_session_id TEXT;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='stripe_subscription_id') THEN
ALTER TABLE protection_subscriptions ADD COLUMN stripe_subscription_id TEXT;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='stripe_customer_id') THEN
ALTER TABLE protection_subscriptions ADD COLUMN stripe_customer_id TEXT;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='stripe_price_id') THEN
ALTER TABLE protection_subscriptions ADD COLUMN stripe_price_id TEXT;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='started_at') THEN
ALTER TABLE protection_subscriptions ADD COLUMN started_at TIMESTAMP;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='current_period_start') THEN
ALTER TABLE protection_subscriptions ADD COLUMN current_period_start TIMESTAMP;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='current_period_end') THEN
ALTER TABLE protection_subscriptions ADD COLUMN current_period_end TIMESTAMP;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='last_payment_at') THEN
ALTER TABLE protection_subscriptions ADD COLUMN last_payment_at TIMESTAMP;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='cancel_at_period_end') THEN
ALTER TABLE protection_subscriptions ADD COLUMN cancel_at_period_end BOOLEAN DEFAULT FALSE;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='cancelled_at') THEN
ALTER TABLE protection_subscriptions ADD COLUMN cancelled_at TIMESTAMP;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='ended_at') THEN
ALTER TABLE protection_subscriptions ADD COLUMN ended_at TIMESTAMP;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='protection_subscriptions' AND column_name='updated_at') THEN
ALTER TABLE protection_subscriptions ADD COLUMN updated_at TIMESTAMP DEFAULT NOW();
END IF;
CREATE TABLE IF NOT EXISTS protection_payment_events (
id SERIAL PRIMARY KEY,
subscription_id INT REFERENCES protection_subscriptions(id) ON DELETE CASCADE,
company_id INT REFERENCES users(id) ON DELETE CASCADE,
stripe_invoice_id TEXT,
stripe_payment_intent_id TEXT,
stripe_checkout_session_id TEXT,
stripe_event_id TEXT,
amount DECIMAL(10,2) DEFAULT 0.00,
currency TEXT DEFAULT 'eur',
status TEXT NOT NULL,
event_type TEXT,
paid_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW()
);
UPDATE protection_subscriptions
SET payment_status = 'impagado'
WHERE payment_status IS NULL OR payment_status = '';
UPDATE protection_subscriptions
SET status = 'pendiente_pago'
WHERE status IS NULL OR status = '';
ALTER TABLE protection_subscriptions ALTER COLUMN payment_status SET DEFAULT 'impagado';
ALTER TABLE protection_subscriptions ALTER COLUMN status SET DEFAULT 'pendiente_pago';
-- 🟢 AÑADIDO: Fecha de última lectura del chat por el operario
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='scraped_services' AND column_name='last_chat_read_worker') THEN
ALTER TABLE scraped_services ADD COLUMN last_chat_read_worker TIMESTAMP DEFAULT '2000-01-01';
END IF;
-- AÑADIDO: Control de envíos automáticos de urgencias por proveedor
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='provider_credentials' AND column_name='auto_dispatch') THEN
ALTER TABLE provider_credentials ADD COLUMN auto_dispatch BOOLEAN DEFAULT FALSE;
END IF;
-- AÑADIDO: Token mágico para el Portal del Cliente
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='clients' AND column_name='portal_token') THEN
ALTER TABLE clients ADD COLUMN portal_token TEXT UNIQUE;
UPDATE clients SET portal_token = substr(md5(random()::text || id::text), 1, 12) WHERE portal_token IS NULL;
ALTER TABLE clients ALTER COLUMN portal_token SET DEFAULT substr(md5(random()::text || clock_timestamp()::text), 1, 12);
END IF;
-- AÑADIDO: Motor de Ranking
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='ranking_score') THEN
ALTER TABLE users ADD COLUMN ranking_score NUMERIC DEFAULT 50.0;
ALTER TABLE users ADD COLUMN ranking_data JSONB DEFAULT '{}'::jsonb;
END IF;
-- NUEVO: Columna para colores personalizados de la App
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='app_settings') THEN
ALTER TABLE users ADD COLUMN app_settings JSONB DEFAULT '{"primary": "#1e3a8a", "secondary": "#2563eb", "bg": "#f8fafc"}';
END IF;
-- AÑADIDO: Permiso para coger servicios libres de la bolsa
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='can_claim_services') THEN
ALTER TABLE users ADD COLUMN can_claim_services BOOLEAN DEFAULT TRUE;
END IF;
-- AÑADIDO: Columna para guardar la configuración de WhatsApp
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='wa_settings') THEN
ALTER TABLE users ADD COLUMN wa_settings JSONB DEFAULT '{}';
END IF;
-- AÑADIDO: Configuración del Portal del Cliente
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='company_logo') THEN
ALTER TABLE users ADD COLUMN company_logo TEXT;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='portal_settings') THEN
ALTER TABLE users ADD COLUMN portal_settings JSONB DEFAULT '{"m_start":"09:00", "m_end":"14:00", "a_start":"16:00", "a_end":"19:00"}';
END IF;
-- AÑADIDO: Columna física de operario para el panel operativo
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='scraped_services' AND column_name='assigned_to') THEN
ALTER TABLE scraped_services ADD COLUMN assigned_to INT REFERENCES users(id);
END IF;
-- ASEGURAR COLUMNA URGENTE EN SCRAPED
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='scraped_services' AND column_name='is_urgent') THEN
ALTER TABLE scraped_services ADD COLUMN is_urgent BOOLEAN DEFAULT FALSE;
END IF;
-- AÑADIDO: Columna de palabras clave IA para los gremios
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='guilds' AND column_name='ia_keywords') THEN
ALTER TABLE guilds ADD COLUMN ia_keywords JSONB DEFAULT '[]';
END IF;
-- AÑADIDO: Columna para marcar estados imborrables del sistema
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='service_statuses' AND column_name='is_system') THEN
ALTER TABLE service_statuses ADD COLUMN is_system BOOLEAN DEFAULT FALSE;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='services' AND column_name='client_id') THEN ALTER TABLE services ADD COLUMN client_id INT REFERENCES clients(id) ON DELETE SET NULL; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='services' AND column_name='status_id') THEN ALTER TABLE services ADD COLUMN status_id INT REFERENCES service_statuses(id) ON DELETE SET NULL; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='services' AND column_name='contact_phone') THEN ALTER TABLE services ADD COLUMN contact_phone TEXT; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='services' AND column_name='contact_name') THEN ALTER TABLE services ADD COLUMN contact_name TEXT; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='plan_tier') THEN ALTER TABLE users ADD COLUMN plan_tier TEXT DEFAULT 'free'; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='company_slug') THEN ALTER TABLE users ADD COLUMN company_slug TEXT UNIQUE; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='zones') THEN ALTER TABLE users ADD COLUMN zones JSONB DEFAULT '[]'; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='users' AND column_name='status') THEN ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='services' AND column_name='provider_data') THEN ALTER TABLE services ADD COLUMN provider_data JSONB DEFAULT '{}'; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='services' AND column_name='import_source') THEN ALTER TABLE services ADD COLUMN import_source TEXT; END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='scraped_services' AND column_name='automation_status') THEN ALTER TABLE scraped_services ADD COLUMN automation_status TEXT DEFAULT 'manual'; END IF;
-- AÑADIDO: Token mágico para el Portal del Cliente
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='clients' AND column_name='portal_token') THEN
ALTER TABLE clients ADD COLUMN portal_token TEXT UNIQUE;
UPDATE clients SET portal_token = substr(md5(random()::text || id::text), 1, 12) WHERE portal_token IS NULL;
ALTER TABLE clients ALTER COLUMN portal_token SET DEFAULT substr(md5(random()::text || clock_timestamp()::text), 1, 12);
END IF;
BEGIN ALTER TABLE users DROP CONSTRAINT IF EXISTS users_phone_key; EXCEPTION WHEN OTHERS THEN NULL; END;
BEGIN ALTER TABLE users DROP CONSTRAINT IF EXISTS users_email_key; EXCEPTION WHEN OTHERS THEN NULL; END;
END $$;
`);
console.log("✅ DB Sincronizada.");
} catch (e) { console.error("❌ Error DB:", e); } finally { client.release(); }
}
// ==========================================
// 📞 HELPERS (UNIFICACIÓN TOTAL DE TELÉFONOS)
// ==========================================
// 1. Extrae SIEMPRE exactamente 9 dígitos (Formato Nacional: 600123456)
function extractValidPhone(rawPhone) {
if (!rawPhone) return "";
let str = String(rawPhone).replace(/\D/g, ""); // Borra todo lo que no sea número (espacios, letras, +, -)
if (str.startsWith("0034")) str = str.slice(4);
if (str.startsWith("34") && str.length >= 11) str = str.slice(2);
const match = str.match(/[6789]\d{8}/);
return match ? match[0] : (str.length >= 9 ? str.slice(-9) : str);
}
// 2. Normaliza SIEMPRE a Formato Internacional para la Base de Datos (+34600123456)
function normalizePhone(phone) {
const clean = extractValidPhone(phone);
return clean ? "+34" + clean : "";
}
function signToken(user) { const accountId = user.owner_id || user.id; return jwt.sign({ sub: user.id, email: user.email, phone: user.phone, role: user.role || 'operario', accountId }, JWT_SECRET, { expiresIn: "30d" }); }
function authMiddleware(req, res, next) { const h = req.headers.authorization || ""; const token = h.startsWith("Bearer ") ? h.slice(7) : ""; if (!token) return res.status(401).json({ ok: false, error: "No token" }); try { req.user = jwt.verify(token, JWT_SECRET); next(); } catch { return res.status(401).json({ ok: false, error: "Token inválido" }); } }
function genCode6() { return String(Math.floor(100000 + Math.random() * 900000)); }
// ==========================================
// 🛡️ ESCUDO DE TITANIO: ANTI-SOLAPAMIENTOS BBDD
// ==========================================
async function comprobarDisponibilidad(ownerId, workerId, date, time, durationMin, excludeId = null) {
try {
if (!ownerId || !workerId || !date || !time) {
return { choca: false };
}
const parseTimeToMinutes = (value) => {
if (!value) return null;
const parts = String(value).trim().split(":");
if (parts.length < 2) return null;
const hh = parseInt(parts[0], 10);
const mm = parseInt(parts[1], 10);
if (Number.isNaN(hh) || Number.isNaN(mm)) return null;
return (hh * 60) + mm;
};
const requestedStart = parseTimeToMinutes(time);
const requestedDuration = Math.max(parseInt(durationMin, 10) || 60, 1);
if (requestedStart === null) {
return { choca: false };
}
const requestedEnd = requestedStart + requestedDuration;
let query = `
SELECT
id,
service_ref,
COALESCE(
NULLIF(raw_data->>'scheduled_time', ''),
CASE
WHEN raw_data->>'appointment_status' = 'pending' THEN NULLIF(raw_data->>'requested_time', '')
ELSE NULL
END
) as effective_time,
COALESCE(
NULLIF(raw_data->>'duration_minutes', ''),
'60'
) as effective_duration
FROM scraped_services
WHERE owner_id = $1
AND assigned_to = $2
AND status != 'archived'
AND (
raw_data->>'scheduled_date' = $3
OR (
raw_data->>'appointment_status' = 'pending'
AND raw_data->>'requested_date' = $3
)
)
`;
const params = [ownerId, workerId, date];
if (excludeId) {
query += ` AND id != $4`;
params.push(excludeId);
}
const q = await pool.query(query, params);
for (const row of q.rows) {
const start = parseTimeToMinutes(row.effective_time);
if (start === null) continue;
const duration = Math.max(parseInt(row.effective_duration, 10) || 60, 1);
const end = start + duration;
if (requestedStart < end && requestedEnd > start) {
return {
choca: true,
ref: row.service_ref || `ID-${row.id}`,
time: row.effective_time
};
}
}
return { choca: false };
} catch (e) {
console.error("❌ Error en comprobarDisponibilidad:", e.message);
return { choca: false };
}
}
// ==========================================
// 🔐 SISTEMA DE AUTENTICACIÓN (LOGIN Y SESIÓN)
// ==========================================
app.post("/auth/login", async (req, res) => {
try {
const { email, password } = req.body;
if (!email || !password) return res.status(400).json({ ok: false, error: "Faltan datos" });
const q = await pool.query("SELECT * FROM users WHERE email = $1 OR phone = $1 LIMIT 1", [email]);
if (q.rowCount === 0) return res.status(401).json({ ok: false, error: "Credenciales incorrectas" });
const user = q.rows[0];
if (user.is_verified === false) {
return res.status(403).json({
ok: false,
error: "Cuenta no verificada. Por favor, revisa tu WhatsApp o contacta con soporte.",
require_verification: true
});
}
if (user.status !== 'active') return res.status(403).json({ ok: false, error: "Cuenta inactiva o bloqueada" });
const valid = await bcrypt.compare(password, user.password_hash);
if (!valid) return res.status(401).json({ ok: false, error: "Credenciales incorrectas" });
// 🛡️ DEVOLVEMOS LA NORMALIDAD: Leemos el rol real de la base de datos
// (Si por algún casual estuviera vacío, le ponemos operario por seguridad)
const safeRole = user.role ? String(user.role).trim().toLowerCase() : 'operario';
user.role = safeRole;
const token = signToken(user);
res.json({
ok: true,
token,
user: {
id: user.id,
full_name: user.full_name,
role: safeRole, // Enviamos si es admin u operario correctamente
accountId: user.owner_id || user.id
}
});
} catch (e) {
console.error("Error en Login:", e);
res.status(500).json({ ok: false, error: "Error interno del servidor" });
}
});
// 🟢 RUTA RESUCITADA: VERIFICACIÓN DE SESIÓN (AUTH ME) 🟢
app.get("/auth/me", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT id, full_name, email, phone, role, company_slug, plan_tier FROM users WHERE id = $1", [req.user.sub]);
if (q.rowCount === 0) return res.status(404).json({ ok: false });
const userData = q.rows[0];
// 🛡️ EL EXORCISMO DEL ROL: Por si entra con sesión guardada
let safeRole = userData.role ? String(userData.role).trim().toLowerCase() : 'operario';
if (safeRole === '' || safeRole === 'null') safeRole = 'operario';
userData.role = safeRole;
res.json({ ok: true, user: userData });
} catch (e) {
res.status(500).json({ ok: false });
}
});
// ==========================================
// 📝 REGISTRO DE NUEVAS EMPRESAS (SAAS)
// ==========================================
app.post("/auth/register", async (req, res) => {
try {
const { fullName, email, phone, password, dni, address } = req.body;
if (!email || !password || !fullName || !phone) {
return res.status(400).json({ ok: false, error: "Faltan datos obligatorios" });
}
const p = normalizePhone(phone);
// 1. Verificamos si el email o teléfono ya están en uso
const check = await pool.query("SELECT id FROM users WHERE email = $1 OR phone = $2", [email, p]);
if (check.rowCount > 0) {
return res.status(400).json({ ok: false, error: "El email o teléfono ya están registrados" });
}
const hash = await bcrypt.hash(password, 10);
// 2. Creamos al usuario como ADMIN (dueño de su propia empresa/instancia)
// Lo creamos como NO verificado hasta que ponga el código
const insert = await pool.query(`
INSERT INTO users (full_name, email, phone, password_hash, dni, address, role, status, is_verified)
VALUES ($1, $2, $3, $4, $5, $6, 'admin', 'active', FALSE)
RETURNING id
`, [fullName, email, p, hash, dni || null, address || null]);
const newUserId = insert.rows[0].id;
// 3. Le asignamos su propio ID como owner_id (es el jefe de su panel)
await pool.query("UPDATE users SET owner_id = $1 WHERE id = $2", [newUserId, newUserId]);
// 4. Generamos el código de 6 dígitos para el WhatsApp
const code = genCode6();
const codeHash = await bcrypt.hash(code, 10);
await pool.query(`
INSERT INTO login_codes (user_id, phone, code_hash, purpose, expires_at)
VALUES ($1, $2, $3, 'register_verify', NOW() + INTERVAL '15 minutes')
`, [newUserId, p, codeHash]);
console.log(`🔐 [SISTEMA] Código de verificación para ${p}: ${code}`);
// 5. Enviamos el WhatsApp usando la instancia principal
// 👇 NUEVO: Le quitamos el símbolo "+" para que Evolution no dé error
const phoneForEvolution = p.replace('+', '');
const msg = `👋 *¡Bienvenido a IntegraRepara!*\n\nTu código de verificación es: *${code}*\n\nTiene una validez de 15 minutos.`;
if (process.env.EVOLUTION_INSTANCE) {
sendWhatsAppAuto(phoneForEvolution, msg, process.env.EVOLUTION_INSTANCE, false).catch(console.error);
} else {
console.warn("⚠️ No se pudo enviar el WhatsApp: Falta la variable EVOLUTION_INSTANCE en Coolify.");
}
res.json({ ok: true, message: "Código enviado" });
} catch (e) {
console.error("Error en Registro:", e);
res.status(500).json({ ok: false, error: "Error interno del servidor" });
}
});
// ==========================================
// ✅ VERIFICACIÓN DEL CÓDIGO (OTP)
// ==========================================
app.post("/auth/verify", async (req, res) => {
try {
const { phone, code } = req.body;
const p = normalizePhone(phone);
// Buscamos códigos pendientes para ese teléfono
const q = await pool.query(`
SELECT c.*, u.id as u_id, u.full_name, u.role, u.owner_id
FROM login_codes c
JOIN users u ON c.user_id = u.id
WHERE c.phone = $1 AND c.consumed_at IS NULL AND c.expires_at > NOW()
ORDER BY c.created_at DESC LIMIT 1
`, [p]);
if (q.rowCount === 0) return res.status(400).json({ ok: false, error: "Código inválido o caducado" });
const codeRecord = q.rows[0];
const valid = await bcrypt.compare(code, codeRecord.code_hash);
if (!valid) return res.status(400).json({ ok: false, error: "Código incorrecto" });
// Marcamos código como usado y usuario como verificado
await pool.query("UPDATE login_codes SET consumed_at = NOW() WHERE id = $1", [codeRecord.id]);
await pool.query("UPDATE users SET is_verified = TRUE WHERE id = $1", [codeRecord.u_id]);
// Generamos su token de sesión para que entre directo
const token = signToken({
id: codeRecord.u_id,
email: codeRecord.email, // Aquí si usaras email en el token
phone: codeRecord.phone,
role: codeRecord.role,
owner_id: codeRecord.owner_id
});
res.json({ ok: true, token, user: { id: codeRecord.u_id, full_name: codeRecord.full_name, role: codeRecord.role } });
} catch (e) {
console.error("Error en Verificación:", e);
res.status(500).json({ ok: false, error: "Error en el servidor" });
}
});
// ==========================================
// 🤖 PUENTE AUTOMÁTICO HACIA HOMESERVE
// ==========================================
async function triggerHomeServeRobot(ownerId, serviceId, eventType) {
try {
// 1. Obtener la configuración del robot y los datos del servicio
const userQ = await pool.query("SELECT wa_settings FROM users WHERE id=$1", [ownerId]);
const settings = userQ.rows[0]?.wa_settings?.robot_homeserve || {};
const rule = settings[eventType];
if (!rule || !rule.enabled) {
console.log(`🤖 [ROBOT HS] Regla '${eventType}' desactivada. Omitiendo.`);
return;
}
const svcQ = await pool.query(`
SELECT s.service_ref, s.raw_data, u.full_name as worker_name
FROM scraped_services s
LEFT JOIN users u ON s.assigned_to = u.id
WHERE s.id = $1
`, [serviceId]);
if (svcQ.rowCount === 0) return;
const s = svcQ.rows[0];
const raw = s.raw_data || {};
// 2. Calcular la fecha de Siguiente Acción (Formato exacto HomeServe DD/MM/YYYY)
let targetDate = new Date();
let formattedDate = "";
if (eventType === 'date' && raw.scheduled_date) {
// 🎯 SI ES "CITA", usamos estrictamente la fecha en la que se ha agendado
const [y, m, d] = raw.scheduled_date.split('-');
formattedDate = `${d.padStart(2, '0')}/${m.padStart(2, '0')}/${y}`;
} else {
// 📅 SI ES "ASIGNAR" o "NO LOCALIZADO", sumamos los días a partir de HOY
const daysToAdd = parseInt(rule.days_next) || 0;
targetDate.setDate(targetDate.getDate() + daysToAdd);
// Blindaje: Si la fecha resultante cae en fin de semana, la movemos al Lunes
if (targetDate.getDay() === 6) targetDate.setDate(targetDate.getDate() + 2); // Si es Sábado -> Suma 2 días (Lunes)
else if (targetDate.getDay() === 0) targetDate.setDate(targetDate.getDate() + 1); // Si es Domingo -> Suma 1 día (Lunes)
const dd = String(targetDate.getDate()).padStart(2, '0');
const mm = String(targetDate.getMonth() + 1).padStart(2, '0');
const yyyy = targetDate.getFullYear();
formattedDate = `${dd}/${mm}/${yyyy}`;
}
// 3. Traducir variables personalizadas
let text = rule.obs || "";
const clientName = raw["Nombre Cliente"] || raw["CLIENTE"] || "Cliente";
let clientPhone = raw["Teléfono"] || raw["TELEFONO"] || raw["TELEFONOS"] || "";
clientPhone = extractValidPhone(clientPhone);
// Formateo humano de la fecha para el texto de observaciones
let textoFechaCita = "hoy";
if (raw.scheduled_date) {
const [y, m, d] = raw.scheduled_date.split('-');
textoFechaCita = `${d.padStart(2, '0')}/${m.padStart(2, '0')}/${y}`;
} else {
// Si no hay cita agendada (Ej: No localizado), usamos la fecha de hoy para el texto
const hoy = new Date();
textoFechaCita = `${String(hoy.getDate()).padStart(2, '0')}/${String(hoy.getMonth() + 1).padStart(2, '0')}/${hoy.getFullYear()}`;
}
text = text.replace(/{{NOMBRE}}/g, clientName);
text = text.replace(/{{FECHA}}/g, textoFechaCita);
// Si no hay hora (ej: No localizado), ponemos la hora actual
const horaActual = `${String(new Date().getHours()).padStart(2, '0')}:${String(new Date().getMinutes()).padStart(2, '0')}`;
text = text.replace(/{{HORA}}/g, raw["scheduled_time"] || horaActual);
text = text.replace(/{{TELEFONO}}/g, clientPhone);
text = text.replace(/{{REFERENCIA}}/g, s.service_ref);
text = text.replace(/{{NOMBRE_OPERARIO}}/g, s.worker_name || "el técnico");
// 4. ESCUDO ANTI-POPUP HOMESERVE (Mínimo 20 caracteres)
if (text.length < 25) {
text += " - Actualizado por el sistema.";
}
// 5. Mandar la orden definitiva al Robot
await pool.query(`
INSERT INTO robot_queue (owner_id, provider, service_number, new_status, appointment_date, observation, inform_client)
VALUES ($1, 'homeserve', $2, $3, $4, $5, $6)
`, [ownerId, s.service_ref, rule.status, formattedDate, text, rule.check_inform]);
console.log(`🤖 [ROBOT HS] Orden enviada a la cola para exp ${s.service_ref} (Regla: ${eventType}) | Fecha: ${formattedDate} | Obs: "${text}"`);
} catch(e) {
console.error("❌ Error en puente HomeServe:", e);
}
}
// ==========================================
// 🤖 PUENTE AUTOMÁTICO HACIA MULTIASISTENCIA
// ==========================================
async function triggerMultiRobot(ownerId, serviceId, eventType) {
try {
const userQ = await pool.query("SELECT wa_settings FROM users WHERE id=$1", [ownerId]);
const settings = userQ.rows[0]?.wa_settings?.robot_multi || {};
const rule = settings[eventType];
if (!rule || !rule.enabled) {
console.log(`🤖 [ROBOT MULTI] Regla '${eventType}' desactivada. Omitiendo.`);
return;
}
const svcQ = await pool.query(`
SELECT s.service_ref, s.raw_data, u.full_name as worker_name
FROM scraped_services s
LEFT JOIN users u ON s.assigned_to = u.id
WHERE s.id = $1
`, [serviceId]);
if (svcQ.rowCount === 0) return;
const s = svcQ.rows[0];
const raw = s.raw_data || {};
let targetDate = new Date();
let formattedDate = "";
let formattedTime = "";
if (eventType === 'date' && raw.scheduled_date) {
const [y, m, d] = raw.scheduled_date.split('-');
formattedDate = `${d.padStart(2, '0')}/${m.padStart(2, '0')}/${y}`;
formattedTime = raw.scheduled_time || "";
} else {
const daysToAdd = parseInt(rule.days_next) || 0;
targetDate.setDate(targetDate.getDate() + daysToAdd);
if (targetDate.getDay() === 6) targetDate.setDate(targetDate.getDate() + 2);
else if (targetDate.getDay() === 0) targetDate.setDate(targetDate.getDate() + 1);
const dd = String(targetDate.getDate()).padStart(2, '0');
const mm = String(targetDate.getMonth() + 1).padStart(2, '0');
const yyyy = targetDate.getFullYear();
formattedDate = `${dd}/${mm}/${yyyy}`;
const horaActual = `${String(new Date().getHours()).padStart(2, '0')}:${String(new Date().getMinutes()).padStart(2, '0')}`;
formattedTime = raw.scheduled_time || horaActual;
}
let text = rule.obs || "";
const clientName = raw["Nombre Cliente"] || raw["CLIENTE"] || "Cliente";
let clientPhone = raw["Teléfono"] || raw["TELEFONO"] || raw["TELEFONOS"] || "";
clientPhone = extractValidPhone(clientPhone);
let textoFechaCita = "hoy";
if (raw.scheduled_date) {
const [y, m, d] = raw.scheduled_date.split('-');
textoFechaCita = `${d.padStart(2, '0')}/${m.padStart(2, '0')}/${y}`;
} else {
const hoy = new Date();
textoFechaCita = `${String(hoy.getDate()).padStart(2, '0')}/${String(hoy.getMonth() + 1).padStart(2, '0')}/${hoy.getFullYear()}`;
}
text = text.replace(/{{NOMBRE}}/g, clientName);
text = text.replace(/{{FECHA}}/g, textoFechaCita);
text = text.replace(/{{HORA}}/g, formattedTime);
text = text.replace(/{{TELEFONO}}/g, clientPhone);
text = text.replace(/{{REFERENCIA}}/g, s.service_ref);
text = text.replace(/{{NOMBRE_OPERARIO}}/g, s.worker_name || "el técnico");
// Añadimos columna si no existe por si acaso
await pool.query(`ALTER TABLE robot_queue ADD COLUMN IF NOT EXISTS appointment_time TEXT;`).catch(() => {});
await pool.query(`
INSERT INTO robot_queue (owner_id, provider, service_number, new_status, appointment_date, appointment_time, observation, inform_client)
VALUES ($1, 'multiasistencia', $2, $3, $4, $5, $6, false)
`, [ownerId, s.service_ref, rule.status, formattedDate, formattedTime, text]);
console.log(`🤖 [ROBOT MULTI] Orden enviada a la cola para exp ${s.service_ref} (Regla: ${eventType}) | Fecha: ${formattedDate} | Hora: ${formattedTime}`);
} catch(e) {
console.error("❌ Error en puente Multiasistencia:", e);
}
}
// ==========================================
// 🕵️ ROBOT NOTARIO (TRAZABILIDAD TOTAL)
// ==========================================
async function registrarMovimiento(serviceId, userId, action, details) {
try {
let userName = "Sistema Robot";
if (userId) {
const u = await pool.query("SELECT full_name FROM users WHERE id=$1", [userId]);
if (u.rowCount > 0) userName = u.rows[0].full_name;
}
await pool.query(
"INSERT INTO scraped_service_logs (scraped_id, user_name, action, details) VALUES ($1, $2, $3, $4)",
[serviceId, userName, action, details || ""]
);
} catch (e) { console.error("Error Robot Notario:", e); }
}
// ==========================================
// 🧠 CEREBRO IA (WHATSAPP)
// ==========================================
async function procesarConIA(ownerId, mensajeCliente, datosExpediente) {
try {
const userQ = await pool.query(
"SELECT wa_settings, full_name, portal_settings FROM users WHERE id=$1",
[ownerId]
);
if (userQ.rowCount === 0) return null;
const userData = userQ.rows[0];
const settings = userData?.wa_settings || {};
const instruccionesExtra = settings.ai_custom_prompt || "";
const empresaNombre = userData?.full_name || "nuestra empresa";
if (!settings.wa_ai_enabled) return null;
const pSettings = userData?.portal_settings || {};
const horarios = {
m_start: pSettings.m_start || "09:00",
m_end: pSettings.m_end || "14:00",
a_start: pSettings.a_start || "16:00",
a_end: pSettings.a_end || "19:00"
};
const fechaHoyTexto = new Intl.DateTimeFormat("es-ES", {
timeZone: "Europe/Madrid",
weekday: "long",
year: "numeric",
month: "long",
day: "numeric"
}).format(new Date());
// =========================================================
// HELPERS INTERNOS (AUTOCONTENIDOS, PARA NO ROMPER TU SERVER)
// =========================================================
const parseTimeToMinutes = (value) => {
if (!value) return null;
const parts = String(value).trim().split(":");
if (parts.length < 2) return null;
const hh = parseInt(parts[0], 10);
const mm = parseInt(parts[1], 10);
if (Number.isNaN(hh) || Number.isNaN(mm)) return null;
return (hh * 60) + mm;
};
const minutesToHHMM = (mins) => {
const safe = Math.max(0, mins);
const hh = String(Math.floor(safe / 60)).padStart(2, "0");
const mm = String(safe % 60).padStart(2, "0");
return `${hh}:${mm}`;
};
const roundUpTo30 = (mins) => Math.ceil(mins / 30) * 30;
const roundDownTo30 = (mins) => Math.floor(mins / 30) * 30;
const mergeIntervals = (intervals) => {
if (!Array.isArray(intervals) || intervals.length === 0) return [];
const sorted = [...intervals].sort((a, b) => a[0] - b[0]);
const merged = [sorted[0]];
for (let i = 1; i < sorted.length; i++) {
const [curStart, curEnd] = sorted[i];
const last = merged[merged.length - 1];
if (curStart <= last[1]) {
last[1] = Math.max(last[1], curEnd);
} else {
merged.push([curStart, curEnd]);
}
}
return merged;
};
const getMadridNowInfo = () => {
const fmt = new Intl.DateTimeFormat("en-CA", {
timeZone: "Europe/Madrid",
year: "numeric",
month: "2-digit",
day: "2-digit",
hour: "2-digit",
minute: "2-digit",
hour12: false
});
const parts = fmt.formatToParts(new Date());
const map = {};
for (const p of parts) {
if (p.type !== "literal") map[p.type] = p.value;
}
return {
todayISO: `${map.year}-${map.month}-${map.day}`,
currentMinutes: (parseInt(map.hour, 10) * 60) + parseInt(map.minute, 10)
};
};
const addDaysISO = (isoDate, daysToAdd) => {
const [y, m, d] = isoDate.split("-").map(Number);
const dt = new Date(Date.UTC(y, m - 1, d));
dt.setUTCDate(dt.getUTCDate() + daysToAdd);
return dt.toISOString().slice(0, 10);
};
const isWeekendISO = (isoDate) => {
const [y, m, d] = isoDate.split("-").map(Number);
const dt = new Date(Date.UTC(y, m - 1, d));
const day = dt.getUTCDay();
return day === 0 || day === 6;
};
const formatDisplayDateES = (isoDate) => {
const [y, m, d] = isoDate.split("-").map(Number);
return new Date(y, m - 1, d, 12, 0, 0).toLocaleDateString("es-ES", {
weekday: "long",
day: "numeric",
month: "long"
});
};
const calcularHuecosDisponiblesExactos = async () => {
if (!datosExpediente.worker_id) return [];
const agendaQ = await pool.query(`
SELECT
COALESCE(
NULLIF(raw_data->>'scheduled_date', ''),
CASE
WHEN raw_data->>'appointment_status' = 'pending' THEN NULLIF(raw_data->>'requested_date', '')
ELSE NULL
END
) as effective_date,
COALESCE(
NULLIF(raw_data->>'scheduled_time', ''),
CASE
WHEN raw_data->>'appointment_status' = 'pending' THEN NULLIF(raw_data->>'requested_time', '')
ELSE NULL
END
) as effective_time,
COALESCE(NULLIF(raw_data->>'duration_minutes', ''), '60') as effective_duration
FROM scraped_services
WHERE owner_id = $1
AND assigned_to = $2
AND status != 'archived'
AND id != $3
AND (
(raw_data->>'scheduled_date' IS NOT NULL AND raw_data->>'scheduled_date' >= CURRENT_DATE::text)
OR
(raw_data->>'appointment_status' = 'pending' AND raw_data->>'requested_date' >= CURRENT_DATE::text)
)
ORDER BY effective_date ASC, effective_time ASC
`, [ownerId, datosExpediente.worker_id, datosExpediente.dbId]);
const ocupacionesPorDia = {};
for (const row of agendaQ.rows) {
const isoDate = row.effective_date;
const startMin = parseTimeToMinutes(row.effective_time);
const duration = Math.max(parseInt(row.effective_duration, 10) || 60, 1);
if (!isoDate || startMin === null) continue;
if (!ocupacionesPorDia[isoDate]) ocupacionesPorDia[isoDate] = [];
ocupacionesPorDia[isoDate].push([startMin, startMin + duration]);
}
const sesionesTrabajo = [
[parseTimeToMinutes(horarios.m_start), parseTimeToMinutes(horarios.m_end)],
[parseTimeToMinutes(horarios.a_start), parseTimeToMinutes(horarios.a_end)]
].filter(([ini, fin]) => ini !== null && fin !== null && fin > ini);
const { todayISO, currentMinutes } = getMadridNowInfo();
const diasDisponibles = [];
for (let offset = 0; offset < 21 && diasDisponibles.length < 10; offset++) {
const isoDate = addDaysISO(todayISO, offset);
if (isWeekendISO(isoDate)) continue;
const ocupadas = mergeIntervals(ocupacionesPorDia[isoDate] || []);
const ventanas = [];
for (const [sesionStartBase, sesionEndBase] of sesionesTrabajo) {
let sesionStart = sesionStartBase;
const sesionEnd = sesionEndBase;
if (isoDate === todayISO) {
sesionStart = Math.max(sesionStart, roundUpTo30(currentMinutes + 30));
}
if ((sesionEnd - sesionStart) < 60) continue;
let cursor = sesionStart;
for (const [occStart, occEnd] of ocupadas) {
if (occEnd <= sesionStart) continue;
if (occStart >= sesionEnd) break;
const gapStart = roundUpTo30(cursor);
const gapEnd = roundDownTo30(Math.min(occStart, sesionEnd));
if ((gapEnd - gapStart) >= 60) {
ventanas.push({
start: minutesToHHMM(gapStart),
end: minutesToHHMM(gapEnd),
startMin: gapStart,
endMin: gapEnd
});
}
cursor = Math.max(cursor, occEnd);
if (cursor >= sesionEnd) break;
}
const tailStart = roundUpTo30(cursor);
const tailEnd = roundDownTo30(sesionEnd);
if ((tailEnd - tailStart) >= 60) {
ventanas.push({
start: minutesToHHMM(tailStart),
end: minutesToHHMM(tailEnd),
startMin: tailStart,
endMin: tailEnd
});
}
}
if (ventanas.length > 0) {
diasDisponibles.push({
date: isoDate,
displayDate: formatDisplayDateES(isoDate),
windows: ventanas
});
}
}
return diasDisponibles;
};
// =========================================================
// HISTORIAL REAL DEL CHAT
// =========================================================
const historyQ = await pool.query(`
SELECT sender_role, message
FROM service_communications
WHERE scraped_id = $1
AND sender_role <> 'system'
ORDER BY created_at DESC
LIMIT 12
`, [datosExpediente.dbId]);
const historialRows = historyQ.rows.reverse();
const historialChat = historialRows.map(row => ({
role: row.sender_role === 'user' ? 'user' : 'assistant',
content: row.message
}));
const esPrimerMensaje = historialRows.length <= 1;
// =========================================================
// HUECOS EXACTOS CALCULADOS POR BACKEND
// =========================================================
let huecosExactos = [];
let agendaDisponibleTexto = "❌ Ahora mismo no hay huecos exactos calculados por el sistema.";
if (datosExpediente.worker_id) {
huecosExactos = await calcularHuecosDisponiblesExactos();
if (huecosExactos.length > 0) {
agendaDisponibleTexto = huecosExactos.map(dia => {
const tramos = dia.windows
.map(w => `entre las ${w.start} y las ${w.end} aprox`)
.join(" | ");
return `- ${dia.displayDate} (${dia.date}): ${tramos}`;
}).join("\n");
} else {
agendaDisponibleTexto = "❌ No hay huecos exactos libres en los próximos días laborables calculados por el sistema.";
}
}
const hayCitaPendiente =
datosExpediente.appointment_status === 'pending' &&
datosExpediente.cita_pendiente_fecha;
const tieneCitaConfirmada =
datosExpediente.cita &&
datosExpediente.cita !== 'Ninguna';
const esUrgencia = datosExpediente.is_urgent;
let tramoPendiente = datosExpediente.cita_pendiente_hora || "";
if (tramoPendiente && tramoPendiente.includes(":")) {
const [h, m] = tramoPendiente.split(':');
const hEnd = String((parseInt(h, 10) + 1) % 24).padStart(2, '0');
tramoPendiente = `entre las ${h}:${m} y las ${hEnd}:${m} aprox`;
}
let tramoConfirmado = datosExpediente.hora_cita || "";
if (tramoConfirmado && tramoConfirmado.includes(":")) {
const [h, m] = tramoConfirmado.split(':');
const hEnd = String((parseInt(h, 10) + 1) % 24).padStart(2, '0');
tramoConfirmado = `entre las ${h}:${m} y las ${hEnd}:${m} aprox`;
} else {
tramoConfirmado = 'una hora por confirmar';
}
let citaYaPaso = false;
if (tieneCitaConfirmada && datosExpediente.cita) {
const hoyTime = new Date().setHours(0, 0, 0, 0);
const [y, m, d] = datosExpediente.cita.split('-');
const citaTime = new Date(y, m - 1, d).setHours(0, 0, 0, 0);
if (citaTime < hoyTime) citaYaPaso = true;
}
const esEstadoFinal =
datosExpediente.estado &&
(
datosExpediente.estado.toLowerCase().includes('finalizado') ||
datosExpediente.estado.toLowerCase().includes('terminado') ||
datosExpediente.estado.toLowerCase().includes('anulado')
);
const noTieneTecnico = !datosExpediente.worker_id;
let directivaEstricta = "";
if (esEstadoFinal) {
directivaEstricta = `🛑 ESTADO ACTUAL: SERVICIO CERRADO. Informa al cliente que el servicio por su avería (${datosExpediente.averia}) está finalizado. NO AGENDES NADA.`;
} else if (noTieneTecnico) {
directivaEstricta = `🛑 ESTADO ACTUAL: SIN TÉCNICO ASIGNADO.
TU ÚNICO OBJETIVO: Informar al cliente que hemos recibido el aviso de su avería (${datosExpediente.averia}) y que estamos coordinando para asignarle un técnico en su zona.
⛔ PROHIBICIÓN ABSOLUTA: NO ofrezcas citas, NO des horas, NO agendes nada hasta que se le asigne un técnico.`;
} else if (citaYaPaso) {
directivaEstricta = `🛑 ESTADO ACTUAL: LA CITA YA PASÓ (${datosExpediente.cita}). Informa que estamos tramitando su avería (${datosExpediente.averia}). NO AGENDES NADA.`;
} else if (esUrgencia) {
directivaEstricta = `🛑 ESTADO ACTUAL: URGENCIA. Tranquiliza al cliente sobre su avería (${datosExpediente.averia}) y dile que el técnico está avisado. NO PROPONGAS HORAS.`;
} else if (hayCitaPendiente) {
directivaEstricta = `🛑 ESTADO ACTUAL: CITA PENDIENTE DE APROBACIÓN.
📅 Propuesta actual: El día ${datosExpediente.cita_pendiente_fecha} ${tramoPendiente}.
TU OBJETIVO: Informar que esperamos confirmación del técnico para reparar su avería (${datosExpediente.averia}).
⚠️ EXCEPCIÓN: Si el cliente pide CAMBIAR o CANCELAR, ofrécele un hueco nuevo usando SOLO los huecos exactos calculados por el sistema.`;
} else if (tieneCitaConfirmada) {
directivaEstricta = `🛑 ESTADO ACTUAL: CITA CONFIRMADA para el ${datosExpediente.cita} ${tramoConfirmado}. Recuerda la cita para su avería (${datosExpediente.averia}).
⚠️ EXCEPCIÓN: Si el cliente pide CAMBIARLA o CANCELARLA, ofrécele un hueco nuevo usando SOLO los huecos exactos calculados por el sistema.`;
} else {
directivaEstricta = `🟢 ESTADO ACTUAL: PENDIENTE DE AGENDAR CITA.
TU OBJETIVO: Acordar fecha y hora para reparar su avería (${datosExpediente.averia}) usando SOLO los huecos exactos calculados por el sistema.
⚠️ MUY IMPORTANTE: Cuando el cliente elija un hueco, NO le digas que la cita está confirmada. Dile que le pasas la nota al técnico para que él lo valide.`;
}
const promptSistema = `
Eres el coordinador humano de "${empresaNombre}". Hablas de tú, de forma natural, clara, cercana y profesional por WhatsApp.
--- 📋 CONTEXTO BÁSICO ---
- Hoy es: ${fechaHoyTexto}.
- Horario de la empresa: L-V de ${horarios.m_start} a ${horarios.m_end} y de ${horarios.a_start} a ${horarios.a_end}.
- ⛔ REGLA DE ORO DEL HORARIO: NUNCA propongas horas fuera de ese horario ni cruces el tramo de comer.
- Fines de semana solo si es una urgencia.
- Localidad del cliente actual: ${datosExpediente.poblacion || 'Localidad no especificada'}.
--- ✅ HUECOS EXACTOS CALCULADOS POR EL SISTEMA ---
${agendaDisponibleTexto}
--- 🚨 NORMA ABSOLUTA DE AGENDA ---
Los huecos anteriores son la ÚNICA verdad.
NO puedes inventar horas.
NO puedes deducir huecos por tu cuenta.
NO puedes reinterpretar horarios ocupados.
SOLO puedes ofrecer horas que estén dentro de esos huecos exactos calculados por el sistema.
Si el cliente pide una hora concreta y esa hora cae dentro de uno de esos huecos, puedes aceptarla tentativamente.
Si cae fuera, debes decir que no la ves libre y ofrecer una alternativa de la lista exacta.
--- 🎯 DIRECTIVA ESTRICTA PARA ESTE MENSAJE ---
${directivaEstricta}
--- ⚡ REGLA CRÍTICA DE AGENDA (COMANDO SECRETO) ---
Si (y solo si) el cliente ACEPTA claramente un hueco concreto, DEBES añadir AL FINAL ABSOLUTO de tu respuesta este texto literal:
[PROPUESTA:YYYY-MM-DD HH:mm]
Ejemplo:
"Perfecto, le paso la nota al técnico para que te confirme el miércoles entre las 12:00 y las 13:00 aprox. ¡Te decimos algo pronto! [PROPUESTA:2026-03-25 12:00]"
⛔ PROHIBICIONES:
- NUNCA digas "te agendo".
- NUNCA digas "cita confirmada".
- NUNCA menciones las palabras "código" o "etiqueta".
- NUNCA ofrezcas huecos distintos a los calculados.
- Si el cliente pregunta por "la semana que viene", responde usando solo los huecos de esa semana.
--- ⚙️ REGLAS DE COMUNICACIÓN ---
1. MÁXIMO 2 FRASES.
2. Mensajes cortos, claros y directos.
3. NO TE PRESENTES si ya habéis hablado antes.
4. NO uses la muletilla "¿En qué más te puedo ayudar?".
5. Si el cliente pide disponibilidad general, resume las opciones reales sin inventar nada.
${esPrimerMensaje ? `6. Primer mensaje: preséntate y menciona el aviso (#${datosExpediente.ref}).` : ''}
${instruccionesExtra ? `7. Instrucción extra de la empresa: ${instruccionesExtra}` : ''}
`;
const completion = await openai.chat.completions.create({
model: OPENAI_MODEL || "gpt-4o-mini",
messages: [
{ role: "system", content: promptSistema },
...historialChat
],
temperature: 0.1
});
return completion.choices?.[0]?.message?.content || null;
} catch (e) {
console.error("❌ Error OpenAI:", e.message);
return null;
}
}
// ==========================================
// 📱 OTP PARA PORTAL DEL CLIENTE (ACCESO WEB)
// ==========================================
app.post("/public/auth/request-otp", async (req, res) => {
try {
const { phone, owner_id } = req.body;
if (!phone || !owner_id) return res.status(400).json({ ok: false, error: "Faltan datos" });
const p = normalizePhone(phone);
// Generamos un código aleatorio de 4 dígitos
const code = String(Math.floor(1000 + Math.random() * 9000));
const codeHash = await bcrypt.hash(code, 10);
// Lo guardamos en la tabla de login_codes (ponemos user_id NULL porque es un cliente, no un operario)
await pool.query(`
INSERT INTO login_codes (user_id, phone, code_hash, purpose, expires_at)
VALUES (NULL, $1, $2, 'client_portal', NOW() + INTERVAL '15 minutes')
`, [p, codeHash]);
const msg = `👋 *Asistencia Técnica*\n\nTu código seguro de acceso al portal es: *${code}*\n\nCaduca en 15 minutos.`;
// Enviamos el WhatsApp usando la instancia de la empresa correspondiente
sendWhatsAppAuto(p, msg, `cliente_${owner_id}`, false).catch(console.error);
res.json({ ok: true });
} catch (e) {
console.error("Error solicitando OTP cliente:", e);
res.status(500).json({ ok: false });
}
});
app.post("/public/auth/verify-otp", async (req, res) => {
try {
const { phone, code, owner_id } = req.body;
const p = normalizePhone(phone);
// 1. Buscamos si el código existe, es de cliente, y no está caducado
const q = await pool.query(`
SELECT id, code_hash FROM login_codes
WHERE phone = $1 AND purpose = 'client_portal' AND consumed_at IS NULL AND expires_at > NOW()
ORDER BY created_at DESC LIMIT 1
`, [p]);
if (q.rowCount === 0) return res.status(400).json({ ok: false, error: "Código inválido o caducado" });
// 2. Comprobamos que el código de 4 dígitos coincide
const valid = await bcrypt.compare(String(code), q.rows[0].code_hash);
if (!valid) return res.status(400).json({ ok: false, error: "Código incorrecto" });
// 3. Quemamos el código para que no se pueda usar 2 veces
await pool.query("UPDATE login_codes SET consumed_at = NOW() WHERE id = $1", [q.rows[0].id]);
// 4. Magia: Buscamos si este cliente ya nos ha llamado antes para pre-rellenar su formulario
const cleanPhoneForSearch = String(phone).replace(/\D/g, "");
const clientQ = await pool.query(
"SELECT full_name, addresses FROM clients WHERE phone LIKE $1 AND owner_id = $2 LIMIT 1",
[`%${cleanPhoneForSearch}%`, owner_id]
);
let clientData = null;
let exists = false;
if (clientQ.rowCount > 0) {
clientData = clientQ.rows[0];
exists = true;
}
res.json({ ok: true, exists, client: clientData });
} catch (e) {
console.error("Error verificando OTP cliente:", e);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 🌐 EMBUDO PÚBLICO DE ENTRADA DE CLIENTES
// ==========================================
app.post("/public/new-request", async (req, res) => {
const clientDb = await pool.connect();
try {
const { phone, name, address, guild_id, description, is_urgent, owner_id } = req.body;
// 🚨 CAMBIO SAAS: Obligamos a que venga el owner_id sí o sí
if (!phone || !guild_id || !owner_id) return res.status(400).json({ ok: false, error: "Faltan datos clave de la empresa" });
// 🛡️ CONVERSIÓN ESTRICTA DE DATOS (Para que PostgreSQL no lance Error 500)
const targetOwnerId = parseInt(owner_id); // Quitamos el "|| 1" para que no asigne a la Empresa 1 por error
const safeGuildId = parseInt(guild_id) || 0;
const isUrgentBool = is_urgent === true || is_urgent === 'true';
const cleanPhone = String(phone).replace(/\D/g, "");
if (cleanPhone.length < 9) return res.status(400).json({ ok: false, error: "Teléfono inválido" });
await clientDb.query('BEGIN');
// 1. BUSCAR O CREAR CLIENTE
let clientId, clientToken;
const qClient = await clientDb.query("SELECT id, portal_token FROM clients WHERE phone LIKE $1 AND owner_id = $2 LIMIT 1", [`%${cleanPhone}%`, targetOwnerId]);
if (qClient.rowCount > 0) {
clientId = qClient.rows[0].id;
clientToken = qClient.rows[0].portal_token;
} else {
clientToken = crypto.randomBytes(6).toString('hex');
const newClient = await clientDb.query(
"INSERT INTO clients (owner_id, full_name, phone, addresses, portal_token) VALUES ($1, $2, $3, $4, $5) RETURNING id",
[targetOwnerId, name || "Cliente Web", phone, JSON.stringify([address || ""]), clientToken]
);
clientId = newClient.rows[0].id;
}
// 2. BUSCAR OPERARIO AL AZAR (Si es NORMAL)
let assignedWorkerId = null;
if (!isUrgentBool) {
let qWorker = await clientDb.query(`
SELECT u.id FROM users u
JOIN user_guilds ug ON u.id = ug.user_id
WHERE u.owner_id = $1 AND u.status = 'active' AND ug.guild_id = $2
ORDER BY RANDOM() LIMIT 1
`, [targetOwnerId, safeGuildId]);
// 🛡️ FALLBACK: Si nadie tiene ese gremio, coge a CUALQUIERA activo
if (qWorker.rowCount === 0) {
qWorker = await clientDb.query("SELECT id FROM users WHERE owner_id = $1 AND status = 'active' ORDER BY RANDOM() LIMIT 1", [targetOwnerId]);
}
if (qWorker.rowCount > 0) {
assignedWorkerId = qWorker.rows[0].id;
} else {
await clientDb.query('ROLLBACK'); // ⚠️ VITAL para no bloquear la BD
return res.status(400).json({ ok: false, error: "No hay técnicos disponibles. Llama a la oficina." });
}
}
// 3. CREAR EL SERVICIO
const rawData = {
"Nombre Cliente": name || "Cliente Web",
"Teléfono": phone,
"Dirección": address || "",
"Descripción": description || "Aviso desde Web",
"guild_id": safeGuildId,
"Compañía": "Particular",
"Origen": "Web Público"
};
const ref = `WEB-${Date.now().toString().slice(-6)}`;
const qSvc = await clientDb.query(`
INSERT INTO scraped_services (owner_id, provider, service_ref, status, automation_status, assigned_to, is_urgent, raw_data)
VALUES ($1, 'particular', $2, 'pending', $3, $4, $5, $6) RETURNING id
`, [
targetOwnerId,
ref,
isUrgentBool ? 'in_progress' : 'manual',
assignedWorkerId,
isUrgentBool,
JSON.stringify(rawData)
]);
const newServiceId = qSvc.rows[0].id;
// 4. SI ES URGENTE, DISPARAMOS EL WHATSAPP DIRECTAMENTE
if (isUrgentBool) {
// Buscamos un operario activo de ese gremio
let workersQ = await clientDb.query(`
SELECT u.id, u.full_name, u.phone FROM users u
JOIN user_guilds ug ON u.id = ug.user_id
WHERE u.owner_id = $1 AND u.status = 'active' AND ug.guild_id = $2
ORDER BY RANDOM() LIMIT 1
`, [targetOwnerId, safeGuildId]);
// 🛡️ FALLBACK URGENCIA: Si nadie tiene ese gremio, coge a CUALQUIERA activo
if (workersQ.rowCount === 0) {
workersQ = await clientDb.query(`
SELECT id, full_name, phone FROM users
WHERE owner_id = $1 AND status = 'active'
ORDER BY RANDOM() LIMIT 1
`, [targetOwnerId]);
}
if (workersQ.rowCount > 0) {
const worker = workersQ.rows[0];
const token = crypto.randomBytes(16).toString('hex');
// Creamos la alerta
await clientDb.query(`
INSERT INTO assignment_pings (scraped_id, user_id, token, expires_at)
VALUES ($1, $2, $3, CURRENT_TIMESTAMP + INTERVAL '5 minutes')
`, [newServiceId, worker.id, token]);
// 🛑 EXTRAEMOS LA CALLE OCULTA (Sin números)
let calleOculta = "Zona asignada";
if (address) {
calleOculta = address.replace(/\s*,?\s*(\d+|s\/n).*$/i, '').trim();
}
// Enviamos el WA al operario
const msg = `🚨 *URGENCIA PARTICULAR (WEB)*\n📍 Calle: ${calleOculta}\n🔗 https://web.integrarepara.es/aceptar.html?t=${token}`;
sendWhatsAppAuto(worker.phone, msg, `cliente_${targetOwnerId}`, false).catch(console.error);
await clientDb.query(
"INSERT INTO scraped_service_logs (scraped_id, user_name, action, details) VALUES ($1, 'Sistema Automático', 'Bolsa de Urgencias', $2)",
[newServiceId, `Notificación enviada a: ${worker.full_name}`]
);
} else {
await clientDb.query("UPDATE scraped_services SET automation_status = 'failed' WHERE id = $1", [newServiceId]);
}
await clientDb.query('COMMIT');
return res.json({ ok: true, action: 'queued', message: "Aviso urgente en cola." });
} else {
await clientDb.query('COMMIT');
// Devolvemos el token del cliente para llevarlo a pedir Cita Normal
return res.json({
ok: true,
action: 'calendar',
redirectUrl: `https://portal.integrarepara.es/?token=${clientToken}&service=${newServiceId}`
});
}
} catch (e) {
await clientDb.query('ROLLBACK');
console.error("🚨 CRASH EN WEB PÚBLICA (NUEVO AVISO):", e.message);
res.status(500).json({ ok: false, error: "Error procesando solicitud" });
} finally {
clientDb.release();
}
});
// ==========================================
// 🔗 PORTAL PÚBLICO DEL CLIENTE
// ==========================================
app.get("/public/portal/:token", async (req, res) => {
try {
const { token } = req.params;
const serviceId = req.query.service;
// 1. Buscamos al cliente por su token
const qClient = await pool.query("SELECT * FROM clients WHERE portal_token = $1 LIMIT 1", [token]);
if (qClient.rowCount === 0) return res.status(404).json({ ok: false, error: "Enlace no válido" });
const client = qClient.rows[0];
const ownerId = client.owner_id;
// 2. 🛑 FIX CRÍTICO: PEDIMOS BILLING_SETTINGS A LA BBDD
const qConfig = await pool.query("SELECT full_name, company_logo, billing_settings FROM users WHERE id = $1", [ownerId]);
const company = {
name: qConfig.rows[0]?.full_name || "IntegraRepara",
logo: qConfig.rows[0]?.company_logo || null,
billing_settings: qConfig.rows[0]?.billing_settings || null // AQUÍ VIAJA EL IBAN Y LAS OBSERVACIONES
};
// 3. CONSULTA SEGURA (Con o sin ID)
let qServices;
if (serviceId && !isNaN(parseInt(serviceId))) {
qServices = await pool.query(`
SELECT s.id, s.service_ref, s.is_urgent, s.raw_data, s.created_at,
st.name as real_status_name, st.is_final as is_status_final,
u.full_name as worker_name, u.phone as worker_phone
FROM scraped_services s
LEFT JOIN users u ON u.id = s.assigned_to
LEFT JOIN service_statuses st ON st.id::text = (s.raw_data->>'status_operativo')::text
WHERE s.id = $1 AND s.owner_id = $2 AND s.provider != 'SYSTEM_BLOCK'
`, [parseInt(serviceId), ownerId]);
} else {
let phoneMatch = String(client.phone || "").replace(/[^0-9]/g, "");
if (phoneMatch.length > 9) phoneMatch = phoneMatch.slice(-9);
if (phoneMatch.length < 6) phoneMatch = "TELEFONO_FALSO_123";
qServices = await pool.query(`
SELECT s.id, s.service_ref, s.is_urgent, s.raw_data, s.created_at,
st.name as real_status_name, st.is_final as is_status_final,
u.full_name as worker_name, u.phone as worker_phone
FROM scraped_services s
LEFT JOIN users u ON u.id = s.assigned_to
LEFT JOIN service_statuses st ON st.id::text = (s.raw_data->>'status_operativo')::text
WHERE s.owner_id = $1 AND s.provider != 'SYSTEM_BLOCK'
AND s.raw_data::text ILIKE $2
ORDER BY s.created_at DESC
`, [ownerId, `%${phoneMatch}%`]);
}
const formattedServices = qServices.rows.map(s => {
return {
id: s.id,
title: s.is_urgent ? `🚨 URGENTE: #${s.service_ref}` : `Expediente #${s.service_ref}`,
description: s.raw_data?.["Descripción"] || s.raw_data?.["DESCRIPCION"] || "Aviso de reparación",
status_name: s.real_status_name || "En gestión",
is_final: s.is_status_final || false,
scheduled_date: s.raw_data?.scheduled_date || "",
scheduled_time: s.raw_data?.scheduled_time || "",
assigned_worker: s.worker_name || null,
worker_phone: s.worker_phone || null,
raw_data: s.raw_data
};
});
// 4. BUSCAR PRESUPUESTOS CON SUS ARTÍCULOS
let cleanPhoneSearch = String(client.phone || "").replace(/[^0-9]/g, "");
if (cleanPhoneSearch.length > 9) cleanPhoneSearch = cleanPhoneSearch.slice(-9);
let formattedQuotes = [];
if (cleanPhoneSearch.length >= 9) {
const qBudgets = await pool.query(`
SELECT id, client_name, client_phone, client_address, items, subtotal, tax, total, status, created_at
FROM budgets
WHERE owner_id = $1
AND client_phone LIKE $2
ORDER BY created_at DESC
`, [ownerId, `%${cleanPhoneSearch}%`]);
formattedQuotes = qBudgets.rows.map(b => ({
id: b.id,
quote_ref: `PRE-${b.id}`,
title: "Presupuesto de Reparación",
amount: parseFloat(b.total).toFixed(2),
created_at: b.created_at,
items: b.items,
subtotal: b.subtotal,
tax: b.tax,
client_name: b.client_name,
client_phone: b.client_phone,
client_address: b.client_address,
status: b.status
}));
}
// 🚀 5. NUEVO: BUSCAR SI TIENE PLAN DE PROTECCIÓN ACTIVO
let activeSubscription = null;
if (cleanPhoneSearch.length >= 9) {
const subQ = await pool.query(`
SELECT s.status, s.payment_status, s.bricos_used, s.urgencies_used, s.renewal_date,
p.name as plan_name, p.bricos_limit, p.urgencies_limit
FROM protection_subscriptions s
JOIN protection_plans p ON s.plan_id = p.id
WHERE s.company_id = $1 AND s.client_phone LIKE $2 AND s.status = 'activo' AND s.payment_status = 'pagado'
ORDER BY s.created_at DESC LIMIT 1
`, [ownerId, `%${cleanPhoneSearch}%`]);
if (subQ.rowCount > 0) {
activeSubscription = subQ.rows[0];
}
}
res.json({
ok: true,
client: { name: client.full_name },
company,
services: formattedServices,
quotes: formattedQuotes,
subscription: activeSubscription // <-- 🟢 Lo pasamos al frontend del cliente
});
} catch (e) {
console.error("🔥 ERROR EN PORTAL:", e.message);
res.status(500).json({ ok: false, error: e.message });
}
});
// 2. Obtener huecos disponibles inteligentes (CON ENRUTAMIENTO POR TURNOS: Mañana y Tarde)
app.get("/public/portal/:token/slots", async (req, res) => {
try {
const { token } = req.params;
const { serviceId } = req.query;
const clientQ = await pool.query("SELECT id, owner_id FROM clients WHERE portal_token = $1", [token]);
if (clientQ.rowCount === 0) return res.status(404).json({ ok: false, error: "Token inválido" });
const ownerId = clientQ.rows[0].owner_id;
// EXTRAEMOS LA CONFIGURACIÓN DE HORARIOS DEL PORTAL
const userQ = await pool.query("SELECT portal_settings FROM users WHERE id = $1", [ownerId]);
const pSet = userQ.rows[0]?.portal_settings || { m_start:"09:00", m_end:"14:00", a_start:"16:00", a_end:"19:00" };
function genSlots(start, end) {
if(!start || !end) return [];
let s = [];
let [sh, sm] = start.split(':').map(Number);
let [eh, em] = end.split(':').map(Number);
let cur = sh * 60 + sm;
let limit = eh * 60 + em;
while(cur + 60 <= limit) {
s.push(`${String(Math.floor(cur/60)).padStart(2,'0')}:${String(cur%60).padStart(2,'0')}`);
cur += 60;
}
return s;
}
const morningBase = genSlots(pSet.m_start, pSet.m_end);
const afternoonBase = genSlots(pSet.a_start, pSet.a_end);
const serviceQ = await pool.query("SELECT * FROM scraped_services WHERE id=$1", [serviceId]);
if (serviceQ.rowCount === 0) return res.status(404).json({ ok: false, error: "Servicio no encontrado" });
const service = serviceQ.rows[0];
const assignedTo = service.assigned_to;
if (!assignedTo) return res.status(400).json({ ok: false, error: "No hay operario asignado" });
const raw = service.raw_data || {};
const targetGuildId = raw["guild_id"];
// 🧠 1. EXTRAER LAS ZONAS DEL OPERARIO
const workerQ = await pool.query("SELECT zones FROM users WHERE id = $1", [assignedTo]);
const workerZones = workerQ.rows[0]?.zones || [];
function getCityForCP(cp, fallbackPop) {
let cleanCP = String(cp || "").trim();
const zone = workerZones.find(z => z.cps === cleanCP);
if (zone && zone.city) return zone.city.toUpperCase().trim();
return String(fallbackPop || "").toUpperCase().trim();
}
// Bautizamos al cliente actual
const targetCity = getCityForCP(raw["Código Postal"] || raw["C.P."], raw["Población"] || raw["POBLACION-PROVINCIA"]);
// 🧠 2. EXTRAER LA AGENDA TOTAL
const agendaQ = await pool.query(`
SELECT raw_data->>'scheduled_date' as date,
raw_data->>'scheduled_time' as time,
raw_data->>'requested_date' as req_date,
raw_data->>'requested_time' as req_time,
raw_data->>'appointment_status' as appt_status,
raw_data->>'duration_minutes' as duration,
raw_data->>'Población' as poblacion,
raw_data->>'Código Postal' as cp,
provider,
raw_data->>'blocked_guild_id' as blocked_guild_id
FROM scraped_services
WHERE assigned_to = $1
AND status != 'archived'
AND id != $2
`, [assignedTo, serviceId]);
const agendaMap = {};
agendaQ.rows.forEach(row => {
let effectiveDate = row.date;
let effectiveTime = row.time;
if (row.appt_status === 'pending' && row.req_date && row.req_time) {
effectiveDate = row.req_date;
effectiveTime = row.req_time;
}
if (!effectiveDate || new Date(effectiveDate) < new Date(new Date().toISOString().split('T')[0])) return;
if (row.provider === 'SYSTEM_BLOCK' && row.blocked_guild_id && String(row.blocked_guild_id) !== String(targetGuildId)) {
return;
}
// 🛑 NUEVO: Ahora guardamos la ciudad de la MAÑANA y de la TARDE por separado
if (!agendaMap[effectiveDate]) agendaMap[effectiveDate] = { times: [], morningCity: null, afternoonCity: null };
if (row.provider !== 'SYSTEM_BLOCK' && effectiveTime) {
const city = getCityForCP(row.cp, row.poblacion);
let [th, tm] = effectiveTime.split(':').map(Number);
let [afternoonStartH, afternoonStartM] = pSet.a_start.split(':').map(Number); // Ej: 16:00
// Si la cita empieza antes del turno de tarde, bautiza la mañana. Si no, la tarde.
if (th < afternoonStartH) {
if (!agendaMap[effectiveDate].morningCity) agendaMap[effectiveDate].morningCity = city;
} else {
if (!agendaMap[effectiveDate].afternoonCity) agendaMap[effectiveDate].afternoonCity = city;
}
}
const dur = parseInt(row.duration || 60);
if (effectiveTime) {
let [th, tm] = effectiveTime.split(':').map(Number);
let startMin = th * 60 + tm;
let endMin = startMin + dur;
const blockSlots = (base) => {
base.forEach(slot => {
let [sh, sm] = slot.split(':').map(Number);
let slotStart = sh * 60 + sm;
let slotEnd = slotStart + 60;
if(slotStart < endMin && slotEnd > startMin) {
agendaMap[effectiveDate].times.push(slot);
}
});
};
blockSlots(morningBase);
blockSlots(afternoonBase);
}
});
// 🧠 3. GENERAR LOS DÍAS DISPONIBLES (CON CORTAFUEGOS POR TURNOS)
const availableDays = [];
let d = new Date();
d.setDate(d.getDate() + 1);
let daysAdded = 0;
while(daysAdded < 5) {
if (d.getDay() !== 0 && d.getDay() !== 6) {
const dateStr = d.toISOString().split('T')[0];
const dayData = agendaMap[dateStr];
const takenTimes = dayData ? dayData.times : [];
let availMorning = morningBase.filter(t => !takenTimes.includes(t));
let availAfternoon = afternoonBase.filter(t => !takenTimes.includes(t));
// 📍 REGLA CRÍTICA DE TURNOS: Bloqueamos de forma independiente
if (dayData) {
if (dayData.morningCity && dayData.morningCity !== targetCity) {
availMorning = []; // Mañana ocupada en otra ciudad
}
if (dayData.afternoonCity && dayData.afternoonCity !== targetCity) {
availAfternoon = []; // Tarde ocupada en otra ciudad
}
}
if (availMorning.length > 0 || availAfternoon.length > 0) {
availableDays.push({
date: dateStr,
displayDate: d.toLocaleDateString('es-ES', { weekday: 'long', day: 'numeric', month: 'long' }),
morning: availMorning,
afternoon: availAfternoon
});
daysAdded++;
}
}
d.setDate(d.getDate() + 1);
}
res.json({ ok: true, days: availableDays });
} catch (e) {
console.error("Error Slots:", e);
res.status(500).json({ ok: false });
}
});
// 3. Obtener los Gremios Visibles, Logo y Nombre para el Portal Público
app.get("/public/company/:id/guilds", async (req, res) => {
try {
const ownerId = parseInt(req.params.id) || 1;
// 1. Leemos la configuración de esta empresa (INCLUYENDO LOGO Y NOMBRE)
const userQ = await pool.query("SELECT full_name, company_logo, portal_settings FROM users WHERE id = $1", [ownerId]);
if(userQ.rowCount === 0) return res.status(404).json({ok: false});
const company = userQ.rows[0];
const settings = company.portal_settings || {};
const visibleIds = settings.visible_guilds || [];
let guilds = [];
// 2. Buscamos los gremios seleccionados
if (visibleIds.length > 0) {
const guildsQ = await pool.query("SELECT id, name FROM guilds WHERE id = ANY($1::int[]) AND owner_id = $2 ORDER BY name ASC", [visibleIds, ownerId]);
guilds = guildsQ.rows;
}
// Devolvemos TODO empaquetado al frontend
res.json({
ok: true,
name: company.full_name,
logo: company.company_logo,
guilds: guilds
});
} catch(e) {
res.status(500).json({ok: false});
}
});
// --- RUTA PARA GUARDAR LA CITA SOLICITADA POR EL CLIENTE ---
app.post("/public/portal/:token/book", async (req, res) => {
try {
const { token } = req.params;
const { serviceId, date, time } = req.body;
if (!serviceId || !date || !time) return res.status(400).json({ ok: false, error: "Faltan datos" });
// Verificamos quién es el dueño del portal usando el token
const clientQ = await pool.query("SELECT id, owner_id FROM clients WHERE portal_token = $1", [token]);
if (clientQ.rowCount === 0) return res.status(404).json({ ok: false, error: "Token inválido" });
const ownerId = clientQ.rows[0].owner_id;
// 🚨 CAMBIO AQUÍ: Recuperamos los datos crudos Y TAMBIÉN a quién está asignado
const serviceQ = await pool.query("SELECT raw_data, assigned_to, service_ref FROM scraped_services WHERE id=$1 AND owner_id=$2", [serviceId, ownerId]);
if (serviceQ.rowCount === 0) return res.status(404).json({ ok: false, error: "Servicio no encontrado" });
const srv = serviceQ.rows[0];
const raw = srv.raw_data || {};
// 🛡️ ESCUDO: Verificamos que el hueco siga libre (Por si 2 clientes hacen clic en el mismo milisegundo)
if (srv.assigned_to) {
const solapamiento = await comprobarDisponibilidad(ownerId, srv.assigned_to, date, time, 60, serviceId);
if (solapamiento.choca) {
return res.status(400).json({ ok: false, error: "Lo sentimos, alguien acaba de reservar ese mismo hueco hace unos instantes. Por favor, elige otro horario." });
}
}
// Grabamos la solicitud en el jsonb para que el admin la vea en agenda.html
raw.requested_date = date;
raw.requested_time = time;
raw.appointment_status = 'pending';
await pool.query("UPDATE scraped_services SET raw_data = $1 WHERE id = $2", [JSON.stringify(raw), serviceId]);
// =========================================================
// 🚨 MAGIA: AVISAR AL OPERARIO POR WHATSAPP AL INSTANTE
// =========================================================
if (srv.assigned_to) {
const workerQ = await pool.query("SELECT full_name, phone FROM users WHERE id=$1", [srv.assigned_to]);
if (workerQ.rowCount > 0) {
const w = workerQ.rows[0];
const ref = srv.service_ref || raw["Referencia"] || serviceId;
const clientName = raw["Nombre Cliente"] || raw["CLIENTE"] || "El cliente";
// Formateamos la fecha para que se lea bonita en WhatsApp
const [y, m, d] = date.split('-');
const dateFormatted = `${d}/${m}/${y}`;
const msg = `🔔 *¡NUEVA CITA SOLICITADA!*\n\nHola ${w.full_name}, ${clientName} acaba de elegir un hueco para el expediente *#${ref}*.\n\n📅 *Fecha:* ${dateFormatted}\n⏰ *Hora aprox:* ${time}\n\nEntra en tu App (Agenda) para confirmarla o rechazarla.`;
// Enviamos el WA de aviso al trabajador
sendWhatsAppAuto(w.phone, msg, `cliente_${ownerId}`, false).catch(console.error);
}
}
res.json({ ok: true });
} catch (e) {
console.error("Error agendando cita (book):", e);
res.status(500).json({ ok: false, error: "Error interno" });
}
});
// 3. OBTENER SOLICITUDES PARA EL PANEL DEL ADMIN Y APP OPERARIO
app.get("/agenda/requests", authMiddleware, async (req, res) => {
try {
let query = `
SELECT s.id, s.service_ref, s.raw_data, u.full_name as assigned_name
FROM scraped_services s
LEFT JOIN users u ON s.assigned_to = u.id
WHERE s.owner_id = $1
AND s.raw_data->>'appointment_status' = 'pending'
`;
const params = [req.user.accountId];
// Si es operario, solo ve sus propias solicitudes
if (req.user.role === 'operario' || req.user.role === 'operario_cerrado') {
query += ` AND s.assigned_to = $2`;
params.push(req.user.sub);
}
query += ` ORDER BY s.created_at ASC`;
const q = await pool.query(query, params);
res.json({ ok: true, requests: q.rows });
} catch (e) { res.status(500).json({ ok: false }); }
});
app.post("/agenda/requests/:id/approve", authMiddleware, async (req, res) => {
try {
const { id } = req.params;
// 🛑 NUEVO: Recibimos date y time desde la App
const { duration, date, time } = req.body;
const current = await pool.query('SELECT raw_data FROM scraped_services WHERE id=$1 AND owner_id=$2', [id, req.user.accountId]);
if (current.rowCount === 0) return res.status(404).json({ok: false});
const raw = current.rows[0].raw_data;
// 🛑 NUEVO: Si el técnico ha cambiado la fecha/hora en la App, usamos esa. Si no, la que pidió el cliente.
const reqDate = date || raw.requested_date;
const reqTime = time || raw.requested_time;
const statusQ = await pool.query("SELECT id FROM service_statuses WHERE owner_id=$1 AND name ILIKE '%citado%' LIMIT 1", [req.user.accountId]);
const idCitado = statusQ.rows[0]?.id || raw.status_operativo;
const updatedRaw = {
...raw,
scheduled_date: reqDate,
scheduled_time: reqTime,
duration_minutes: duration,
appointment_status: 'approved',
status_operativo: idCitado
};
delete updatedRaw.requested_date;
delete updatedRaw.requested_time;
// 1. Guardamos en Base de Datos
await pool.query("UPDATE scraped_services SET raw_data=$1 WHERE id=$2", [JSON.stringify(updatedRaw), id]);
console.log(`🤖 [PORTAL-CITA] Cita aprobada para ${reqDate}. Disparando eventos...`);
// 🚀 2. RESPONDEMOS AL NAVEGADOR INMEDIATAMENTE (La ventana se cierra al instante)
res.json({ok: true});
// 👻 3. TAREAS EN SEGUNDO PLANO (El WA tarda 10 segs, pero el usuario ya no espera)
(async () => {
try {
// Disparamos WhatsApp oficial de cita confirmada
await triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_date');
// Disparamos ROBOTS SEGÚN PROVEEDOR
const checkProv = await pool.query("SELECT provider FROM scraped_services WHERE id=$1", [id]);
const providerName = checkProv.rows[0]?.provider;
if (providerName === 'homeserve') {
console.log(`✅ [PORTAL-CITA] Disparando robot HS: CITA (${reqDate})`);
triggerHomeServeRobot(req.user.accountId, id, 'date').catch(console.error);
} else if (providerName === 'multiasistencia') {
console.log(`✅ [PORTAL-CITA] Disparando robot MULTI: CITA (${reqDate})`);
triggerMultiRobot(req.user.accountId, id, 'date').catch(console.error);
}
} catch (errBckg) {
console.error("Error en tareas de fondo de aprobar cita:", errBckg);
}
})();
} catch (e) {
console.error("Error al aprobar cita:", e);
if (!res.headersSent) res.status(500).json({ok: false});
}
});
// 5. RECHAZAR CITA
app.post("/agenda/requests/:id/reject", authMiddleware, async (req, res) => {
try {
const { id } = req.params;
const current = await pool.query('SELECT raw_data FROM scraped_services WHERE id=$1 AND owner_id=$2', [id, req.user.accountId]);
if (current.rowCount === 0) return res.status(404).json({ok: false});
const raw = current.rows[0].raw_data;
const updatedRaw = { ...raw, appointment_status: 'rejected' };
delete updatedRaw.requested_date;
delete updatedRaw.requested_time;
await pool.query("UPDATE scraped_services SET raw_data=$1 WHERE id=$2", [JSON.stringify(updatedRaw), id]);
// Enviar WA de rechazo con el enlace para que elija otra
const phone = raw["Teléfono"] || raw["TELEFONO"] || "";
if (phone) {
const clientQ = await pool.query("SELECT portal_token FROM clients WHERE phone LIKE $1 AND owner_id=$2 LIMIT 1", [`%${phone.replace('+34', '').trim()}%`, req.user.accountId]);
const token = clientQ.rows[0]?.portal_token;
const link = `https://portal.integrarepara.es/?token=${token}&service=${id}`;
const finalMsg = `⚠️ *CITA NO CONFIRMADA*\n\nHola ${raw["Nombre Cliente"] || "Cliente"}. Lamentamos informarte que el técnico no podrá acudir en el horario que solicitaste por un problema de ruta.\n\nPor favor, entra de nuevo en tu portal y elige otro hueco disponible:\n🔗 ${link}`;
await sendWhatsAppAuto(phone, finalMsg, `cliente_${req.user.accountId}`, false);
}
res.json({ok: true});
} catch (e) { res.status(500).json({ok: false}); }
});
// 6. RUTAS DE BLOQUEOS (AGENDA) CON SOPORTE PARA GREMIOS
app.post("/agenda/blocks", authMiddleware, async (req, res) => {
try {
const { worker_id, date, time, duration, reason, guild_id, guild_name } = req.body;
const raw = {
"Nombre Cliente": "BLOQUEO DE AGENDA",
"Descripción": reason,
scheduled_date: date,
scheduled_time: time,
duration_minutes: duration,
blocked_guild_id: guild_id || null, // Guardamos el gremio
blocked_guild_name: guild_name || null
};
await pool.query(`
INSERT INTO scraped_services (owner_id, provider, service_ref, status, assigned_to, raw_data)
VALUES ($1, 'SYSTEM_BLOCK', 'BLOCK-' || extract(epoch from now())::int, 'pending', $2, $3)
`, [req.user.accountId, worker_id, JSON.stringify(raw)]);
res.json({ ok: true });
} catch(e) { res.status(500).json({ ok: false }); }
});
app.get("/agenda/blocks", authMiddleware, async (req, res) => {
try {
const q = await pool.query(`
SELECT s.id, u.full_name as worker_name,
s.raw_data->>'scheduled_date' as date,
s.raw_data->>'scheduled_time' as time,
s.raw_data->>'duration_minutes' as duration,
s.raw_data->>'Descripción' as reason,
s.raw_data->>'blocked_guild_name' as guild_name
FROM scraped_services s
JOIN users u ON s.assigned_to = u.id
WHERE s.owner_id = $1 AND s.provider = 'SYSTEM_BLOCK'
ORDER BY s.raw_data->>'scheduled_date' DESC
`, [req.user.accountId]);
res.json({ ok: true, blocks: q.rows });
} catch(e) { res.status(500).json({ ok: false }); }
});
app.delete("/agenda/blocks/:id", authMiddleware, async (req, res) => {
try {
await pool.query("DELETE FROM scraped_services WHERE id=$1 AND owner_id=$2 AND provider='SYSTEM_BLOCK'", [req.params.id, req.user.accountId]);
res.json({ ok: true });
} catch(e) { res.status(500).json({ ok: false }); }
});
// ==========================================
// 📥 TRASPASO MANUAL (BUZÓN -> PANEL)
// ==========================================
app.post('/providers/import/:id', authMiddleware, async (req, res) => {
try {
const { id } = req.params;
// 1. Cambiamos el estado principal a 'imported' para que el buzón lo marque en azul
// y detenemos cualquier automatismo pendiente ('completed')
await pool.query(
"UPDATE scraped_services SET status = 'imported', automation_status = 'completed' WHERE id = $1 AND owner_id = $2",
[id, req.user.accountId]
);
// 2. Dejamos constancia en la trazabilidad (Historial)
await pool.query(
"INSERT INTO scraped_service_logs (scraped_id, user_name, action, details) VALUES ($1, $2, $3, $4)",
[id, "Admin", "Traspaso Manual", "El expediente ha sido validado y asignado desde el buzón."]
);
res.json({ ok: true });
} catch (e) {
console.error("Error en traspaso manual:", e);
res.status(500).json({ ok: false, error: "Error en el servidor" });
}
});
// ==========================================
// ⚙️ MOTOR AUTOMÁTICO DE WHATSAPP Y APP SETTINGS (AÑADIDO PARA SOLUCIONAR ERROR 404)
// ==========================================
app.get("/whatsapp/status", authMiddleware, async (req, res) => {
try {
const instanceName = `cliente_${req.user.accountId}`;
if (!EVOLUTION_BASE_URL || !EVOLUTION_API_KEY) return res.json({ ok: false, error: "Servidor Evolution no configurado" });
const baseUrl = EVOLUTION_BASE_URL.replace(/\/$/, "");
const headers = { "Content-Type": "application/json", "apikey": EVOLUTION_API_KEY.trim() };
// 1. Verificamos si la instancia existe en Evolution
let statusRes = await fetch(`${baseUrl}/instance/connectionState/${instanceName}`, { headers });
// 2. Si da 404, significa que nunca se ha creado. La creamos y pedimos el QR
if (statusRes.status === 404) {
await fetch(`${baseUrl}/instance/create`, {
method: 'POST', headers,
body: JSON.stringify({ instanceName: instanceName, qrcode: true, integration: "WHATSAPP-BAILEYS" })
});
// Esperamos un poco y volvemos a consultar
await new Promise(r => setTimeout(r, 2000));
statusRes = await fetch(`${baseUrl}/instance/connectionState/${instanceName}`, { headers });
}
const data = await statusRes.json();
// 3. Devolvemos el estado
if (data.instance?.state === 'open') {
return res.json({ ok: true, state: 'open', instanceName });
} else {
// Buscamos el QR
const qrRes = await fetch(`${baseUrl}/instance/connect/${instanceName}`, { headers });
const qrData = await qrRes.json();
return res.json({ ok: true, state: 'connecting', qr: qrData.base64 || qrData.code });
}
} catch (e) {
console.error("Error consultando estado WA:", e);
res.status(500).json({ ok: false, error: "Error de conexión con el motor WA" });
}
});
app.get("/whatsapp/settings", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT wa_settings FROM users WHERE id=$1", [req.user.accountId]);
res.json({ ok: true, settings: q.rows[0]?.wa_settings || {} });
} catch (e) { res.status(500).json({ ok: false }); }
});
app.post("/whatsapp/settings", authMiddleware, async (req, res) => {
try {
// 👇 MAGIA: Usamos COALESCE y el operador || de PostgreSQL para FUSIONAR los JSON.
// Así, si guardas la IA, no borra el Robot, y si guardas el Robot, no borra la IA.
await pool.query(`
UPDATE users
SET wa_settings = COALESCE(wa_settings, '{}'::jsonb) || $1::jsonb
WHERE id=$2
`, [JSON.stringify(req.body), req.user.accountId]);
res.json({ ok: true });
} catch (e) {
console.error("Error guardando settings:", e);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 💬 NÚCLEO DE ENVÍO WHATSAPP (EVOLUTION API - MODO HUMANO DINÁMICO)
// ==========================================
async function sendWhatsAppAuto(phone, message, instanceName, useDelay = false) {
if (!EVOLUTION_BASE_URL || !EVOLUTION_API_KEY) {
console.error("⚠️ WhatsApp omitido: Servidor Evolution no configurado.");
return false;
}
// 🧠 Lógica de escritura "Modo Humano"
let timeToType = 0;
if (useDelay) {
// Calcula 50ms por cada carácter del mensaje
let baseTime = message.length * 50;
// Nos aseguramos de que tarde entre 1.5 segundos (mínimo) y 10 segundos (máximo)
baseTime = Math.min(Math.max(baseTime, 1500), 10000);
// Sumamos un margen aleatorio (entre 0 y 1.5 segundos) para que nunca tarde exactamente lo mismo
const factorAleatorio = Math.floor(Math.random() * 1500);
timeToType = baseTime + factorAleatorio;
console.log(`[WA] Modo Humano: Simulando escritura durante ${Math.round(timeToType/1000)} segundos para un texto de ${message.length} letras.`);
}
try {
const baseUrl = EVOLUTION_BASE_URL.replace(/\/$/, "");
const url = `${baseUrl}/message/sendText/${instanceName}`;
const response = await fetch(url, {
method: 'POST',
headers: {
"Content-Type": "application/json",
"apikey": EVOLUTION_API_KEY.trim()
},
body: JSON.stringify({
number: phone,
text: message,
delay: timeToType // Usamos el tiempo dinámico que acabamos de calcular
})
});
if (!response.ok) {
console.error("❌ Fallo enviando WhatsApp. Estado Evolution:", response.status);
return false;
}
return true;
} catch (error) {
console.error("❌ Error de red conectando con Evolution API:", error.message);
return false;
}
}
// ==========================================
// 🔔 DISPARADOR DE EVENTOS DE WHATSAPP (CON MODO SEGURO)
// ==========================================
async function triggerWhatsAppEvent(ownerId, serviceId, eventType) {
try {
const userQ = await pool.query("SELECT wa_settings FROM users WHERE id=$1", [ownerId]);
const settings = userQ.rows[0]?.wa_settings || {};
const checkSwitch = eventType === 'wa_evt_update' ? 'wa_evt_date' : eventType;
if (!settings[checkSwitch]) return false; // Botón apagado = No enviado
const tplTypeMap = {
'wa_evt_welcome': 'welcome',
'wa_evt_assigned': 'assigned',
'wa_evt_date': 'appointment',
'wa_evt_update': 'update',
'wa_evt_onway': 'on_way',
'wa_evt_survey': 'survey'
};
const tplQ = await pool.query("SELECT content FROM message_templates WHERE owner_id=$1 AND type=$2", [ownerId, tplTypeMap[eventType]]);
if (tplQ.rowCount === 0 || !tplQ.rows[0].content) return false;
let text = tplQ.rows[0].content;
const svcQ = await pool.query("SELECT * FROM scraped_services WHERE id=$1", [serviceId]);
if (svcQ.rowCount === 0) return false;
const s = svcQ.rows[0];
const raw = s.raw_data || {};
// 1. EXTRAER TELÉFONO DEL CLIENTE LIMPIO
let rawPhone = raw["Teléfono"] || raw["TELEFONO"] || raw["TELEFONOS"] || "";
let cleanPhoneToMatch = extractValidPhone(rawPhone);
if (cleanPhoneToMatch.length < 9) return false; // Si no hay teléfono válido, cancelamos
const finalPhoneToSend = "34" + cleanPhoneToMatch;
// 2. Buscamos el token del portal cliente (o lo creamos si no existe)
let token = "ERROR";
const clientQ = await pool.query("SELECT portal_token FROM clients WHERE phone LIKE $1 AND owner_id=$2 LIMIT 1", [`%${cleanPhoneToMatch}%`, ownerId]);
if (clientQ.rowCount > 0) {
token = clientQ.rows[0].portal_token;
} else {
const cName = raw["Nombre Cliente"] || raw["CLIENTE"] || "Asegurado";
const cAddr = raw["Dirección"] || raw["DOMICILIO"] || "";
const insertC = await pool.query(
"INSERT INTO clients (owner_id, full_name, phone, addresses) VALUES ($1, $2, $3, $4) RETURNING portal_token",
[ownerId, cName, finalPhoneToSend, JSON.stringify([cAddr])]
);
token = insertC.rows[0].portal_token;
}
const linkMagico = `https://portal.integrarepara.es/?token=${token}&service=${serviceId}`;
let fechaLimpia = raw["scheduled_date"] || "la fecha acordada";
if (fechaLimpia.includes("-")) {
const partes = fechaLimpia.split("-");
if (partes.length === 3) {
const fechaObj = new Date(partes[0], partes[1] - 1, partes[2], 12, 0, 0);
const diaSemana = fechaObj.toLocaleDateString('es-ES', { weekday: 'long' });
fechaLimpia = `(${diaSemana}) ${partes[2]}/${partes[1]}/${partes[0]}`;
}
}
text = text.replace(/{{NOMBRE}}/g, raw["Nombre Cliente"] || raw["CLIENTE"] || "Cliente");
text = text.replace(/{{DIRECCION}}/g, raw["Dirección"] || raw["DOMICILIO"] || "su domicilio");
text = text.replace(/{{FECHA}}/g, fechaLimpia);
text = text.replace(/{{HORA}}/g, raw["scheduled_time"] || "la hora acordada");
text = text.replace(/{{COMPANIA}}/g, raw["Compañía"] || raw["COMPAÑIA"] || "su Aseguradora");
text = text.replace(/{{REFERENCIA}}/g, s.service_ref || raw["Referencia"] || raw["Nº Siniestro"] || serviceId);
text = text.replace(/{{ENLACE}}/g, linkMagico);
const useDelay = settings.wa_delay_enabled !== false;
const instanceName = `cliente_${ownerId}`;
// ====================================================
// 🛑 MODO PRUEBAS (Desvía los mensajes a tu móvil)
// ====================================================
const MODO_PRUEBAS = false; // Cambia esto a 'false' para enviar a clientes reales
const MI_TELEFONO = "34667248132"; // <--- TU NÚMERO DE MÓVIL YA CONFIGURADO
if (MODO_PRUEBAS) {
console.log(`🛡️ [MODO PRUEBAS] Enviando WA al admin (${MI_TELEFONO}) en lugar de al cliente (${finalPhoneToSend})`);
const textoPrueba = `*(SIMULACIÓN PARA CLIENTE: ${finalPhoneToSend})*\n\n` + text;
return await sendWhatsAppAuto(MI_TELEFONO, textoPrueba, instanceName, useDelay);
} else {
console.log(`[WA] Enviando mensaje real al cliente: ${finalPhoneToSend}`);
return await sendWhatsAppAuto(finalPhoneToSend, text, instanceName, useDelay);
}
} catch (e) {
console.error("Error Motor WA:", e.message);
return false;
}
}
// ==========================================
// 🔐 CREDENCIALES DE PROVEEDORES
// ==========================================
app.get("/providers/credentials", authMiddleware, async (req, res) => {
try {
// 🛠️ FIX 1: Añadimos 'auto_dispatch' a la consulta para que el botón "recuerde" si está encendido
const q = await pool.query("SELECT provider, username, last_sync, status, auto_dispatch FROM provider_credentials WHERE owner_id=$1", [req.user.accountId]);
res.json({ ok: true, credentials: q.rows });
} catch (e) {
res.status(500).json({ ok: false });
}
});
app.post("/providers/credentials", authMiddleware, async (req, res) => {
try {
const { provider, username, password, auto_dispatch } = req.body;
// Si no hay contraseña, pasamos null
const passwordSafe = (password && password.trim() !== "") ? Buffer.from(password).toString('base64') : null;
const autoDispatchBool = auto_dispatch === true;
// 🛠️ FIX 2: Usamos COALESCE($4, '') en el INSERT.
// Si es la primera vez y no pones clave, guarda un texto vacío ('') en vez de null para que la base de datos no explote con error 500.
await pool.query(`
INSERT INTO provider_credentials (owner_id, provider, username, password_hash, auto_dispatch)
VALUES ($1, $2, $3, COALESCE($4, ''), $5)
ON CONFLICT (owner_id, provider) DO UPDATE SET
username = EXCLUDED.username,
password_hash = COALESCE($4, provider_credentials.password_hash),
auto_dispatch = EXCLUDED.auto_dispatch,
status = 'active'
`, [req.user.accountId, provider, username, passwordSafe, autoDispatchBool]);
res.json({ ok: true });
} catch (e) {
console.error("Error guardando credenciales:", e.message);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 🚀 MOTOR DE AUTO-ASIGNACIÓN (BOLSA INTERNA)
// ==========================================
async function dispatchToBolsa(serviceId, guildId, cp, accountId, userId) {
try {
let workersQ = await pool.query(`
SELECT u.id, u.full_name, u.phone FROM users u
JOIN user_guilds ug ON u.id = ug.user_id
WHERE u.owner_id = $1 AND u.role = 'operario' AND u.status = 'active'
AND ug.guild_id = $2 AND u.zones::jsonb @> $3::jsonb
`, [accountId, guildId, JSON.stringify([{ cps: (cp || "00000").toString() }])]);
// 🔥 Se ha ELIMINADO el bloque fallback. Si nadie tiene el CP, abortamos misión y se queda en el buzón.
if (workersQ.rowCount === 0) {
console.log(`⚠️ [AUTO-DISPATCH] Abortado: No hay operarios de ese gremio dados de alta en el CP ${cp}.`);
return { ok: false, error: "No hay operarios con este código postal asignado." };
}
await pool.query("UPDATE scraped_services SET automation_status = 'in_progress' WHERE id = $1", [serviceId]);
const worker = workersQ.rows[Math.floor(Math.random() * workersQ.rows.length)];
const token = crypto.randomBytes(16).toString('hex');
await pool.query(`INSERT INTO assignment_pings (scraped_id, user_id, token, expires_at) VALUES ($1, $2, $3, CURRENT_TIMESTAMP + INTERVAL '5 minutes')`, [serviceId, worker.id, token]);
// 🧠 Buscamos el nombre del gremio para el WhatsApp
let nombreGremio = "URGENCIA";
const gQ = await pool.query("SELECT name FROM guilds WHERE id = $1", [guildId]);
if(gQ.rowCount > 0) nombreGremio = gQ.rows[0].name.toUpperCase();
const msg = `🚨 *NUEVO AVISO: ${nombreGremio}*\n📍 Código Postal: ${(cp && cp !== "00000") ? cp : "Zona Asignada"}\n\nTienes un aviso urgente pendiente. Entra aquí para ver la avería y aceptarlo antes de que caduque el turno:\n🔗 https://web.integrarepara.es/aceptar.html?t=${token}`;
sendWhatsAppAuto(worker.phone, msg, `cliente_${accountId}`, false).catch(console.error);
await registrarMovimiento(serviceId, userId, "Bolsa Automática", `Notificación enviada a: ${worker.full_name}`);
console.log(`✅ [AUTO-DISPATCH] Urgencia enviada con éxito a ${worker.full_name}`);
return { ok: true };
} catch (e) {
console.error("❌ Error en dispatchToBolsa:", e);
return { ok: false, error: "Error interno" };
}
}
// ==========================================
// 📥 RECEPCIÓN DE SERVICIOS (EL MOTOR DEFINITIVO)
// ==========================================
app.post("/providers/scraped", authMiddleware, async (req, res) => {
try {
const { provider, services } = req.body;
if (!provider || !Array.isArray(services)) return res.status(400).json({ ok: false });
// 1. Cargamos el interruptor (siendo flexibles con 1 o true)
const credsQ = await pool.query(
"SELECT auto_dispatch FROM provider_credentials WHERE owner_id = $1 AND provider = $2",
[req.user.accountId, provider]
);
const autoDispatchEnabled = credsQ.rowCount > 0 &&
(credsQ.rows[0].auto_dispatch === true || credsQ.rows[0].auto_dispatch === 1 || credsQ.rows[0].auto_dispatch === '1');
// 2. Cargamos los gremios y sus palabras clave para detectar el gremio si falta
const allGuilds = await pool.query("SELECT id, name, ia_keywords FROM guilds WHERE owner_id = $1", [req.user.accountId]);
let count = 0;
for (const svc of services) {
const ref = svc['service_ref'] || svc['SERVICIO'] || svc['Referencia'] || svc['Expediente'] ||
(svc.raw_data && (svc.raw_data['SERVICIO'] || svc.raw_data['Referencia']));
if (!ref) continue;
// DETECCIÓN DE URGENCIA
let esUrgente = false;
const textoLimpio = JSON.stringify(svc).toLowerCase().normalize("NFD").replace(/[\u0300-\u036f]/g, "");
if (textoLimpio.includes("urgencia") || textoLimpio.includes("urgente") || textoLimpio.includes("por atencion")) {
esUrgente = true;
}
// DETECCIÓN DE GREMIO (Si no viene, lo buscamos nosotros)
let guildId = svc.guild_id || svc['guild_id'] || (svc.raw_data && svc.raw_data.guild_id);
if (!guildId) {
for (const g of allGuilds.rows) {
const keywords = Array.isArray(g.ia_keywords) ? g.ia_keywords : [];
if (keywords.some(kw => textoLimpio.includes(kw.toLowerCase()))) {
guildId = g.id;
break;
}
}
}
// 🚨 SOLUCIÓN DEFINITIVA PARA ARCHIVADOS:
let scraperStatus = (svc.status === 'archived') ? 'archived' : 'pending';
// 🛑 NUEVO: ESCUDO DETECTOR DE NOVEDADES (100% Fiable)
const checkExist = await pool.query("SELECT id FROM scraped_services WHERE owner_id=$1 AND provider=$2 AND service_ref=$3", [req.user.accountId, provider, ref]);
const isNewRecord = (checkExist.rowCount === 0);
// GUARDAR EN BD
const insertRes = await pool.query(`
INSERT INTO scraped_services (owner_id, provider, service_ref, status, raw_data, is_urgent)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT (owner_id, provider, service_ref)
DO UPDATE SET
status = CASE
WHEN EXCLUDED.status = 'archived' THEN 'archived'
ELSE scraped_services.status
END,
raw_data = scraped_services.raw_data || EXCLUDED.raw_data
RETURNING id, automation_status
`, [req.user.accountId, provider, ref, scraperStatus, JSON.stringify(svc), esUrgente]);
const newSvcId = insertRes.rows[0].id;
const autoStatus = insertRes.rows[0].automation_status;
// 📢 ¡CHIVATO DE CONSOLA!
console.log(`[DETECTOR-PRO] Ref: ${ref} | Urgente: ${esUrgente} | Gremio: ${guildId} | Es Nuevo: ${isNewRecord}`);
// 👋 1. DISPARADOR DEL MENSAJE DE BIENVENIDA
if (isNewRecord) {
console.log(`👋 [BIENVENIDA] Expediente #${ref} acaba de nacer. Disparando WhatsApp...`);
triggerWhatsAppEvent(req.user.accountId, newSvcId, 'wa_evt_welcome').catch(console.error);
}
// 🔥 2. LANZAMIENTO AUTOMÁTICO A LA BOLSA 🔥
if (esUrgente && guildId && autoDispatchEnabled && (autoStatus === 'manual' || autoStatus === 'pending')) {
console.log(`⚡ [AUTO-DISPATCH] Lanzando a la bolsa: ${ref}`);
const cpMatch = textoLimpio.match(/\b\d{5}\b/);
dispatchToBolsa(newSvcId, guildId, cpMatch ? cpMatch[0] : "00000", req.user.accountId, req.user.sub).catch(console.error);
}
count++;
}
res.json({ ok: true, inserted: count });
} catch (error) {
console.error("❌ Error grave en recepción:", error);
res.status(500).json({ ok: false, error: error.message });
}
});
// ==========================================
// 📤 OBTENER SERVICIOS PARA EL BUZÓN (Y BOLSA DE LA APP)
// ==========================================
app.get("/providers/scraped", authMiddleware, async (req, res) => {
try {
let query = `
SELECT s.*, ap.token as active_token,
EXTRACT(EPOCH FROM (ap.expires_at - CURRENT_TIMESTAMP)) as seconds_left,
u.full_name as current_worker_name,
(SELECT json_agg(json_build_object('name', u2.full_name, 'phone', u2.phone))
FROM assignment_pings ap2 JOIN users u2 ON ap2.user_id = u2.id
WHERE ap2.scraped_id = s.id AND ap2.status IN ('expired', 'rejected')) as attempted_workers_data
FROM scraped_services s
LEFT JOIN assignment_pings ap ON s.id = ap.scraped_id AND ap.status = 'pending'
LEFT JOIN users u ON ap.user_id = u.id
WHERE s.owner_id = $1
`;
const params = [req.user.accountId];
// 🛡️ ESCUDO DE ZONA: Si es operario, aplicamos filtros de gremio y CP obligatorios
if (req.user.role === 'operario') {
query += ` AND s.assigned_to IS NULL AND s.status != 'archived'`;
// 🚨 FIX: Usamos NULLIF para evitar que un guild_id vacío ("") rompa el casting a número
query += ` AND NULLIF(s.raw_data->>'guild_id', '')::int IN (SELECT guild_id FROM user_guilds WHERE user_id = $2)`;
// 2. El CP de la avería tiene que coincidir SÍ o SÍ con los del operario
// Buscamos el CP en varios campos del JSON para asegurarnos de que no se escapa
query += ` AND EXISTS (
SELECT 1 FROM jsonb_array_elements((SELECT zones FROM users WHERE id = $2)) as z
WHERE z->>'cps' = COALESCE(s.raw_data->>'Código Postal', s.raw_data->>'C.P.', substring(s.raw_data::text from '\\y[0-9]{5}\\y'), '00000')
)`;
params.push(req.user.sub);
}
query += ` ORDER BY s.created_at DESC`;
const q = await pool.query(query, params);
const services = q.rows.map(row => {
if (row.seconds_left && row.seconds_left > 0) row.token_expires_at = new Date(Date.now() + (row.seconds_left * 1000));
return row;
});
res.json({ ok: true, services });
} catch (e) {
console.error("Error buzón/bolsa:", e.message);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 🤖 RUTA DE AUTOMATIZACIÓN (BOTÓN DEL RAYO)
// ==========================================
app.post("/providers/automate/:id", authMiddleware, async (req, res) => {
try {
const { guild_id, cp } = req.body;
const result = await dispatchToBolsa(req.params.id, guild_id, cp, req.user.accountId, req.user.sub);
if (result.ok) res.json({ ok: true });
else res.status(404).json(result);
} catch (e) { res.status(500).json({ ok: false }); }
});
// [EL RESTO DEL CÓDIGO PERMANECE IGUAL DESDE app.put('/providers/scraped/:id'...]
app.put('/providers/scraped/:id', authMiddleware, async (req, res) => {
const { id } = req.params;
// Extraemos todos los datos que vienen del frontend
let { automation_status, status, name, phone, address, cp, description, guild_id, assigned_to, assigned_to_name, internal_notes, client_notes, is_urgent, scheduled_date, scheduled_time, status_operativo, ...extra } = req.body;
try {
if (automation_status) {
await pool.query(`UPDATE scraped_services SET automation_status = $1 WHERE id = $2 AND owner_id = $3`, [automation_status, id, req.user.accountId]);
return res.json({ ok: true });
}
const current = await pool.query('SELECT raw_data, assigned_to, status, is_urgent FROM scraped_services WHERE id = $1 AND owner_id = $2', [id, req.user.accountId]);
if (current.rows.length === 0) return res.status(404).json({ error: 'No encontrado' });
let rawActual = current.rows[0].raw_data || {};
let oldStatus = String(rawActual.status_operativo || "");
let newStatus = String(status_operativo !== undefined ? status_operativo : oldStatus);
const oldDate = String(rawActual.scheduled_date || "");
const newDate = String(scheduled_date !== undefined ? scheduled_date : oldDate);
const oldTime = String(rawActual.scheduled_time || "");
const newTime = String(scheduled_time !== undefined ? scheduled_time : oldTime);
const statusChanged = (newStatus !== oldStatus && newStatus !== "" && newStatus !== "null");
const dateChanged = (newDate !== oldDate && newDate !== "" && newDate !== "null");
const timeChanged = (newTime !== oldTime && newTime !== "" && newTime !== "null");
const oldWorkerId = current.rows[0].assigned_to || rawActual.assigned_to;
let finalAssignedTo = assigned_to !== undefined ? (assigned_to === "" ? null : assigned_to) : oldWorkerId;
// 🚨 NUEVO: Detectar si le acabamos de asignar un técnico (de la nada a alguien, o de un técnico a otro distinto)
const workerNewlyAssigned = finalAssignedTo && (String(finalAssignedTo) !== String(oldWorkerId));
let stName = "";
if (newStatus && newStatus !== "null") {
const statusQ = await pool.query("SELECT name FROM service_statuses WHERE id=$1", [newStatus]);
stName = (statusQ.rows[0]?.name || "").toLowerCase();
}
// 🚨 EL EXORCISMO: Si pasa a Pendiente o Desasignado, forzamos la limpieza del operario
if (stName.includes('pendiente') || stName.includes('desasignado')) {
finalAssignedTo = null;
assigned_to_name = "";
}
console.log(`🤖 [DEBUG ADMIN-PANEL] Exp: ${id} | Estado: '${stName}' | statusChanged: ${statusChanged} | dateChanged: ${dateChanged} | timeChanged: ${timeChanged}`);
// 🚨 AQUÍ ESTABA EL BUG: AHORA RE-INYECTAMOS TODOS LOS CAMPOS AL JSON 🚨
const updatedRawData = {
...rawActual, ...extra,
"Nombre Cliente": name !== undefined ? name : rawActual["Nombre Cliente"],
"Teléfono": phone !== undefined ? phone : rawActual["Teléfono"],
"Dirección": address !== undefined ? address : rawActual["Dirección"],
"Código Postal": cp !== undefined ? cp : (rawActual["Código Postal"] || rawActual["C.P."]),
"Descripción": description !== undefined ? description : rawActual["Descripción"],
"guild_id": guild_id !== undefined ? guild_id : rawActual.guild_id,
"internal_notes": internal_notes !== undefined ? internal_notes : rawActual.internal_notes,
"client_notes": client_notes !== undefined ? client_notes : rawActual.client_notes,
"assigned_to": finalAssignedTo, // 🧄 EL REMATE AL ZOMBIE
"assigned_to_name": assigned_to_name !== undefined ? assigned_to_name : rawActual.assigned_to_name,
"scheduled_date": newDate,
"scheduled_time": newTime,
"status_operativo": newStatus === "null" ? null : newStatus
};
// Rescatamos si es urgente (es una columna física en tu base de datos)
const isUrgentFinal = is_urgent !== undefined ? is_urgent : current.rows[0].is_urgent;
// 🧹 AUTO-ARCHIVO: Si es finalizado o anulado, lo quitamos del buzón de proveedores
const isFinal = stName.includes('finalizado') || stName.includes('terminado') || stName.includes('anulado');
const mainStatus = isFinal ? 'archived' : 'imported';
await pool.query(
`UPDATE scraped_services SET raw_data = $1, assigned_to = $2, is_urgent = $3, status = $6 WHERE id = $4 AND owner_id = $5`,
[JSON.stringify(updatedRawData), finalAssignedTo, isUrgentFinal, id, req.user.accountId, mainStatus]
);
// BANDERAS INTELIGENTES
const isAsignado = stName.includes('asignado');
const isCitado = stName.includes('citado') || stName.includes('cita') || stName.includes('agendado');
const isNoLocalizado = stName.includes('no localizado') || stName.includes('buzon') || stName.includes('contesta');
// --- DISPARADORES WHATSAPP ---
if ((statusChanged && isAsignado && finalAssignedTo) || workerNewlyAssigned) {
triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_assigned').catch(console.error);
}
// 🔔 Se dispara si cambia el estado a citado, si cambia el día o si cambia la hora
if (isCitado && (statusChanged || dateChanged || timeChanged)) {
if (oldDate === "" || oldDate === "null") triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_date').catch(console.error);
else triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_update').catch(console.error);
}
// 🚀 DISPARAR ROBOTS SEGÚN PROVEEDOR
const checkProvider = await pool.query("SELECT provider FROM scraped_services WHERE id=$1", [id]);
const providerName = checkProvider.rows[0]?.provider;
if (providerName === 'homeserve') {
if ((statusChanged && isAsignado && finalAssignedTo) || workerNewlyAssigned) {
console.log("✅ [ADMIN] Disparando robot HS: ASIGNACIÓN");
triggerHomeServeRobot(req.user.accountId, id, 'assign').catch(console.error);
}
if (isCitado && (statusChanged || dateChanged || timeChanged) && newDate !== "" && newDate !== "null") {
console.log(`✅ [ADMIN] Disparando robot HS: CITA (${newDate} ${newTime})`);
triggerHomeServeRobot(req.user.accountId, id, 'date').catch(console.error);
}
if (isNoLocalizado && statusChanged) {
console.log(`✅ [ADMIN] Disparando robot HS: NO LOCALIZADO`);
triggerHomeServeRobot(req.user.accountId, id, 'notfound').catch(console.error);
}
}
else if (providerName === 'multiasistencia') {
if ((statusChanged && isAsignado && finalAssignedTo) || workerNewlyAssigned) {
console.log("✅ [ADMIN] Disparando robot MULTI: ASIGNACIÓN");
triggerMultiRobot(req.user.accountId, id, 'assign').catch(console.error);
}
if (isCitado && (statusChanged || dateChanged || timeChanged) && newDate !== "" && newDate !== "null") {
console.log(`✅ [ADMIN] Disparando robot MULTI: CITA (${newDate} ${newTime})`);
triggerMultiRobot(req.user.accountId, id, 'date').catch(console.error);
}
if (isNoLocalizado && statusChanged) {
console.log(`✅ [ADMIN] Disparando robot MULTI: NO LOCALIZADO`);
triggerMultiRobot(req.user.accountId, id, 'notfound').catch(console.error);
}
}
res.json({ ok: true });
} catch (error) {
console.error("❌ ERROR EN ACTUALIZACIÓN:", error);
res.status(500).json({ ok: false, error: error.message });
}
});
// Validar si una referencia ya existe
app.get("/services/check-ref", authMiddleware, async (req, res) => {
try {
const { ref } = req.query;
const q = await pool.query("SELECT id FROM scraped_services WHERE service_ref = $1 AND owner_id = $2", [ref, req.user.accountId]);
res.json({ exists: q.rowCount > 0 });
} catch (e) { res.status(500).json({ ok: false }); }
});
app.get("/discovery/keys/:provider", authMiddleware, async (req, res) => {
try {
const { provider } = req.params;
const rawServices = await pool.query("SELECT raw_data FROM scraped_services WHERE owner_id=$1 AND provider=$2 ORDER BY id DESC LIMIT 1", [req.user.accountId, provider]);
const mappings = await pool.query("SELECT original_key, target_key, is_ignored FROM variable_mappings WHERE owner_id=$1 AND provider=$2", [req.user.accountId, provider]);
const mapDict = {}; mappings.rows.forEach(m => { mapDict[m.original_key] = m; });
const discoverySet = new Set(); const samples = {};
rawServices.rows.forEach(row => { const data = row.raw_data; if (data && typeof data === 'object') { Object.keys(data).forEach(k => { discoverySet.add(k); if (!samples[k]) samples[k] = data[k]; }); } });
const result = Array.from(discoverySet).map(key => ({ original: key, sample: samples[key] || "(Vacío)", mappedTo: mapDict[key]?.target_key || "", ignored: mapDict[key]?.is_ignored || false })).sort((a, b) => a.original.localeCompare(b.original));
res.json({ ok: true, keys: result });
} catch (e) { res.status(500).json({ ok: false }); }
});
// RUTA PARA EL PANEL OPERATIVO (ADMIN VE TODO, OPERARIO VE LO SUYO)
app.get("/services/active", authMiddleware, async (req, res) => {
try {
let query = `
SELECT
s.*,
u.full_name as assigned_name
FROM scraped_services s
LEFT JOIN users u ON s.assigned_to = u.id
WHERE s.owner_id = $1
`;
// 🚨 MAGIA: Ya no ocultamos los archivados en la consulta general.
// Así, tú como Admin, SÍ los verás en la pantalla de Servicios (Kanban).
const params = [req.user.accountId];
// SI ES OPERARIO: Ve solo lo suyo.
// 👻 FANTASMAS: Enviamos los archivados de los últimos 15 días para que salgan translúcidos
if (req.user.role === 'operario' || req.user.role === 'operario_cerrado') {
query += ` AND s.assigned_to = $2 AND (s.status != 'archived' OR (s.status = 'archived' AND s.created_at >= NOW() - INTERVAL '15 days'))`;
params.push(req.user.sub);
}
query += ` ORDER BY s.created_at DESC`;
const q = await pool.query(query, params);
res.json({ ok: true, services: q.rows });
} catch (e) {
console.error("Error al cargar /services/active:", e);
res.status(500).json({ ok: false });
}
});
app.put("/services/set-appointment/:id", authMiddleware, async (req, res) => {
try {
const { id } = req.params;
let { date, time, status_operativo, skip_survey, ...extra } = req.body;
const current = await pool.query('SELECT raw_data, assigned_to FROM scraped_services WHERE id = $1 AND owner_id = $2', [id, req.user.accountId]);
if (current.rowCount === 0) return res.status(404).json({ ok: false, error: 'No encontrado' });
const rawActual = current.rows[0].raw_data || {};
// FIX CRÍTICO: Si la app no envía estado, mantenemos el que tenía el expediente para no borrarlo
if (status_operativo === undefined || status_operativo === "") {
status_operativo = rawActual.status_operativo || null;
}
let newDate = (date !== undefined) ? date : (rawActual.scheduled_date || "");
let newTime = (time !== undefined) ? time : (rawActual.scheduled_time || "");
let finalAssignedTo = current.rows[0].assigned_to;
let stName = "";
if (status_operativo) {
const statusQ = await pool.query("SELECT name FROM service_statuses WHERE id=$1", [status_operativo]);
stName = (statusQ.rows[0]?.name || "").toLowerCase();
}
if (stName.includes('pendiente') || stName.includes('desasignado') || stName.includes('anulado')) {
newDate = "";
newTime = "";
}
// GUARDAR EN BBDD
const updatedRawData = {
...rawActual, ...extra,
"scheduled_date": newDate,
"scheduled_time": newTime,
"status_operativo": status_operativo
};
// 🧹 AUTO-ARCHIVO DESDE LA APP: Si finaliza, lo quitamos del buzón de proveedores
const isFinalApp = stName.includes('finalizado') || stName.includes('terminado') || stName.includes('anulado');
const mainStatusApp = isFinalApp ? 'archived' : 'imported';
await pool.query('UPDATE scraped_services SET raw_data = $1, assigned_to = $2, status = $5 WHERE id = $3 AND owner_id = $4',
[JSON.stringify(updatedRawData), finalAssignedTo, id, req.user.accountId, mainStatusApp]
);
let logDetalle = `Estado modificado a: ${stName.toUpperCase() || 'MODIFICADO'}.`;
if (newDate) logDetalle += ` Cita para el ${newDate} a las ${newTime}.`;
await registrarMovimiento(id, req.user.sub, "Actualización desde App", logDetalle);
res.json({ ok: true });
// TAREAS EN SEGUNDO PLANO
(async () => {
try {
const statusChanged = String(status_operativo) !== String(rawActual.status_operativo);
const oldDate = rawActual.scheduled_date || "";
const dateChanged = newDate !== "" && newDate !== oldDate;
console.log(`🤖 [DEBUG APP-OP] Exp: ${id} | Estado: '${stName}' | statusChanged: ${statusChanged} | dateChanged: ${dateChanged}`);
// 🚀 LEER PROVEEDOR REAL (HOME SERVE O MULTIASISTENCIA)
const checkProv = await pool.query("SELECT provider FROM scraped_services WHERE id=$1", [id]);
const providerName = checkProv.rows[0]?.provider;
// BANDERAS INTELIGENTES
const isAsignado = stName.includes('asignado');
const isCitado = stName.includes('citado') || stName.includes('cita') || stName.includes('agendado');
const isNoLocalizado = stName.includes('no localizado') || stName.includes('buzon') || stName.includes('contesta');
// --- ASIGNADO ---
if (statusChanged && isAsignado) {
// Disparar Robot Inteligente
if (providerName === 'homeserve') triggerHomeServeRobot(req.user.accountId, id, 'assign').catch(console.error);
else if (providerName === 'multiasistencia') triggerMultiRobot(req.user.accountId, id, 'assign').catch(console.error);
// Notificar por WhatsApp y cambiar a Esperando al Cliente si tiene éxito
const waEnviadoExito = await triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_assigned');
if (waEnviadoExito) {
const estadoEsperando = await pool.query("SELECT id FROM service_statuses WHERE owner_id=$1 AND name='Esperando al Cliente' LIMIT 1", [req.user.accountId]);
if (estadoEsperando.rowCount > 0) {
updatedRawData.status_operativo = estadoEsperando.rows[0].id;
await pool.query('UPDATE scraped_services SET raw_data = $1 WHERE id = $2 AND owner_id = $3', [JSON.stringify(updatedRawData), id, req.user.accountId]);
}
}
}
// --- CITADO (CONFIRMAR CITA) ---
else if (isCitado && (statusChanged || dateChanged)) {
if (newDate !== "") {
// Disparar Robot Inteligente
if (providerName === 'homeserve') {
console.log(`✅ [APP-OP] Disparando robot HS: CITA (${newDate})`);
triggerHomeServeRobot(req.user.accountId, id, 'date').catch(console.error);
} else if (providerName === 'multiasistencia') {
console.log(`✅ [APP-OP] Disparando robot MULTI: CITA (${newDate})`);
triggerMultiRobot(req.user.accountId, id, 'date').catch(console.error);
}
}
// Notificar por WhatsApp
if (oldDate === "") await triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_date');
else if (oldDate !== newDate) await triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_update');
}
// --- NO LOCALIZADO ---
else if (isNoLocalizado && statusChanged) {
if (providerName === 'homeserve') {
console.log(`✅ [APP-OP] Disparando robot HS: NO LOCALIZADO`);
triggerHomeServeRobot(req.user.accountId, id, 'notfound').catch(console.error);
} else if (providerName === 'multiasistencia') {
console.log(`✅ [APP-OP] Disparando robot MULTI: NO LOCALIZADO`);
triggerMultiRobot(req.user.accountId, id, 'notfound').catch(console.error);
}
}
// --- CAMINO / FINALIZADO ---
else if (stName.includes('camino')) {
await triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_onway');
} else if (stName.includes('finalizado') || stName.includes('terminado')) {
if (!skip_survey) await triggerWhatsAppEvent(req.user.accountId, id, 'wa_evt_survey');
}
} catch (errBckg) {
console.error("Error en tareas de fondo:", errBckg);
}
})();
} catch (e) {
console.error("❌ ERROR EXACTO AL CAMBIAR ESTADO/CITA:", e.stack);
if (!res.headersSent) res.status(500).json({ ok: false, error: e.message, stack: e.stack });
}
});
// ==========================================
// 📞 RUTA PARA CLIENTE NO LOCALIZADO
// ==========================================
app.post("/services/not-found/:id", authMiddleware, async (req, res) => {
try {
const { id } = req.params;
const current = await pool.query('SELECT raw_data, provider, owner_id FROM scraped_services WHERE id=$1 AND owner_id=$2', [id, req.user.accountId]);
if (current.rowCount === 0) return res.status(404).json({ok: false});
const raw = current.rows[0].raw_data || {};
// Sumamos 1 al contador de llamadas
const currentCalls = parseInt(raw.called_times || 0) + 1;
raw.called_times = currentCalls;
// Guardamos en la base de datos
await pool.query("UPDATE scraped_services SET raw_data=$1 WHERE id=$2", [JSON.stringify(raw), id]);
// Intentamos enviar el WhatsApp usando la plantilla
let rawPhone = raw["Teléfono"] || raw["TELEFONOS"] || raw["TELEFONO"] || "";
let cleanPhoneToMatch = extractValidPhone(rawPhone);
if (cleanPhoneToMatch.length >= 9) { // Solo intentamos enviar si hay un número válido
// 🚨 Búsqueda flexible por si la plantilla se guardó con otro nombre interno
const tplQ = await pool.query("SELECT content FROM message_templates WHERE owner_id=$1 AND type IN ('not_found', 'unreachable', 'no_reply') LIMIT 1", [req.user.accountId]);
let text = tplQ.rowCount > 0 && tplQ.rows[0].content
? tplQ.rows[0].content
: `Hola {{NOMBRE}}, soy el técnico de {{COMPANIA}}. He intentado contactar contigo para agendar tu reparación (Exp. {{REFERENCIA}}), pero no ha sido posible. Por favor, pulsa aquí para elegir tu cita: {{ENLACE}}`;
const finalPhoneToSend = "34" + cleanPhoneToMatch;
let token = "ERROR";
const clientQ = await pool.query("SELECT portal_token FROM clients WHERE phone LIKE $1 AND owner_id=$2 LIMIT 1", [`%${cleanPhoneToMatch}%`, req.user.accountId]);
if (clientQ.rowCount > 0) {
token = clientQ.rows[0].portal_token;
} else {
const newToken = crypto.randomBytes(6).toString('hex');
const insertC = await pool.query(
"INSERT INTO clients (owner_id, full_name, phone, addresses, portal_token) VALUES ($1, $2, $3, '[]', $4) RETURNING portal_token",
[req.user.accountId, raw["Nombre Cliente"] || "Cliente", finalPhoneToSend, newToken]
);
token = insertC.rows[0].portal_token;
}
const linkMagico = `https://portal.integrarepara.es/?token=${token}&service=${id}`;
let fechaLimpia = raw["scheduled_date"] || "la fecha acordada";
if (fechaLimpia.includes("-")) {
const partes = fechaLimpia.split("-");
if (partes.length === 3) {
const fechaObj = new Date(partes[0], partes[1] - 1, partes[2], 12, 0, 0);
const diaSemana = fechaObj.toLocaleDateString('es-ES', { weekday: 'long' });
fechaLimpia = `(${diaSemana}) ${partes[2]}/${partes[1]}/${partes[0]}`;
}
}
// REEMPLAZO DE TODAS LAS VARIABLES (Como en la foto)
text = text.replace(/{{NOMBRE}}/g, raw["Nombre Cliente"] || raw["CLIENTE"] || "Cliente");
text = text.replace(/{{DIRECCION}}/g, raw["Dirección"] || raw["DOMICILIO"] || "su domicilio");
text = text.replace(/{{FECHA}}/g, fechaLimpia);
text = text.replace(/{{HORA}}/g, raw["scheduled_time"] || "la hora acordada");
text = text.replace(/{{COMPANIA}}/g, raw["Compañía"] || raw["COMPAÑIA"] || "su Aseguradora");
text = text.replace(/{{REFERENCIA}}/g, current.rows[0].service_ref || id);
text = text.replace(/{{ENLACE}}/g, linkMagico);
const userQ = await pool.query("SELECT wa_settings FROM users WHERE id=$1", [req.user.accountId]);
const settings = userQ.rows[0]?.wa_settings || {};
const useDelay = settings.wa_delay_enabled !== false;
// MODO PRUEBAS: Redirigir el mensaje a tu móvil
const MODO_PRUEBAS = false;
const MI_TELEFONO = "34667248132";
if (MODO_PRUEBAS) {
console.log(`🛡️ [MODO PRUEBAS NO LOCALIZADO] Desvío a tu móvil (${MI_TELEFONO})`);
const textoPrueba = `*(SIMULACIÓN NO LOCALIZADO PARA: ${finalPhoneToSend})*\n\n` + text;
await sendWhatsAppAuto(MI_TELEFONO, textoPrueba, `cliente_${req.user.accountId}`, useDelay);
} else {
await sendWhatsAppAuto(finalPhoneToSend, text, `cliente_${req.user.accountId}`, useDelay);
}
}
// --- INICIO TRAZABILIDAD ---
await registrarMovimiento(id, req.user.sub, "Intento de Contacto", `El operario reporta que el cliente no contesta. Total intentos: ${currentCalls}`);
// 🚀 DISPARAR ROBOTS SEGÚN PROVEEDOR
const providerName = current.rows[0].provider;
if (providerName === 'homeserve') {
console.log(`✅ [NO-LOCALIZADO] Disparando robot HS`);
triggerHomeServeRobot(req.user.accountId, id, 'notfound').catch(console.error);
} else if (providerName === 'multiasistencia') {
console.log(`✅ [NO-LOCALIZADO] Disparando robot MULTI`);
triggerMultiRobot(req.user.accountId, id, 'notfound').catch(console.error);
}
// --- FIN TRAZABILIDAD ---
res.json({ ok: true, called_times: currentCalls });
} catch (e) {
console.error("Error No Localizado:", e);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 📝 RUTAS DE PLANTILLAS DE MENSAJES
// ==========================================
app.get("/templates", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT type, content FROM message_templates WHERE owner_id=$1", [req.user.accountId]);
res.json({ ok: true, templates: q.rows });
} catch (e) {
res.status(500).json({ ok: false, error: e.message });
}
});
app.post("/templates", authMiddleware, async (req, res) => {
try {
const { type, content } = req.body;
if (!type) return res.status(400).json({ ok: false, error: "Falta el tipo de plantilla" });
await pool.query(`
INSERT INTO message_templates (owner_id, type, content)
VALUES ($1, $2, $3)
ON CONFLICT (owner_id, type) DO UPDATE SET content = EXCLUDED.content
`, [req.user.accountId, type, content || ""]);
res.json({ ok: true });
} catch (e) {
console.error("Error guardando plantilla:", e);
res.status(500).json({ ok: false, error: e.message });
}
});
// ==========================================
// 🎨 RUTAS DE ESTADOS DEL SISTEMA (SAAS COMPLETO)
// ==========================================
app.get("/statuses", authMiddleware, async (req, res) => {
try {
// 1. FORZAMOS LA INYECCIÓN/ACTUALIZACIÓN SIEMPRE
const defaults = [
{name:'Pendiente de Asignar', c:'gray', d:true, f:false, sys:true},
{name:'Asignado', c:'blue', d:false, f:false, sys:true},
{name:'Esperando al Cliente', c:'amber', d:false, f:false, sys:true},
{name:'Citado', c:'emerald', d:false, f:false, sys:true},
{name:'En espera de Compañía / Perito', c:'blue', d:false, f:false, sys:true}, // <--- NUEVO
{name:'De Camino', c:'indigo', d:false, f:false, sys:true},
{name:'Trabajando', c:'orange', d:false, f:false, sys:true},
{name:'Incidencia', c:'red', d:false, f:false, sys:true},
{name:'Desasignado', c:'rose', d:false, f:false, sys:true},
{name:'Finalizado', c:'purple', d:false, f:true, sys:true},
{name:'Anulado', c:'gray', d:false, f:true, sys:true}
];
for (const s of defaults) {
const check = await pool.query("SELECT id FROM service_statuses WHERE owner_id=$1 AND name=$2", [req.user.accountId, s.name]);
if(check.rowCount === 0){
await pool.query("INSERT INTO service_statuses (owner_id,name,color,is_default,is_final,is_system) VALUES ($1,$2,$3,$4,$5,$6)", [req.user.accountId,s.name,s.c,s.d,s.f,s.sys]);
} else {
await pool.query("UPDATE service_statuses SET is_system=true, color=$2, is_final=$3 WHERE id=$1", [check.rows[0].id, s.c, s.f]);
}
}
// 🧹 Limpiamos el candado a los viejos (Asegura que esta parte se mantiene igual)
const nombresOficiales = defaults.map(d => d.name);
await pool.query("UPDATE service_statuses SET is_system=false WHERE owner_id=$1 AND name != ALL($2::text[])", [req.user.accountId, nombresOficiales]);
// 🚀 FUSIÓN AUTOMÁTICA: Movemos todo lo viejo al nuevo y borramos el fantasma
let currentDb = await pool.query("SELECT * FROM service_statuses WHERE owner_id=$1", [req.user.accountId]);
const idEsperando = currentDb.rows.find(s => s.name === 'Esperando al Cliente')?.id;
const idPendiente = currentDb.rows.find(s => s.name === 'Pendiente de Cita')?.id;
if (idEsperando && idPendiente) {
// Pasamos los servicios normales
await pool.query("UPDATE services SET status_id = $1 WHERE status_id = $2 AND owner_id = $3", [idEsperando, idPendiente, req.user.accountId]);
// Pasamos los servicios del panel operativo (JSON)
await pool.query(`UPDATE scraped_services SET raw_data = jsonb_set(COALESCE(raw_data, '{}'::jsonb), '{status_operativo}', to_jsonb($1::text)) WHERE raw_data->>'status_operativo' = $2 AND owner_id = $3`, [String(idEsperando), String(idPendiente), req.user.accountId]);
// Exterminamos "Pendiente de Cita"
await pool.query("DELETE FROM service_statuses WHERE id = $1 AND owner_id = $2", [idPendiente, req.user.accountId]);
}
// 2. RECUPERAMOS LOS ESTADOS LIMPIOS
let q = await pool.query("SELECT * FROM service_statuses WHERE owner_id=$1", [req.user.accountId]);
// ORDENAMOS
let sortedStatuses = q.rows.sort((a, b) => {
let idxA = nombresOficiales.indexOf(a.name);
let idxB = nombresOficiales.indexOf(b.name);
if(idxA === -1) idxA = 99;
if(idxB === -1) idxB = 99;
return idxA - idxB;
});
res.json({ ok: true, statuses: sortedStatuses });
} catch (e) { res.status(500).json({ ok: false }); }
});
app.get("/clients/search", authMiddleware, async (req, res) => { try { const { phone } = req.query; const p = normalizePhone(phone); if(!p) return res.json({ok:true,client:null}); const q = await pool.query("SELECT * FROM clients WHERE phone=$1 AND owner_id=$2 LIMIT 1", [p, req.user.accountId]); res.json({ ok: true, client: q.rows[0] || null }); } catch (e) { res.status(500).json({ ok: false }); } });
// --- ENDPOINT PARA GENERAR ENLACE AL PORTAL DEL CLIENTE DESDE LA APP ---
app.post('/clients/ensure', authMiddleware, async (req, res) => {
try {
const { phone, name, address } = req.body;
if (!phone) return res.status(400).json({ ok: false, error: "Teléfono obligatorio" });
const cleanPhone = phone.replace('+34', '').replace(/\s+/g, '').trim();
const ownerId = req.user.accountId;
const q = await pool.query("SELECT * FROM clients WHERE phone LIKE $1 AND owner_id = $2 LIMIT 1", [`%${cleanPhone}%`, ownerId]);
if (q.rowCount > 0) {
let client = q.rows[0];
// PARCHE: Si el cliente existe pero no tiene token (porque es antiguo), se lo creamos
if (!client.portal_token) {
client.portal_token = crypto.randomBytes(6).toString('hex');
await pool.query("UPDATE clients SET portal_token = $1 WHERE id = $2", [client.portal_token, client.id]);
}
res.json({ ok: true, client });
} else {
const newToken = crypto.randomBytes(6).toString('hex');
const insert = await pool.query(
"INSERT INTO clients (owner_id, full_name, phone, addresses, portal_token) VALUES ($1, $2, $3, $4, $5) RETURNING portal_token",
[ownerId, name || "Cliente", phone, JSON.stringify([address || ""]), newToken]
);
res.json({ ok: true, client: { portal_token: insert.rows[0].portal_token } });
}
} catch (e) {
res.status(500).json({ ok: false, error: "Error interno del servidor" });
}
});
app.get("/companies", authMiddleware, async (req, res) => { try { const q = await pool.query("SELECT * FROM companies WHERE owner_id=$1 ORDER BY name ASC", [req.user.accountId]); res.json({ ok: true, companies: q.rows }); } catch (e) { res.status(500).json({ ok: false }); } });
app.post("/companies", authMiddleware, async (req, res) => { try { const { name } = req.body; await pool.query("INSERT INTO companies (name, owner_id) VALUES ($1, $2)", [name, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
app.delete("/companies/:id", authMiddleware, async (req, res) => { try { await pool.query("DELETE FROM companies WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
// AÑADIDO: Filtro estricto para que solo devuelva operarios que estén en estado 'active'
app.get("/operators", authMiddleware, async (req, res) => {
try {
const guildId = req.query.guild_id;
let query = `SELECT u.id, u.full_name, u.zones FROM users u WHERE u.owner_id=$1 AND u.role='operario' AND u.status='active'`;
const params = [req.user.accountId];
if (guildId) { query = `SELECT u.id, u.full_name, u.zones FROM users u JOIN user_guilds ug ON u.id = ug.user_id WHERE u.owner_id=$1 AND u.role='operario' AND u.status='active' AND ug.guild_id=$2`; params.push(guildId); }
query += ` ORDER BY u.full_name ASC`;
const q = await pool.query(query, params);
res.json({ ok: true, operators: q.rows });
} catch (e) { res.status(500).json({ ok: false }); }
});
app.get("/zones", authMiddleware, async (req, res) => { try { const q = await pool.query("SELECT * FROM zones WHERE owner_id=$1 ORDER BY name ASC", [req.user.accountId]); res.json({ ok: true, zones: q.rows }); } catch (e) { res.status(500).json({ ok: false }); } });
app.post("/zones", authMiddleware, async (req, res) => { try { const { name } = req.body; await pool.query("INSERT INTO zones (name, owner_id) VALUES ($1, $2)", [name, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
app.delete("/zones/:id", authMiddleware, async (req, res) => { try { await pool.query("DELETE FROM zones WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
app.get("/zones/:id/operators", authMiddleware, async (req, res) => { try { const q = await pool.query("SELECT user_id FROM user_zones WHERE zone_id=$1", [req.params.id]); res.json({ ok: true, assignedIds: q.rows.map(r=>r.user_id) }); } catch (e) { res.status(500).json({ ok: false }); } });
app.post("/zones/:id/assign", authMiddleware, async (req, res) => { const client = await pool.connect(); try { const { operator_ids } = req.body; await client.query('BEGIN'); await client.query("DELETE FROM user_zones WHERE zone_id=$1", [req.params.id]); if(operator_ids) for(const uid of operator_ids) await client.query("INSERT INTO user_zones (user_id, zone_id) VALUES ($1, $2)", [uid, req.params.id]); await client.query('COMMIT'); res.json({ok:true}); } catch(e){ await client.query('ROLLBACK'); res.status(500).json({ok:false}); } finally { client.release(); } });
app.get("/api/geo/municipios/:provincia", authMiddleware, async (req, res) => { try { let { provincia } = req.params; const provClean = provincia.toUpperCase().normalize("NFD").replace(/[\u0300-\u036f]/g, ""); const q = await pool.query("SELECT municipio, codigo_postal FROM master_geo_es WHERE provincia = $1 ORDER BY municipio ASC", [provClean]); res.json({ ok: true, municipios: q.rows }); } catch (e) { res.status(500).json({ ok: false }); } });
app.patch("/admin/users/:id/status", authMiddleware, async (req, res) => { try { const { status } = req.body; await pool.query("UPDATE users SET status = $1 WHERE id = $2 AND owner_id = $3", [status, req.params.id, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
app.get("/admin/users", authMiddleware, async (req, res) => { try { const q = await pool.query(`SELECT u.id, u.full_name, u.email, u.phone, u.role, u.zones, u.status, COALESCE(json_agg(g.id) FILTER (WHERE g.id IS NOT NULL), '[]') as guilds FROM users u LEFT JOIN user_guilds ug ON u.id=ug.user_id LEFT JOIN guilds g ON ug.guild_id=g.id WHERE u.owner_id=$1 GROUP BY u.id ORDER BY u.id DESC`, [req.user.accountId]); res.json({ ok: true, users: q.rows }); } catch (e) { res.status(500).json({ ok: false }); } });
app.post("/admin/users", authMiddleware, async (req, res) => { const client = await pool.connect(); try { const { fullName, email, password, role, guilds, phone, zones } = req.body; if (!email || !password || !fullName || !phone) return res.status(400).json({ ok: false }); const p = normalizePhone(phone); const hash = await bcrypt.hash(password, 10); const check = await client.query("SELECT id FROM users WHERE (phone=$1 OR email=$2) AND owner_id=$3", [p, email, req.user.accountId]); if (check.rowCount > 0) return res.status(400).json({ ok: false, error: "Duplicado" }); await client.query('BEGIN'); const insert = await client.query("INSERT INTO users (full_name, email, password_hash, role, phone, is_verified, owner_id, zones, status) VALUES ($1, $2, $3, $4, $5, TRUE, $6, $7, 'active') RETURNING id", [fullName, email, hash, role || 'operario', p, req.user.accountId, JSON.stringify(zones || [])]); const uid = insert.rows[0].id; if (guilds) for (const gid of guilds) await client.query("INSERT INTO user_guilds (user_id, guild_id) VALUES ($1, $2)", [uid, gid]); await client.query('COMMIT'); res.json({ ok: true }); } catch (e) { await client.query('ROLLBACK'); res.status(500).json({ ok: false }); } finally { client.release(); } });
app.put("/admin/users/:id", authMiddleware, async (req, res) => { const client = await pool.connect(); try { const userId = req.params.id; const { fullName, email, phone, role, guilds, password, zones } = req.body; const p = normalizePhone(phone); await client.query('BEGIN'); if(password) { const hash = await bcrypt.hash(password, 10); await client.query("UPDATE users SET full_name=$1, email=$2, phone=$3, role=$4, password_hash=$5, zones=$6 WHERE id=$7", [fullName, email, p, role, hash, JSON.stringify(zones || []), userId]); } else { await client.query("UPDATE users SET full_name=$1, email=$2, phone=$3, role=$4, zones=$5 WHERE id=$6", [fullName, email, p, role, JSON.stringify(zones || []), userId]); } if (guilds && Array.isArray(guilds)) { await client.query("DELETE FROM user_guilds WHERE user_id=$1", [userId]); for (const gid of guilds) await client.query("INSERT INTO user_guilds (user_id, guild_id) VALUES ($1, $2)", [userId, gid]); } await client.query('COMMIT'); res.json({ ok: true }); } catch (e) { await client.query('ROLLBACK'); res.status(500).json({ ok: false }); } finally { client.release(); } });
app.delete("/admin/users/:id", authMiddleware, async (req, res) => { try { await pool.query("DELETE FROM users WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
// ==========================================
// 🏢 CONFIGURACIÓN EMPRESA (COLORES, LOGO, PORTAL)
// ==========================================
app.get("/config/company", authMiddleware, async (req, res) => {
try {
// 🔎 AÑADIMOS 'billing_settings' A LA CONSULTA
const q = await pool.query("SELECT company_slug, full_name, plan_tier, company_logo, portal_settings, app_settings, billing_settings FROM users WHERE id=$1", [req.user.accountId]);
res.json({ ok: true, config: q.rows[0] || {} });
} catch (e) { res.status(500).json({ ok: false }); }
});
app.post("/config/company", authMiddleware, async (req, res) => {
const client = await pool.connect();
try {
const { slug, company_name, company_logo, portal_settings, app_settings, billing_settings } = req.body;
// 1. Manejo del Slug (Enlace)
let cleanSlug = null;
if (slug) {
cleanSlug = slug.toLowerCase().replace(/[^a-z0-9-]/g, "");
const check = await client.query("SELECT id FROM users WHERE company_slug=$1 AND id != $2", [cleanSlug, req.user.accountId]);
if (check.rowCount > 0) return res.status(400).json({ ok: false, error: "Enlace en uso" });
}
// 2. Asegurar que existe la columna de facturación
await client.query(`ALTER TABLE users ADD COLUMN IF NOT EXISTS billing_settings JSONB DEFAULT '{}'`);
// 3. UPDATE Inteligente: Solo cambia lo que le enviamos, el resto lo deja como está
await client.query(`
UPDATE users
SET company_slug = COALESCE($1, company_slug),
full_name = COALESCE($2, full_name),
company_logo = COALESCE($3, company_logo),
portal_settings = COALESCE(portal_settings, '{}'::jsonb) || COALESCE($4, '{}'::jsonb),
app_settings = COALESCE(app_settings, '{}'::jsonb) || COALESCE($5, '{}'::jsonb),
billing_settings = COALESCE(billing_settings, '{}'::jsonb) || COALESCE($6, '{}'::jsonb)
WHERE id = $7
`, [
cleanSlug,
company_name ?? null,
company_logo ?? null,
portal_settings ? JSON.stringify(portal_settings) : null,
app_settings ? JSON.stringify(app_settings) : null,
billing_settings ? JSON.stringify(billing_settings) : null,
req.user.accountId
]);
res.json({ ok: true });
} catch (e) {
console.error("❌ Error guardando config:", e);
res.status(500).json({ ok: false });
} finally { client.release(); }
});
app.get("/config/company", authMiddleware, async (req, res) => {
try {
// 4. Modificamos también el GET para que devuelva los datos de facturación al Frontend
const q = await pool.query("SELECT company_slug, full_name, plan_tier, company_logo, portal_settings, app_settings, billing_settings FROM users WHERE id=$1", [req.user.accountId]);
res.json({ ok: true, config: q.rows[0] || {} });
} catch (e) {
// Si falla porque la columna aún no se ha creado (el usuario nunca ha guardado), devolvemos el resto normal
try {
const fallback = await pool.query("SELECT company_slug, full_name, plan_tier, company_logo, portal_settings, app_settings FROM users WHERE id=$1", [req.user.accountId]);
res.json({ ok: true, config: fallback.rows[0] || {} });
} catch(fallbackError) {
res.status(500).json({ ok: false });
}
}
});
// RUTA: Alta de expediente manual con validación de cliente
app.post("/services/manual-high", authMiddleware, async (req, res) => {
try {
const { phone, name, address, description, guild_id, assigned_to, duration_minutes, mode, is_company, company_name, company_ref } = req.body;
const ownerId = req.user.accountId;
// 1. Manejo del Cliente (Buscamos si existe por teléfono)
const cleanPhone = phone.replace(/\D/g, "");
let clientQ = await pool.query("SELECT id, addresses FROM clients WHERE phone LIKE $1 AND owner_id = $2", [`%${cleanPhone}%`, ownerId]);
let clientId;
if (clientQ.rowCount > 0) {
clientId = clientQ.rows[0].id;
let currentAddrs = clientQ.rows[0].addresses || [];
// Si la dirección es nueva, la añadimos a su ficha
if (!currentAddrs.includes(address)) {
currentAddrs.push(address);
await pool.query("UPDATE clients SET addresses = $1 WHERE id = $2", [JSON.stringify(currentAddrs), clientId]);
}
} else {
// Si no existe, creamos cliente nuevo
const token = crypto.randomBytes(6).toString('hex');
const newClient = await pool.query(
"INSERT INTO clients (owner_id, full_name, phone, addresses, portal_token) VALUES ($1, $2, $3, $4, $5) RETURNING id",
[ownerId, name, phone, JSON.stringify([address]), token]
);
clientId = newClient.rows[0].id;
}
// 2. Crear el Expediente
const rawData = {
"Nombre Cliente": name,
"Teléfono": phone,
"Dirección": address,
"Descripción": description,
"guild_id": guild_id,
"scheduled_date": "",
"scheduled_time": "",
"duration_minutes": duration_minutes || 60,
"Compañía": is_company ? company_name : "Particular"
};
const serviceReference = is_company ? company_ref : `M-${Date.now().toString().slice(-6)}`;
const insertSvc = await pool.query(
`INSERT INTO scraped_services (owner_id, provider, service_ref, status, automation_status, assigned_to, raw_data)
VALUES ($1, 'MANUAL', $2, 'pending', $3, $4, $5) RETURNING id`,
[
ownerId,
serviceReference,
mode === 'auto' ? 'in_progress' : 'manual',
assigned_to || null,
JSON.stringify(rawData)
]
);
const newId = insertSvc.rows[0].id;
// 3. Si se eligió "Mandar a la bolsa", llamamos internamente al robot
if (mode === 'auto' && guild_id) {
const port = process.env.PORT || 3000;
fetch(`http://127.0.0.1:${port}/providers/automate/${newId}`, {
method: 'POST',
headers: { 'Content-Type': 'application/json', 'Authorization': req.headers.authorization },
body: JSON.stringify({ guild_id, cp: "00000" })
}).catch(e => console.error("Error lanzando bolsa:", e));
}
// --- TRAZABILIDAD ---
await registrarMovimiento(newId, req.user.sub, "Alta Manual", `Servicio creado manualmente (${rawData["Compañía"]}).`);
res.json({ ok: true, id: newId });
} catch (e) {
console.error("Error Alta Manual:", e);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 🛠️ RUTAS DE GREMIOS E INTELIGENCIA ARTIFICIAL
// ==========================================
app.get("/guilds", authMiddleware, async (req, res) => {
try {
let q = await pool.query("SELECT id, name, ia_keywords FROM guilds WHERE owner_id=$1 ORDER BY name ASC", [req.user.accountId]);
if (q.rowCount === 0) {
const defaults = [
{ n: "ELECTRICISTA", kw: '["electric", "cortocircuito", "cuadro electrico", "salto de plomos", "apagon", "diferencial", "icp", "magnetotermico", "chispazo", "sin luz", "cableado", "derivacion", "no hay luz", "salta el termico"]' },
{ n: "FONTANERIA", kw: '["fontaner", "fuga de agua", "tuberia", "atasco", "desatasco", "bote sifonico", "llave de paso", "calentador", "termo", "radiador", "caldera", "gotera", "inundacion", "filtracion", "bajante", "humedad"]' },
{ n: "CRISTALERIA", kw: '["cristal", "vidrio", "ventana rota", "escaparate", "luna", "espejo", "climalit", "doble acristalamiento", "velux", "rotura"]' },
{ n: "PERSIANAS", kw: '["motor persiana", "eje persiana", "persianista", "persiana atascada", "rotura de persiana", "domotica persiana"]' },
{ n: "CARPINTERIA", kw: '["carpinter", "puerta de madera", "bisagra", "marco", "rodapie", "tarima", "armario", "cepillar puerta", "cajon", "encimera", "madera hinchada"]' },
{ n: "ALBAÑILERIA", kw: '["albañil", "cemento", "yeso", "ladrillo", "azulejo", "desconchado", "grieta", "muro", "alicatado"]' },
{ n: "MANITAS ELECTRICISTA", kw: '["manitas electric", "cambiar bombilla", "colgar lampara", "instalar foco", "fluorescente", "casquillo", "lampara del dormitorio", "cambiar enchufe", "embellecedor"]' },
{ n: "MANITAS FONTANERIA", kw: '["manitas fontaner", "cambiar grifo", "sellar bañera", "silicona", "latiguillo", "alcachofa", "tapon", "cambiar cisterna", "descargador"]' },
{ n: "MANITAS PERSIANAS", kw: '["manitas persian", "cambiar cinta", "cuerda persiana", "recogedor", "atasco persiana", "lamas rotas", "persiana descolgada"]' },
{ n: "MANITAS GENERAL", kw: '["bombin", "colgar cuadro", "soporte tv", "estanteria", "montar mueble", "ikea", "cortina", "riel", "estor", "agujero", "taladro", "picaporte", "colgar espejo"]' }
];
for (const g of defaults) { await pool.query("INSERT INTO guilds (owner_id, name, ia_keywords) VALUES ($1, $2, $3::jsonb)", [req.user.accountId, g.n, g.kw]); }
q = await pool.query("SELECT id, name, ia_keywords FROM guilds WHERE owner_id=$1 ORDER BY name ASC", [req.user.accountId]);
}
res.json({ ok: true, guilds: q.rows });
} catch (e) { res.status(500).json({ ok: false }); }
});
app.post("/guilds", authMiddleware, async (req, res) => { try { const { name } = req.body; await pool.query("INSERT INTO guilds (name, owner_id) VALUES ($1, $2)", [name, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
app.delete("/guilds/:id", authMiddleware, async (req, res) => { try { await pool.query("DELETE FROM guilds WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]); res.json({ ok: true }); } catch (e) { res.status(500).json({ ok: false }); } });
app.put("/guilds/:id/ia-rules", authMiddleware, async (req, res) => {
try {
const { keywords } = req.body;
const guildId = req.params.id;
const safeKeywords = Array.isArray(keywords) ? keywords : [];
await pool.query("UPDATE guilds SET ia_keywords = $1 WHERE id = $2 AND owner_id = $3", [JSON.stringify(safeKeywords), guildId, req.user.accountId]);
res.json({ ok: true });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// ==========================================
// 🏆 MOTOR DE RANKING Y ESTADÍSTICAS
// ==========================================
function calculateScore(services) {
let score = 0;
const now = new Date();
const thirtyDaysAgo = new Date(now.getTime() - (30 * 24 * 60 * 60 * 1000));
// Separar servicios (usamos created_at para medir el último mes)
const openServices = services.filter(s => !s.is_final);
const closedLast30Days = services.filter(s => s.is_final && new Date(s.created_at) >= thirtyDaysAgo);
// --- 1. VELOCIDAD DE CIERRE (Max 30 Puntos) ---
let scoreCierre = 0;
if (closedLast30Days.length > 0) {
let totalDaysToClose = 0;
closedLast30Days.forEach(s => {
const created = new Date(s.created_at);
const closed = new Date(); // Estimamos el cierre en el ciclo actual
totalDaysToClose += Math.max(1, (closed - created) / (1000 * 60 * 60 * 24));
});
const avgCloseDays = totalDaysToClose / closedLast30Days.length;
if (avgCloseDays <= 2) scoreCierre = 30;
else if (avgCloseDays >= 14) scoreCierre = 0;
else scoreCierre = 30 - ((avgCloseDays - 2) * (30 / 12));
}
// --- 2. CITA RÁPIDA < 24h (Max 30 Puntos) ---
let scoreCita = 0;
let validSchedules = 0;
let fastSchedules = 0;
const recentServices = services.filter(s => new Date(s.created_at) >= thirtyDaysAgo);
recentServices.forEach(s => {
const raw = s.raw_data || {};
if (raw.scheduled_date) {
validSchedules++;
const created = new Date(s.created_at);
const [y, m, d] = raw.scheduled_date.split('-');
const schedDate = new Date(y, m - 1, d);
const diffDays = (schedDate - created) / (1000 * 60 * 60 * 24);
if (diffDays <= 1.5) fastSchedules++;
}
});
if (validSchedules > 0) {
const fastRatio = fastSchedules / validSchedules;
scoreCita = fastRatio * 30;
}
// --- 3. VOLUMEN DE TRABAJO AL DÍA (Max 20 Puntos) ---
let scoreVolumen = 0;
const closedPerDay = closedLast30Days.length / 22;
if (closedPerDay >= 3) scoreVolumen = 20;
else scoreVolumen = (closedPerDay / 3) * 20;
// --- 4. PENALIZACIÓN POR RE-CITAS (Max 20 Puntos) ---
let scoreRecitas = 20;
let totalCalled = 0;
recentServices.forEach(s => {
const raw = s.raw_data || {};
const calls = parseInt(raw.called_times || 0);
if (calls > 1) totalCalled += (calls - 1);
});
scoreRecitas = Math.max(0, 20 - (totalCalled * 2));
// --- SUMA BASE ---
let totalScore = scoreCierre + scoreCita + scoreVolumen + scoreRecitas;
// --- 5. PENALIZACIÓN POR ACUMULACIÓN DE ABIERTOS ---
let penalizacionAbiertos = 0;
if (openServices.length > 15) {
penalizacionAbiertos = (openServices.length - 15) * 1.5;
}
totalScore -= penalizacionAbiertos;
totalScore = Math.min(100, Math.max(0, totalScore));
return {
score: Math.round(totalScore),
details: {
cierre: Math.round(scoreCierre),
cita: Math.round(scoreCita),
volumen: Math.round(scoreVolumen),
recitas: Math.round(scoreRecitas),
penalizacion: Math.round(penalizacionAbiertos),
abiertos: openServices.length,
cerrados_mes: closedLast30Days.length
}
};
}
// RUTA GET PARA EL RANKING
app.get("/ranking", authMiddleware, async (req, res) => {
try {
// CORRECCIÓN: Hemos quitado "updated_at" de aquí para evitar que la base de datos se queje
const q = await pool.query(`
SELECT id, created_at, raw_data,
(SELECT is_final FROM service_statuses WHERE id::text = raw_data->>'status_operativo') as is_final
FROM scraped_services
WHERE assigned_to = $1
`, [req.user.sub]);
const rankingData = calculateScore(q.rows);
await pool.query(
"UPDATE users SET ranking_score = $1, ranking_data = $2 WHERE id = $3",
[rankingData.score, rankingData.details, req.user.sub]
);
res.json({ ok: true, ranking: rankingData });
} catch (error) {
console.error("Error en ranking:", error);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 📍 MOTOR GPS: RASTREO EN TIEMPO REAL
// ==========================================
// El operario envía su ubicación
app.post("/services/:id/location", authMiddleware, async (req, res) => {
try {
const { lat, lng } = req.body;
if (!lat || !lng) return res.status(400).json({ ok: false });
const locData = { lat, lng, updated_at: new Date().toISOString() };
// CORRECCIÓN: Ahora usa owner_id para que funcione aunque el admin esté haciendo la prueba
await pool.query(`
UPDATE scraped_services
SET raw_data = jsonb_set(COALESCE(raw_data, '{}'::jsonb), '{worker_location}', $1::jsonb)
WHERE id = $2 AND owner_id = $3
`, [JSON.stringify(locData), req.params.id, req.user.accountId]);
res.json({ ok: true });
} catch (e) {
res.status(500).json({ ok: false });
}
});
// El cliente consulta la ubicación
app.get("/public/portal/:token/location/:serviceId", async (req, res) => {
try {
const { token, serviceId } = req.params;
const clientQ = await pool.query("SELECT owner_id FROM clients WHERE portal_token = $1", [token]);
if (clientQ.rowCount === 0) return res.status(404).json({ ok: false });
const serviceQ = await pool.query("SELECT raw_data FROM scraped_services WHERE id = $1 AND owner_id = $2", [serviceId, clientQ.rows[0].owner_id]);
if (serviceQ.rowCount === 0) return res.status(404).json({ ok: false });
const loc = serviceQ.rows[0].raw_data.worker_location || null;
res.json({ ok: true, location: loc });
} catch (e) {
res.status(500).json({ ok: false });
}
});
// ==========================================
// 🔗 RUTAS PARA ACEPTAR/RECHAZAR AVISOS (PANTALLA OPERARIOS)
// ==========================================
app.get("/public/assignment/:token", async (req, res) => {
try {
const { token } = req.params;
// 🧠 AÑADIDO: Subconsulta mágica para sacar el nombre real del Gremio
const q = await pool.query(`
SELECT ap.*, s.raw_data, s.service_ref, s.is_urgent, u.full_name,
(SELECT name FROM guilds WHERE id::text = s.raw_data->>'guild_id' LIMIT 1) as real_guild_name
FROM assignment_pings ap
JOIN scraped_services s ON ap.scraped_id = s.id
JOIN users u ON ap.user_id = u.id
WHERE ap.token = $1
`, [token]);
if (q.rowCount === 0) return res.status(404).json({ ok: false, error: "Asignación no encontrada" });
const assign = q.rows[0];
if (assign.status !== 'pending') {
return res.json({ ok: false, error: "Este aviso ya fue " + (assign.status === 'accepted' ? 'aceptado' : 'rechazado o ha caducado.') });
}
if (new Date() > new Date(assign.expires_at)) {
await pool.query("UPDATE assignment_pings SET status = 'expired' WHERE id = $1", [assign.id]);
return res.json({ ok: false, error: "El tiempo para aceptar este aviso ha caducado." });
}
// Parsear datos para enviarlos bonitos a la pantalla de aceptar.html
const raw = assign.raw_data || {};
const serviceData = {
"Gremio": assign.real_guild_name || raw["Gremio"] || raw.guild_name || "Servicio General",
"Expediente": assign.service_ref || raw["Referencia"] || "Sin Ref",
"Dirección": raw["Dirección"] || raw["DOMICILIO"] || "",
"Población": raw["Población"] || raw["POBLACION-PROVINCIA"] || "",
"Código Postal": raw["Código Postal"] || "",
"Descripción": raw["Descripción"] || raw["DESCRIPCION"] || "Revisar en el lugar."
};
res.json({ ok: true, service: serviceData, debug: { hora_limite_bd: assign.expires_at } });
} catch (e) {
console.error("Error al obtener asignación:", e);
res.status(500).json({ ok: false });
}
});
app.post("/public/assignment/respond", async (req, res) => {
const client = await pool.connect();
try {
const { token, action } = req.body;
if (!token || !action) return res.status(400).json({ ok: false, error: "Faltan datos" });
await client.query('BEGIN');
// 1. Validar ping bloqueando la fila
const qPing = await client.query("SELECT * FROM assignment_pings WHERE token = $1 AND status = 'pending' FOR UPDATE", [token]);
if (qPing.rowCount === 0) {
await client.query('ROLLBACK');
return res.status(400).json({ ok: false, error: "Este aviso ya no está disponible." });
}
const ping = qPing.rows[0];
if (action === 'reject') {
await client.query("UPDATE assignment_pings SET status = 'rejected' WHERE id = $1", [ping.id]);
await client.query('COMMIT');
// --- INICIO TRAZABILIDAD ---
await registrarMovimiento(ping.scraped_id, ping.user_id, "Servicio Aceptado", "El operario ha aceptado el aviso desde su teléfono móvil.");
// --- FIN TRAZABILIDAD ---
return res.json({ ok: true });
}
if (action === 'accept') {
// 2. Marcar ping como aceptado
await client.query("UPDATE assignment_pings SET status = 'accepted' WHERE id = $1", [ping.id]);
// 3. Sacar datos del servicio
const sQ = await client.query("SELECT owner_id, raw_data FROM scraped_services WHERE id = $1", [ping.scraped_id]);
const ownerId = sQ.rows[0].owner_id;
let rawData = sQ.rows[0].raw_data || {};
// 4. Poner el estado en "Asignado" si existe
const statusQ = await client.query("SELECT id, name FROM service_statuses WHERE owner_id=$1 AND name ILIKE '%asignado%' LIMIT 1", [ownerId]);
const statusAsignadoId = statusQ.rows[0]?.id || null;
if (statusAsignadoId) {
rawData.status_operativo = statusAsignadoId;
}
// 5. Actualizar el expediente oficial con el técnico y BLINDARLO contra el robot
await client.query(`
UPDATE scraped_services
SET assigned_to = $1, automation_status = 'completed', status = 'imported', raw_data = $2
WHERE id = $3
`, [ping.user_id, JSON.stringify(rawData), ping.scraped_id]);
await client.query('COMMIT');
// 6. ¡Magia! Le enviamos un WhatsApp automático al cliente diciendo que ya tiene técnico
triggerWhatsAppEvent(ownerId, ping.scraped_id, 'wa_evt_assigned').catch(e => console.error(e));
return res.json({ ok: true });
}
await client.query('ROLLBACK');
res.status(400).json({ ok: false, error: "Acción no válida" });
} catch (e) {
await client.query('ROLLBACK');
console.error("Error respondiendo asignación:", e);
res.status(500).json({ ok: false, error: "Error interno del servidor" });
} finally {
client.release();
}
});
app.post("/public/assignment/:token/reject", async (req, res) => {
try {
const { token } = req.params;
// Si lo rechaza, el reloj (setInterval) se encargará de buscar al siguiente técnico libre al instante
await pool.query("UPDATE assignment_pings SET status = 'rejected' WHERE token = $1 AND status = 'pending'", [token]);
res.json({ ok: true });
} catch (e) {
console.error("Error rechazando asignación:", e);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 📄 MOTOR DE PRESUPUESTOS Y CATÁLOGO DE ARTÍCULOS
// ==========================================
pool.query(`
CREATE TABLE IF NOT EXISTS articles (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS budgets (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
client_phone TEXT,
client_name TEXT,
client_address TEXT,
items JSONB DEFAULT '[]',
subtotal DECIMAL(10,2) DEFAULT 0.00,
tax DECIMAL(10,2) DEFAULT 0.00,
total DECIMAL(10,2) DEFAULT 0.00,
status TEXT DEFAULT 'pending', -- pending, accepted, rejected, converted
created_at TIMESTAMP DEFAULT NOW()
);
`).catch(console.error);
// --- CATÁLOGO DE ARTÍCULOS ---
app.get("/articles", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT * FROM articles WHERE owner_id=$1 ORDER BY name ASC", [req.user.accountId]);
res.json({ ok: true, articles: q.rows });
} catch(e) { res.status(500).json({ok: false}); }
});
app.post("/articles", authMiddleware, async (req, res) => {
try {
const { name, price } = req.body;
await pool.query("INSERT INTO articles (owner_id, name, price) VALUES ($1, $2, $3)", [req.user.accountId, name, price]);
res.json({ ok: true });
} catch(e) { res.status(500).json({ok: false}); }
});
app.put("/articles/:id", authMiddleware, async (req, res) => {
try {
const { name, price } = req.body;
await pool.query("UPDATE articles SET name=$1, price=$2 WHERE id=$3 AND owner_id=$4", [name, price, req.params.id, req.user.accountId]);
res.json({ ok: true });
} catch(e) { res.status(500).json({ok: false}); }
});
// --- PRESUPUESTOS ---
app.get("/budgets", authMiddleware, async (req, res) => {
try {
// MAGIA: Cruzamos los datos con scraped_services y service_statuses para saber
// exactamente en qué estado se encuentra el servicio que nació de este presupuesto.
const q = await pool.query(`
SELECT b.*,
s.status as linked_service_status,
st.name as linked_service_status_name
FROM budgets b
LEFT JOIN scraped_services s ON s.service_ref = 'PRE-' || b.id AND s.owner_id = b.owner_id
LEFT JOIN service_statuses st ON st.id::text = (s.raw_data->>'status_operativo')::text
WHERE b.owner_id=$1
ORDER BY b.created_at DESC
`, [req.user.accountId]);
res.json({ ok: true, budgets: q.rows });
} catch(e) { res.status(500).json({ok: false}); }
});
// Borrar Presupuesto
app.delete("/budgets/:id", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT status FROM budgets WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]);
if (q.rowCount === 0) return res.status(404).json({ok: false, error: "No encontrado"});
const status = q.rows[0].status;
// Comprobamos si el servicio vinculado está anulado
const sq = await pool.query(`
SELECT st.name as status_name
FROM scraped_services s
LEFT JOIN service_statuses st ON st.id::text = (s.raw_data->>'status_operativo')::text
WHERE s.service_ref = $1 AND s.owner_id = $2
`, [`PRE-${req.params.id}`, req.user.accountId]);
let isAnulado = false;
if (sq.rowCount > 0 && sq.rows[0].status_name && sq.rows[0].status_name.toLowerCase().includes('anulado')) {
isAnulado = true;
}
// REGLA DE NEGOCIO: No se puede borrar si está aceptado o convertido (Y NO ESTÁ ANULADO)
if ((status === 'accepted' || status === 'converted') && !isAnulado) {
return res.status(400).json({ok: false, error: "Para poder borrar un presupuesto, el servicio primero debe estar anulado."});
}
await pool.query("DELETE FROM budgets WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]);
res.json({ ok: true });
} catch(e) {
res.status(500).json({ok: false, error: "Error interno"});
}
});
// Convertir Presupuesto en Servicio Activo (CON SOPORTE RED INTERNA Y TRAZABILIDAD)
app.post("/budgets/:id/convert", authMiddleware, async (req, res) => {
try {
const { date, time, guild_id, assigned_to, use_automation } = req.body;
const bq = await pool.query("SELECT * FROM budgets WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]);
if (bq.rowCount === 0) return res.status(404).json({ok: false});
const budget = bq.rows[0];
// 🟢 Saber si ya estaba pagado antes de convertirlo
const isAlreadyPaid = budget.status === 'paid';
// 1. Montamos el Raw Data para el servicio
const rawData = {
"Nombre Cliente": budget.client_name,
"Teléfono": budget.client_phone,
"Dirección": budget.client_address,
"Compañía": "Particular",
"Descripción": "PRESUPUESTO ACEPTADO.\n" + budget.items.map(i => `${i.qty}x ${i.concept}`).join("\n"),
"guild_id": guild_id || null,
"assigned_to": assigned_to || null,
"scheduled_date": date || "",
"scheduled_time": time || "",
"is_paid": isAlreadyPaid // 🟢 Inyección limpia estructural
};
// 2. Insertamos en el Panel Operativo (Buzón) empezando en manual
const insertSvc = await pool.query(
"INSERT INTO scraped_services (owner_id, provider, service_ref, status, automation_status, assigned_to, raw_data) VALUES ($1, 'particular', $2, 'pending', 'manual', $3, $4) RETURNING id",
[
req.user.accountId,
`PRE-${budget.id}`,
assigned_to || null,
JSON.stringify(rawData)
]
);
const newServiceId = insertSvc.rows[0].id;
// 3. Marcamos presupuesto y enlazamos ficha financiera
const finalBudgetStatus = isAlreadyPaid ? 'paid' : 'converted';
await pool.query("UPDATE budgets SET status=$1 WHERE id=$2", [finalBudgetStatus, budget.id]);
await pool.query(
"INSERT INTO service_financials (scraped_id, amount, payment_method, is_paid) VALUES ($1, $2, $3, $4)",
[newServiceId, budget.total, isAlreadyPaid ? 'Tarjeta (Stripe)' : 'Pendiente', isAlreadyPaid]
);
// 4. Si pide automatización, la disparamos internamente llamando a nuestra propia IP (127.0.0.1)
if (use_automation && guild_id) {
const cpMatch = budget.client_address ? budget.client_address.match(/\b\d{5}\b/) : null;
const cp = cpMatch ? cpMatch[0] : "00000";
const port = process.env.PORT || 3000;
const autoUrl = `http://127.0.0.1:${port}/providers/automate/${newServiceId}`;
fetch(autoUrl, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'Authorization': req.headers.authorization
},
body: JSON.stringify({ guild_id, cp, useDelay: false })
}).catch(e => console.error("Error lanzando automatización interna:", e));
if (budget.client_phone) {
const msg = `✅ *PRESUPUESTO ACEPTADO*\n\nHola ${budget.client_name}, confirmamos la aceptación del presupuesto por un total de *${budget.total}€*.\n\nEn breve un técnico se pondrá en contacto contigo para agendar la cita. ¡Gracias por confiar en nosotros!`;
sendWhatsAppAuto(budget.client_phone, msg, `cliente_${req.user.accountId}`, false).catch(console.error);
}
}
else if (budget.client_phone && date && time) {
// Asignación directa a un técnico con fecha y hora
const [y, m, d] = date.split('-');
const msg = `✅ *PRESUPUESTO ACEPTADO*\n\nHola ${budget.client_name}, confirmamos la aceptación del presupuesto por un total de *${budget.total}€*.\n\nEl servicio ha sido agendado para el *${d}/${m}/${y} a las ${time}*. ¡Gracias por confiar en nosotros!`;
sendWhatsAppAuto(budget.client_phone, msg, `cliente_${req.user.accountId}`, false).catch(console.error);
if (assigned_to) {
const statusQ = await pool.query("SELECT id FROM service_statuses WHERE owner_id=$1 AND name ILIKE '%asignado%' LIMIT 1", [req.user.accountId]);
if (statusQ.rowCount > 0) {
rawData.status_operativo = statusQ.rows[0].id;
await pool.query("UPDATE scraped_services SET raw_data = $1 WHERE id = $2", [JSON.stringify(rawData), newServiceId]);
}
}
}
// --- INICIO TRAZABILIDAD ---
await registrarMovimiento(newServiceId, req.user.sub, "Aviso Creado", `Servicio generado a raíz del presupuesto aceptado #PRE-${budget.id}.`);
// --- FIN TRAZABILIDAD ---
res.json({ ok: true });
} catch(e) {
console.error("Error convirtiendo presupuesto:", e);
res.status(500).json({ok: false});
}
});
// ==========================================
// 💰 MOTOR FINANCIERO Y CONTABILIDAD (PREPARADO PARA ROBOT PDF)
// ==========================================
// Creamos la tabla financiera preparada para facturas y robots
pool.query(`
CREATE TABLE IF NOT EXISTS service_financials (
id SERIAL PRIMARY KEY,
scraped_id INT REFERENCES scraped_services(id) ON DELETE CASCADE UNIQUE,
amount DECIMAL(10,2) DEFAULT 0.00,
payment_method TEXT,
is_paid BOOLEAN DEFAULT false,
invoice_ref TEXT,
pdf_raw_data JSONB,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
`).catch(console.error);
// Obtener toda la contabilidad
app.get("/financials", authMiddleware, async (req, res) => {
try {
// 1. Truco de magia: Si hay servicios que no tienen ficha financiera, se la creamos automáticamente.
// Si tiene compañía asignada, le ponemos "Cobro Banco" por defecto. Si no, "Pendiente".
await pool.query(`
INSERT INTO service_financials (scraped_id, payment_method)
SELECT id,
CASE WHEN raw_data->>'Compañía' IS NOT NULL AND raw_data->>'Compañía' != '' AND raw_data->>'Compañía' != 'Particular'
THEN 'Cobro Banco'
ELSE 'Pendiente' END
FROM scraped_services
WHERE owner_id = $1 AND id NOT IN (SELECT scraped_id FROM service_financials)
`, [req.user.accountId]);
// 2. Devolvemos la lista cruzando las finanzas con los datos del servicio
const q = await pool.query(`
SELECT f.*, s.service_ref, s.raw_data, s.status
FROM service_financials f
JOIN scraped_services s ON f.scraped_id = s.id
WHERE s.owner_id = $1
ORDER BY f.updated_at DESC
`, [req.user.accountId]);
res.json({ ok: true, financials: q.rows });
} catch(e) {
console.error("Error financiero:", e);
res.status(500).json({ ok: false });
}
});
// Guardar un cobro/pago
app.put("/financials/:id", authMiddleware, async (req, res) => {
try {
const { amount, payment_method } = req.body;
const parsedAmount = parseFloat(amount) || 0;
// NUEVA REGLA: Si el método de pago es "Pendiente", NO está pagado,
// independientemente del importe que tenga apuntado (Ej: Presupuestos).
const isPaid = payment_method !== 'Pendiente';
await pool.query(`
UPDATE service_financials
SET amount = $1, payment_method = $2, is_paid = $3, updated_at = NOW()
WHERE scraped_id = $4
`, [parsedAmount, payment_method, isPaid, req.params.id]);
// LOG AUTOMÁTICO DE TRAZABILIDAD
const userQ = await pool.query("SELECT full_name FROM users WHERE id=$1", [req.user.sub]);
const userName = userQ.rows[0]?.full_name || "Sistema";
await pool.query(
"INSERT INTO scraped_service_logs (scraped_id, user_name, action, details) VALUES ($1, $2, $3, $4)",
[req.params.id, userName, "Cobro Actualizado", `Importe: ${parsedAmount}€ | Método: ${payment_method}`]
);
res.json({ ok: true, is_paid: isPaid });
} catch(e) {
console.error(e);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 📖 MOTOR DE TRAZABILIDAD (LOGS)
// ==========================================
// Creamos la tabla automáticamente si no existe
pool.query(`
CREATE TABLE IF NOT EXISTS scraped_service_logs (
id SERIAL PRIMARY KEY,
scraped_id INT REFERENCES scraped_services(id) ON DELETE CASCADE,
user_name TEXT,
action TEXT NOT NULL,
details TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
`).catch(console.error);
// Ruta para GUARDAR un evento en el log
app.post("/services/:id/log", authMiddleware, async (req, res) => {
try {
const { action, details } = req.body;
const serviceId = req.params.id;
// Verificamos propiedad antes de insertar
const check = await pool.query("SELECT id FROM scraped_services WHERE id=$1 AND owner_id=$2", [serviceId, req.user.accountId]);
if (check.rowCount === 0) return res.status(403).json({ ok: false, error: "No autorizado" });
const userQ = await pool.query("SELECT full_name FROM users WHERE id=$1", [req.user.sub]);
const userName = userQ.rows[0]?.full_name || "Sistema";
await pool.query(
"INSERT INTO scraped_service_logs (scraped_id, user_name, action, details) VALUES ($1, $2, $3, $4)",
[serviceId, userName, action, details || ""]
);
res.json({ ok: true });
} catch(e) {
res.status(500).json({ ok: false });
}
});
// Ruta para LEER el historial de un servicio
app.get("/services/:id/logs", authMiddleware, async (req, res) => {
try {
// Cruce con la tabla principal para verificar el dueño (owner_id)
const q = await pool.query(`
SELECT l.* FROM scraped_service_logs l
JOIN scraped_services s ON l.scraped_id = s.id
WHERE l.scraped_id = $1
AND s.owner_id = $2
ORDER BY l.created_at DESC
`, [req.params.id, req.user.accountId]);
res.json({ ok: true, logs: q.rows });
} catch(e) {
res.status(500).json({ ok: false });
}
});
// ==========================================
// 📄 MOTOR DE PRESUPUESTOS Y CATÁLOGO DE ARTÍCULOS
// ==========================================
pool.query(`
CREATE TABLE IF NOT EXISTS articles (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
price DECIMAL(10,2) DEFAULT 0.00,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS budgets (
id SERIAL PRIMARY KEY,
owner_id INT REFERENCES users(id) ON DELETE CASCADE,
client_phone TEXT,
client_name TEXT,
client_address TEXT,
items JSONB DEFAULT '[]',
subtotal DECIMAL(10,2) DEFAULT 0.00,
tax DECIMAL(10,2) DEFAULT 0.00,
total DECIMAL(10,2) DEFAULT 0.00,
status TEXT DEFAULT 'pending',
created_at TIMESTAMP DEFAULT NOW()
);
`).catch(console.error);
// --- CATÁLOGO DE ARTÍCULOS ---
app.get("/articles", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT * FROM articles WHERE owner_id=$1 ORDER BY name ASC", [req.user.accountId]);
res.json({ ok: true, articles: q.rows });
} catch(e) { res.status(500).json({ok: false}); }
});
app.post("/articles", authMiddleware, async (req, res) => {
try {
const { name, price } = req.body;
await pool.query("INSERT INTO articles (owner_id, name, price) VALUES ($1, $2, $3)", [req.user.accountId, name, price]);
res.json({ ok: true });
} catch(e) { res.status(500).json({ok: false}); }
});
app.put("/articles/:id", authMiddleware, async (req, res) => {
try {
const { name, price } = req.body;
await pool.query("UPDATE articles SET name=$1, price=$2 WHERE id=$3 AND owner_id=$4", [name, price, req.params.id, req.user.accountId]);
res.json({ ok: true });
} catch(e) { res.status(500).json({ok: false}); }
});
// --- PRESUPUESTOS ---
app.get("/budgets", authMiddleware, async (req, res) => {
try {
const q = await pool.query(`
SELECT b.*,
s.status as linked_service_status,
st.name as linked_service_status_name
FROM budgets b
LEFT JOIN scraped_services s ON s.service_ref = 'PRE-' || b.id AND s.owner_id = b.owner_id
LEFT JOIN service_statuses st ON st.id::text = (s.raw_data->>'status_operativo')::text
WHERE b.owner_id=$1
ORDER BY b.created_at DESC
`, [req.user.accountId]);
res.json({ ok: true, budgets: q.rows });
} catch(e) { res.status(500).json({ok: false}); }
});
app.post("/budgets", authMiddleware, async (req, res) => {
try {
const { client_phone, client_name, client_address, items, subtotal, tax, total } = req.body;
// 1. Guardamos el presupuesto en la BBDD
await pool.query(
"INSERT INTO budgets (owner_id, client_phone, client_name, client_address, items, subtotal, tax, total) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)",
[req.user.accountId, client_phone, client_name, client_address, JSON.stringify(items), subtotal, tax, total]
);
// Respondemos a la App rápido para que no se quede pensando
res.json({ ok: true });
// 2. TAREAS EN SEGUNDO PLANO (Envío del WhatsApp)
(async () => {
try {
let rawPhone = client_phone || "";
let cleanPhoneToMatch = extractValidPhone(rawPhone);
if (cleanPhoneToMatch.length >= 9) {
const finalPhoneToSend = "34" + cleanPhoneToMatch;
let token = null;
// Buscamos si el cliente ya tiene un token
const clientQ = await pool.query("SELECT portal_token FROM clients WHERE phone LIKE $1 AND owner_id=$2 LIMIT 1", [`%${cleanPhoneToMatch}%`, req.user.accountId]);
if (clientQ.rowCount > 0) {
token = clientQ.rows[0].portal_token;
} else {
// Si es un cliente totalmente nuevo, lo registramos y le creamos un token seguro
const newToken = crypto.randomBytes(32).toString('hex');
const insertC = await pool.query(
"INSERT INTO clients (owner_id, full_name, phone, addresses, portal_token) VALUES ($1, $2, $3, $4, $5) RETURNING portal_token",
[req.user.accountId, client_name || "Cliente", finalPhoneToSend, JSON.stringify([client_address || ""]), newToken]
);
token = insertC.rows[0].portal_token;
}
if (token) {
const linkMagico = `https://portal.integrarepara.es/?token=${token}`;
const msg = `👋 Hola ${client_name || "Cliente"},\n\nAcabamos de generar un *nuevo presupuesto* para tu reparación por un total de *${total}€*.\n\n📄 Puedes revisarlo al detalle y descargarlo en PDF directamente desde tu portal privado:\n🔗 ${linkMagico}`;
// MODO PRUEBAS: Si quieres probarlo a tu número primero, cambia MODO_PRUEBAS a true
const MODO_PRUEBAS = false;
const MI_TELEFONO = "34667248132";
if (MODO_PRUEBAS) {
console.log(`🛡️ [MODO PRUEBAS] Enviando WA presupuesto a (${MI_TELEFONO}) en lugar de al cliente`);
await sendWhatsAppAuto(MI_TELEFONO, `*(SIMULACIÓN PRESUPUESTO PARA: ${finalPhoneToSend})*\n\n${msg}`, `cliente_${req.user.accountId}`, false);
} else {
console.log(`✅ Enviando WA de nuevo presupuesto al cliente: ${finalPhoneToSend}`);
await sendWhatsAppAuto(finalPhoneToSend, msg, `cliente_${req.user.accountId}`, false);
}
}
}
} catch(errWA) {
console.error("❌ Error enviando WA de presupuesto:", errWA);
}
})();
} catch(e) {
res.status(500).json({ok: false});
}
});
app.patch("/budgets/:id/status", authMiddleware, async (req, res) => {
try {
await pool.query("UPDATE budgets SET status=$1 WHERE id=$2 AND owner_id=$3", [req.body.status, req.params.id, req.user.accountId]);
res.json({ ok: true });
} catch(e) { res.status(500).json({ok: false}); }
});
app.delete("/budgets/:id", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT status FROM budgets WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]);
if (q.rowCount === 0) return res.status(404).json({ok: false, error: "No encontrado"});
const status = q.rows[0].status;
const sq = await pool.query(`
SELECT st.name as status_name
FROM scraped_services s
LEFT JOIN service_statuses st ON st.id::text = (s.raw_data->>'status_operativo')::text
WHERE s.service_ref = $1 AND s.owner_id = $2
`, [`PRE-${req.params.id}`, req.user.accountId]);
let isAnulado = false;
if (sq.rowCount > 0 && sq.rows[0].status_name && sq.rows[0].status_name.toLowerCase().includes('anulado')) {
isAnulado = true;
}
if ((status === 'accepted' || status === 'converted') && !isAnulado) {
return res.status(400).json({ok: false, error: "Para poder borrar un presupuesto, el servicio primero debe estar anulado."});
}
await pool.query("DELETE FROM budgets WHERE id=$1 AND owner_id=$2", [req.params.id, req.user.accountId]);
res.json({ ok: true });
} catch(e) {
res.status(500).json({ok: false, error: "Error interno"});
}
});
// ==========================================
// 🤖 API COLA DEL ROBOT (REEMPLAZO FIREBASE)
// ==========================================
// 1. Enviar una orden al Robot
app.post("/robot/queue", authMiddleware, async (req, res) => {
try {
// AÑADIDO: Recogemos el provider y el appointment_time
const { provider, service_number, new_status, appointment_date, appointment_time, observation, inform_client } = req.body;
// Si no mandan provider (ej: App vieja), asumimos homeserve por retrocompatibilidad
const finalProvider = provider || 'homeserve';
// IMPORTANTE: Nos aseguramos de que la tabla tiene la columna de la hora para Multiasistencia
await pool.query(`ALTER TABLE robot_queue ADD COLUMN IF NOT EXISTS appointment_time TEXT;`).catch(() => {});
const q = await pool.query(`
INSERT INTO robot_queue (owner_id, provider, service_number, new_status, appointment_date, appointment_time, observation, inform_client)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8) RETURNING id
`, [
req.user.accountId,
finalProvider,
service_number,
new_status,
appointment_date || "",
appointment_time || "", // Nueva variable de hora
observation || "",
inform_client || false
]);
res.json({ ok: true, jobId: q.rows[0].id });
} catch (e) {
console.error("Error guardando en cola:", e);
res.status(500).json({ ok: false, error: e.message });
}
});
// 3. Obtener resumen de mensajes nuevos para el operario (Globo de Notificación)
app.get("/worker/notifications", authMiddleware, async (req, res) => {
try {
// Buscamos expedientes donde la fecha del último mensaje de ADMIN sea más reciente que la última lectura del operario
const q = await pool.query(`
SELECT DISTINCT s.id
FROM scraped_services s
JOIN service_communications c ON s.id = c.scraped_id
WHERE s.assigned_to = $1
AND c.sender_role IN ('admin', 'superadmin')
AND c.is_internal = FALSE
AND c.created_at > COALESCE(s.last_chat_read_worker, '2000-01-01')
`, [req.user.sub]);
res.json({ ok: true, unreadCount: q.rowCount, serviceIds: q.rows.map(r => r.id) });
} catch (e) {
console.error("Error notificaciones:", e);
res.status(500).json({ ok: false });
}
});
// 4. Marcar chat como leído por el operario
app.post("/services/:id/chat/read", authMiddleware, async (req, res) => {
try {
await pool.query(
"UPDATE scraped_services SET last_chat_read_worker = NOW() WHERE id = $1 AND assigned_to = $2",
[req.params.id, req.user.sub]
);
res.json({ ok: true });
} catch (e) {
res.status(500).json({ ok: false });
}
});
// ==========================================
// 💬 CHAT Y COMUNICACIÓN INTERNA (TIPO iTRAMIT)
// ==========================================
app.get("/services/:id/chat", authMiddleware, async (req, res) => {
try {
const { id } = req.params;
const isOperario = req.user.role === 'operario';
let query = `
SELECT id, sender_id, sender_name, sender_role, message, is_internal, created_at
FROM service_communications
WHERE scraped_id = $1 AND owner_id = $2
`;
if (isOperario) query += ` AND is_internal = FALSE`;
query += ` ORDER BY created_at ASC`;
const q = await pool.query(query, [id, req.user.accountId]);
res.json({ ok: true, messages: q.rows });
} catch (e) {
console.error("Error cargando chat:", e);
res.status(500).json({ ok: false });
}
});
// 🤖 WEBHOOK CON ESCUDO HUMANO, MEMORIA Y GESTIÓN DE URGENCIAS
app.post("/webhook/evolution", async (req, res) => {
try {
const data = req.body;
if (data.event !== "messages.upsert") {
return res.sendStatus(200);
}
const remoteJid = data?.data?.key?.remoteJid || "";
const fromMe = !!data?.data?.key?.fromMe;
const instanceName = data?.instance || "";
const mensajeTexto = (
data?.data?.message?.conversation ||
data?.data?.message?.extendedTextMessage?.text ||
""
).trim();
if (!mensajeTexto || !remoteJid || !instanceName) {
return res.sendStatus(200);
}
// Ignorar grupos, estados y cosas raras
if (!/@s\.whatsapp\.net$/i.test(remoteJid)) {
return res.sendStatus(200);
}
// Respondemos rápido a Evolution
res.sendStatus(200);
if (!/^cliente_\d+$/.test(instanceName)) {
console.log(`⚠️ [WEBHOOK IA] Instancia ignorada: ${instanceName}`);
return;
}
const ownerId = parseInt(instanceName.replace("cliente_", ""), 10);
if (!ownerId) return;
const telefonoCliente = remoteJid.split("@")[0];
const cleanPhone = telefonoCliente.replace(/\D/g, "").slice(-9);
if (!cleanPhone || cleanPhone.length < 9) return;
// 🔍 BUSCAMOS EL EXPEDIENTE ACTIVO MÁS RECIENTE POR TELÉFONO REAL
const svcQ = await pool.query(`
SELECT
s.id,
s.service_ref,
s.assigned_to,
s.is_urgent,
u.full_name as worker_name,
st.name as status_name,
COALESCE(s.raw_data->>'scheduled_date', '') as cita,
COALESCE(s.raw_data->>'scheduled_time', '') as hora_cita,
COALESCE(s.raw_data->>'Población', s.raw_data->>'POBLACION-PROVINCIA', '') as poblacion,
COALESCE(s.raw_data->>'appointment_status', '') as appointment_status,
COALESCE(s.raw_data->>'requested_date', '') as cita_pendiente_fecha,
COALESCE(s.raw_data->>'requested_time', '') as cita_pendiente_hora,
COALESCE(s.raw_data->>'Compañía', s.raw_data->>'COMPAÑIA', '') as compania,
COALESCE(s.raw_data->>'Descripción', s.raw_data->>'DESCRIPCION', '') as averia,
CASE
WHEN LOWER(COALESCE(s.raw_data->>'ia_paused', 'false')) IN ('true', 't', '1') THEN true
ELSE false
END as ia_paused
FROM scraped_services s
LEFT JOIN users u ON s.assigned_to = u.id
LEFT JOIN service_statuses st ON (s.raw_data->>'status_operativo')::text = st.id::text
WHERE s.owner_id = $1
AND s.status != 'archived'
AND RIGHT(
REGEXP_REPLACE(
COALESCE(
s.raw_data->>'Teléfono',
s.raw_data->>'TELEFONO',
s.raw_data->>'TELEFONOS',
''
),
'\\D',
'',
'g'
),
9
) = $2
AND (
st.name IS NULL OR (
st.name NOT ILIKE '%finalizado%' AND
st.name NOT ILIKE '%anulado%' AND
st.name NOT ILIKE '%desasignado%'
)
)
ORDER BY
(SELECT MAX(sc.created_at) FROM service_communications sc WHERE sc.scraped_id = s.id) DESC NULLS LAST,
s.created_at DESC
LIMIT 1
`, [ownerId, cleanPhone]);
if (svcQ.rowCount === 0) {
console.log(`⚠️ [WEBHOOK IA] No se encontró expediente activo para ${cleanPhone}`);
return;
}
const service = svcQ.rows[0];
// ==========================================
// 🧑‍🔧 MENSAJES TUYOS / ADMIN / OFICINA
// ==========================================
if (fromMe) {
const msgCmd = mensajeTexto.trim();
// 🔴 PAUSAR IA
if (msgCmd.includes("🔴")) {
try {
await pool.query(`
UPDATE scraped_services
SET raw_data = COALESCE(raw_data, '{}'::jsonb) || '{"ia_paused": true}'::jsonb
WHERE id = $1
`, [service.id]);
await pool.query(`
INSERT INTO service_communications
(scraped_id, owner_id, sender_name, sender_role, message, is_internal)
VALUES ($1, $2, 'Sistema', 'system', $3, true)
`, [service.id, ownerId, "🔴 IA Pausada manualmente con Emoji."]);
console.log(`🔴 [IA PAUSADA] ${service.service_ref}`);
} catch (err) {
console.error("Error pausando IA:", err);
}
return;
}
// 🟢 REACTIVAR IA
if (msgCmd.includes("🟢")) {
try {
await pool.query(`
UPDATE scraped_services
SET raw_data = raw_data - 'ia_paused'
WHERE id = $1
`, [service.id]);
await pool.query(`
INSERT INTO service_communications
(scraped_id, owner_id, sender_name, sender_role, message, is_internal)
VALUES ($1, $2, 'Sistema', 'system', $3, true)
`, [service.id, ownerId, "🟢 IA Reactivada manualmente con Emoji."]);
console.log(`🟢 [IA ACTIVADA] ${service.service_ref}`);
} catch (err) {
console.error("Error activando IA:", err);
}
return;
}
// Guardamos mensajes humanos salientes
try {
await pool.query(`
INSERT INTO service_communications
(scraped_id, owner_id, sender_name, sender_role, message, is_internal)
VALUES ($1, $2, 'Técnico (WhatsApp)', 'operario', $3, false)
`, [service.id, ownerId, mensajeTexto]);
} catch (err) {
console.error("Error guardando mensaje humano saliente:", err);
}
return;
}
// ==========================================
// 👤 MENSAJE ENTRANTE DEL CLIENTE
// ==========================================
try {
await pool.query(`
INSERT INTO service_communications
(scraped_id, owner_id, sender_name, sender_role, message, is_internal)
VALUES ($1, $2, 'Cliente', 'user', $3, false)
`, [service.id, ownerId, mensajeTexto]);
} catch (err) {
console.error("Error guardando mensaje del cliente:", err);
}
if (candadosIA.has(service.id)) return;
candadosIA.add(service.id);
try {
// 🛑 Si está pausada, no contestamos
if (service.ia_paused === true || service.ia_paused === 'true') {
console.log(`🤫 [IA MUTEADA] ${service.service_ref}`);
return;
}
// 🛡️ Escudo humano: si alguien habló hace menos de 120 min, la IA se calla
const checkHumanQ = await pool.query(`
SELECT sender_role, created_at
FROM service_communications
WHERE scraped_id = $1
AND sender_role IN ('admin', 'superadmin', 'operario')
ORDER BY created_at DESC
LIMIT 1
`, [service.id]);
if (checkHumanQ.rowCount > 0) {
const lastMsg = checkHumanQ.rows[0];
const diffMinutos = (Date.now() - new Date(lastMsg.created_at).getTime()) / (1000 * 60);
if (diffMinutos < 0) {
console.log(`🛡️ [ESCUDO IA] Silenciada porque un humano habló hace ${Math.round(diffMinutos)} minutos.`);
return;
}
}
// 🧠 LLAMADA A LA IA
const respuestaIA = await procesarConIA(ownerId, mensajeTexto, {
dbId: service.id,
ref: service.service_ref,
estado: service.status_name || "En proceso",
operario: service.worker_name,
worker_id: service.assigned_to,
cita: service.cita,
hora_cita: service.hora_cita,
poblacion: service.poblacion || "",
is_urgent: service.is_urgent,
appointment_status: service.appointment_status,
cita_pendiente_fecha: service.cita_pendiente_fecha,
cita_pendiente_hora: service.cita_pendiente_hora,
compania: service.compania,
averia: service.averia
});
if (!respuestaIA) return;
const matchPropuesta = respuestaIA.match(/\[PROPUESTA:\s*(\d{4}-\d{2}-\d{2})\s+(\d{2}:\d{2})\]/i);
let textoLimpio = respuestaIA
.replace(/\[PROPUESTA:.*?\]/gi, "")
.replace(/\n{3,}/g, "\n\n")
.trim();
if (matchPropuesta && service.assigned_to) {
const fechaSugerida = matchPropuesta[1];
const horaSugerida = matchPropuesta[2];
const disponibilidad = await comprobarDisponibilidad(
ownerId,
service.assigned_to,
fechaSugerida,
horaSugerida,
60,
service.id
);
if (disponibilidad.choca) {
console.log(`⛔ [DOBLE-BOOKING EVITADO] ${service.service_ref} chocaba con ${disponibilidad.ref}`);
textoLimpio = "Uy, justo ese hueco ya no lo veo libre en el sistema. Dime otra hora y lo reviso.";
} else {
await pool.query(`
UPDATE scraped_services
SET raw_data = raw_data || jsonb_build_object(
'requested_date', $1::text,
'requested_time', $2::text,
'appointment_status', 'pending'
)
WHERE id = $3
`, [fechaSugerida, horaSugerida, service.id]);
}
}
if (!textoLimpio) return;
await sendWhatsAppAuto(telefonoCliente, textoLimpio, instanceName, true);
await pool.query(`
INSERT INTO service_communications
(scraped_id, owner_id, sender_name, sender_role, message, is_internal)
VALUES ($1, $2, 'Asistente IA', 'ia', $3, false)
`, [service.id, ownerId, textoLimpio]);
} finally {
candadosIA.delete(service.id);
}
} catch (e) {
console.error("❌ [WEBHOOK ERROR]:", e);
if (!res.headersSent) return res.sendStatus(200);
}
});
// ==========================================
// ✍️ 5. RUTA PARA ACEPTAR/RECHAZAR Y FIRMAR PRESUPUESTOS
// ==========================================
app.post("/public/portal/:token/budget/:id/respond", async (req, res) => {
try {
const { token, id } = req.params;
const { action, signature } = req.body;
const clientQ = await pool.query("SELECT owner_id, full_name, phone FROM clients WHERE portal_token = $1", [token]);
if (clientQ.rowCount === 0) return res.status(404).json({ ok: false });
const ownerId = clientQ.rows[0].owner_id;
await pool.query(`ALTER TABLE budgets ADD COLUMN IF NOT EXISTS signature TEXT;`).catch(()=>{});
const newStatus = action === 'accept' ? 'accepted' : 'rejected';
await pool.query(
"UPDATE budgets SET status = $1, signature = $2 WHERE id = $3 AND owner_id = $4",
[newStatus, signature || null, id, ownerId]
);
const ownerData = await pool.query("SELECT phone FROM users WHERE id = $1", [ownerId]);
const bQ = await pool.query("SELECT client_name, total FROM budgets WHERE id = $1", [id]);
if (ownerData.rowCount > 0 && bQ.rowCount > 0) {
const adminPhone = ownerData.rows[0].phone;
const bInfo = bQ.rows[0];
const msgWa = action === 'accept'
? `🟢 *PRESUPUESTO ACEPTADO*\nEl cliente ${bInfo.client_name} ha ACEPTADO y firmado el presupuesto PRE-${id} por ${bInfo.total}€.`
: `🔴 *PRESUPUESTO RECHAZADO*\nEl cliente ${bInfo.client_name} ha RECHAZADO el presupuesto PRE-${id}.`;
sendWhatsAppAuto(adminPhone, msgWa, `cliente_${ownerId}`, false).catch(console.error);
}
res.json({ ok: true });
} catch (e) {
console.error("Error firmando presupuesto:", e);
res.status(500).json({ ok: false });
}
});
// ==========================================
// 💳 6. MOTOR DE PAGOS (STRIPE) PARA PRESUPUESTOS Y FACTURAS
// ==========================================
app.post("/public/portal/:token/budget/:id/checkout", async (req, res) => {
try {
const { token, id } = req.params;
// 1. Identificar al cliente y su empresa (dueño)
const clientQ = await pool.query("SELECT owner_id, full_name, phone FROM clients WHERE portal_token = $1", [token]);
if (clientQ.rowCount === 0) return res.status(404).json({ ok: false, error: "Token inválido" });
const ownerId = clientQ.rows[0].owner_id;
// 2. Extraer datos del presupuesto
const bQ = await pool.query("SELECT * FROM budgets WHERE id = $1 AND owner_id = $2", [id, ownerId]);
if (bQ.rowCount === 0) return res.status(404).json({ ok: false, error: "Presupuesto no encontrado" });
const budget = bQ.rows[0];
// 3. Extraer las claves secretas de Stripe de ESA EMPRESA EN CONCRETO (Modo SaaS)
const ownerQ = await pool.query("SELECT billing_settings FROM users WHERE id = $1", [ownerId]);
const billingSettings = ownerQ.rows[0]?.billing_settings || {};
if (!billingSettings.stripe_enabled || !billingSettings.stripe_sk) {
return res.status(400).json({ ok: false, error: "Los pagos con tarjeta no están habilitados para esta empresa." });
}
// 4. Iniciar Stripe de forma dinámica con la llave del cliente
const stripe = new Stripe(billingSettings.stripe_sk, { apiVersion: "2023-10-16" });
// 5. Crear la ventana mágica de pago (Checkout Session)
const session = await stripe.checkout.sessions.create({
payment_method_types: ['card'],
customer_email: budget.client_email || undefined, // Opcional, si tienes su email
line_items: [
{
price_data: {
currency: 'eur',
product_data: {
name: `Presupuesto #PRE-${budget.id}`,
description: `Servicios de asistencia técnica para ${budget.client_name}`,
},
unit_amount: Math.round(budget.total * 100), // Stripe cobra en céntimos (84.70€ = 8470)
},
quantity: 1,
},
],
mode: 'payment',
// Enviaremos metadatos para saber exactamente qué han pagado cuando Stripe nos avise de vuelta
metadata: {
budget_id: budget.id,
owner_id: ownerId,
client_phone: budget.client_phone
},
// Redirecciones tras el pago
success_url: `https://portal.integrarepara.es/pago_exito.html?budget_id=${budget.id}`,
cancel_url: `https://portal.integrarepara.es/?token=${token}`,
});
// 6. Devolvemos el link seguro de Stripe al frontend para que redirija al cliente
res.json({ ok: true, checkout_url: session.url });
} catch (e) {
console.error("❌ Error creando sesión de Stripe:", e);
res.status(500).json({ ok: false, error: e.message });
}
});
// ==========================================
// 🛡️ MÓDULO SAAS: PLANES DE PROTECCIÓN
// ==========================================
// 1. DASHBOARD: Estadísticas
app.get("/protection/dashboard", authMiddleware, async (req, res) => {
const cid = req.user.accountId;
try {
const [statsQ, actQ, topQ] = await Promise.all([
pool.query(`
SELECT
(SELECT COUNT(*) FROM protection_subscriptions WHERE company_id = $1) as total,
(SELECT COALESCE(SUM(p.price), 0) FROM protection_subscriptions s JOIN protection_plans p ON s.plan_id = p.id WHERE s.company_id = $1 AND s.status = 'activo') as mrr,
(SELECT COALESCE(SUM(urgencies_used), 0) FROM protection_subscriptions WHERE company_id = $1) as urgUsed,
(SELECT COALESCE(SUM(bricos_used), 0) FROM protection_subscriptions WHERE company_id = $1) as briUsed,
(SELECT COUNT(*) FROM protection_subscriptions WHERE company_id = $1 AND payment_status = 'impagado') as unpaid
`, [cid]),
pool.query("SELECT * FROM protection_activity WHERE company_id = $1 ORDER BY created_at DESC LIMIT 10", [cid]),
pool.query(`
SELECT p.*, COUNT(s.id) as users
FROM protection_plans p
LEFT JOIN protection_subscriptions s ON p.id = s.plan_id
WHERE p.company_id = $1
GROUP BY p.id
`, [cid])
]);
res.json({ ok: true, stats: statsQ.rows[0], activity: actQ.rows, topPlans: topQ.rows });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// 2. PLANES
app.get("/protection/plans", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT * FROM protection_plans WHERE company_id = $1 ORDER BY created_at DESC", [req.user.accountId]);
res.json({ ok: true, plans: q.rows });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
app.post("/protection/plans", authMiddleware, async (req, res) => {
const cid = req.user.accountId;
const { name, type, price, renewal, urgencies, bricos, coverages } = req.body;
try {
await pool.query(
"INSERT INTO protection_plans (company_id, name, type, price, renewal_price, urgencies_limit, bricos_limit, coverages) VALUES ($1,$2,$3,$4,$5,$6,$7,$8)",
[cid, name, type, price, renewal, urgencies, bricos, coverages]
);
await pool.query("INSERT INTO protection_activity (company_id, type, description) VALUES ($1, 'alta', $2)", [cid, `Nuevo plan creado: ${name}`]);
res.json({ ok: true });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// 3. SUSCRIPTORES
app.get("/protection/subscribers", authMiddleware, async (req, res) => {
try {
const q = await pool.query(`
SELECT s.*, p.name as plan_name, p.price as plan_price, p.urgencies_limit, p.bricos_limit
FROM protection_subscriptions s
LEFT JOIN protection_plans p ON s.plan_id = p.id
WHERE s.company_id = $1 ORDER BY s.created_at DESC
`, [req.user.accountId]);
res.json({ ok: true, subscribers: q.rows });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
app.post("/protection/subscribers", authMiddleware, async (req, res) => {
const cid = req.user.accountId;
const { plan_id, name, dni, phone, payment_status, status, renewal_date, bricos_used, urgencies_used } = req.body;
try {
const d = renewal_date ? renewal_date : null;
await pool.query(
`INSERT INTO protection_subscriptions
(company_id, plan_id, client_name, client_dni, client_phone, payment_status, status, renewal_date, bricos_used, urgencies_used)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,
[cid, plan_id, name, dni, phone, payment_status, status, d, bricos_used, urgencies_used]
);
await pool.query("INSERT INTO protection_activity (company_id, type, description) VALUES ($1, 'alta', $2)", [cid, `Suscripción creada: ${name}`]);
res.json({ ok: true });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
app.put("/protection/subscribers/:id/toggle", authMiddleware, async (req, res) => {
const cid = req.user.accountId;
const { id } = req.params;
const { field, value } = req.body;
try {
if (!['payment_status', 'status'].includes(field)) throw new Error("Invalid field");
await pool.query(`UPDATE protection_subscriptions SET ${field} = $1 WHERE id = $2 AND company_id = $3`, [value, id, cid]);
await pool.query("INSERT INTO protection_activity (company_id, type, description) VALUES ($1, 'cobro', $2)", [cid, `Cambiado a ${value} en ID: ${id}`]);
res.json({ ok: true });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// 4. CONFIGURACIÓN
app.get("/protection/config", authMiddleware, async (req, res) => {
try {
const q = await pool.query("SELECT * FROM protection_config WHERE company_id = $1", [req.user.accountId]);
res.json({ ok: true, config: q.rows[0] || {} });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
app.post("/protection/config", authMiddleware, async (req, res) => {
const cid = req.user.accountId;
const { name, email, phone, auto_renew, pre_notice, billing_method, contract_text } = req.body;
try {
await pool.query(`
INSERT INTO protection_config (company_id, name, email, phone, auto_renew, pre_notice, billing_method, contract_text)
VALUES ($1,$2,$3,$4,$5,$6,$7,$8) ON CONFLICT (company_id) DO UPDATE SET
name=EXCLUDED.name, email=EXCLUDED.email, phone=EXCLUDED.phone, auto_renew=EXCLUDED.auto_renew,
pre_notice=EXCLUDED.pre_notice, billing_method=EXCLUDED.billing_method, contract_text=EXCLUDED.contract_text
`, [cid, name, email, phone, auto_renew, pre_notice, billing_method, contract_text]);
res.json({ ok: true });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// ==========================================
// 🛡️ RUTAS PÚBLICAS: CONTRATACIÓN DE PLANES
// ==========================================
// 1. Obtener la información pública de la empresa, planes y comprobar si YA tiene seguro
app.get("/public/portal/:token/protection", async (req, res) => {
try {
const { token } = req.params;
// Identificar al cliente y sacar el ID de la empresa dueña
const clientQ = await pool.query("SELECT id, full_name, phone, owner_id FROM clients WHERE portal_token = $1", [token]);
if (clientQ.rowCount === 0) return res.status(404).json({ ok: false, error: "Token inválido" });
const client = clientQ.rows[0];
const ownerId = client.owner_id;
// Comprobar si el cliente YA tiene un plan activo (Solo si está pagado y activo)
let cleanPhone = String(client.phone || "").replace(/[^0-9]/g, "");
if (cleanPhone.length > 9) cleanPhone = cleanPhone.slice(-9);
const subQ = await pool.query(`
SELECT s.status, p.name as plan_name
FROM protection_subscriptions s
JOIN protection_plans p ON s.plan_id = p.id
WHERE s.company_id = $1 AND s.client_phone LIKE $2 AND s.status = 'activo' AND s.payment_status = 'pagado'
ORDER BY s.created_at DESC LIMIT 1
`, [ownerId, `%${cleanPhone}%`]);
const hasActivePlan = subQ.rowCount > 0;
const activePlanDetails = hasActivePlan ? subQ.rows[0] : null;
// Extraer datos de la empresa, configuración y planes
const [companyQ, configQ, plansQ] = await Promise.all([
pool.query("SELECT full_name FROM users WHERE id = $1", [ownerId]),
pool.query("SELECT contract_text FROM protection_config WHERE company_id = $1", [ownerId]),
pool.query("SELECT * FROM protection_plans WHERE company_id = $1 ORDER BY price DESC", [ownerId])
]);
res.json({
ok: true,
client: { name: client.full_name },
company: { name: companyQ.rows[0]?.full_name || "Empresa" },
config: configQ.rows[0] || {},
plans: plansQ.rows,
hasActivePlan: hasActivePlan,
activePlanDetails: activePlanDetails
});
} catch (e) {
console.error("Error cargando portal protección:", e);
res.status(500).json({ ok: false, error: "Error interno del servidor" });
}
});
app.post("/public/portal/:token/protection/subscribe", async (req, res) => {
try {
const { token } = req.params;
const { plan_id, signature, pdf_document, dni } = req.body;
const clientQ = await pool.query(
"SELECT * FROM clients WHERE portal_token = $1",
[token]
);
if (clientQ.rowCount === 0) {
return res.status(404).json({ ok: false, error: "Token inválido" });
}
const client = clientQ.rows[0];
const ownerId = client.owner_id;
const planQ = await pool.query(
"SELECT * FROM protection_plans WHERE id = $1 AND company_id = $2",
[plan_id, ownerId]
);
if (planQ.rowCount === 0) {
return res.status(404).json({ ok: false, error: "El plan seleccionado no existe" });
}
const plan = planQ.rows[0];
const ownerConfigQ = await pool.query(
"SELECT billing_settings FROM users WHERE id = $1",
[ownerId]
);
let billingSettings = ownerConfigQ.rows[0]?.billing_settings || {};
if (typeof billingSettings === "string") {
try { billingSettings = JSON.parse(billingSettings); } catch (_) { billingSettings = {}; }
}
if (!billingSettings.stripe_enabled || !billingSettings.stripe_sk) {
return res.status(400).json({ ok: false, error: "La empresa no tiene habilitados los pagos con tarjeta." });
}
const stripe = new Stripe(billingSettings.stripe_sk, { apiVersion: "2023-10-16" });
const interval =
String(plan.billing_interval || "").toLowerCase() === "year" ||
String(plan.type || "").toLowerCase().includes("anual")
? "year"
: "month";
const intervalCount = Number(plan.billing_interval_count || 1);
const subInsert = await pool.query(`
INSERT INTO protection_subscriptions (
company_id,
plan_id,
client_name,
client_dni,
client_phone,
payment_status,
status,
contract_pdf_url
)
VALUES ($1, $2, $3, $4, $5, 'impagado', 'pendiente_pago', $6)
RETURNING id
`, [
ownerId,
plan.id,
client.full_name,
dni || null,
client.phone,
pdf_document || null
]);
const subscriptionId = subInsert.rows[0].id;
await pool.query(
"INSERT INTO protection_activity (company_id, type, description) VALUES ($1, 'alta', $2)",
[ownerId, `Suscripción iniciada vía Web: ${client.full_name} (${plan.name})`]
);
const session = await stripe.checkout.sessions.create({
payment_method_types: ["card"],
mode: "subscription",
line_items: [{
price_data: {
currency: "eur",
product_data: {
name: `Plan de Protección: ${plan.name}`,
description: `Suscripción ${plan.type || interval}`
},
unit_amount: Math.round(Number(plan.price || 0) * 100),
recurring: {
interval,
interval_count: intervalCount
}
},
quantity: 1
}],
metadata: {
subscription_id: String(subscriptionId),
owner_id: String(ownerId),
plan_id: String(plan.id),
type: "protection_plan"
},
customer_email: client.email || undefined,
success_url: `https://portal.integrarepara.es/pago_exito.html?sub_id=${subscriptionId}`,
cancel_url: `https://portal.integrarepara.es/plan-tranquilidad.html?token=${token}`,
});
await pool.query(`
UPDATE protection_subscriptions
SET stripe_session_id = $1,
stripe_price_id = $2,
updated_at = NOW()
WHERE id = $3
`, [
session.id,
null,
subscriptionId
]);
res.json({ ok: true, checkout_url: session.url });
} catch (e) {
console.error("Error al procesar firma de plan:", e);
res.status(500).json({ ok: false, error: "Error al generar la suscripción." });
}
});
// ==========================================
// 🕒 EL RELOJ DEL SISTEMA (Ejecutar cada minuto)
// ==========================================
setInterval(async () => {
const client = await pool.connect(); // <-- Conectamos de forma segura
try {
// --- 1. CADUCIDAD DE PINGS (Reasignación si el operario no contesta) ---
const expiredPings = await client.query(`
SELECT ap.id, ap.scraped_id, ap.user_id, s.owner_id, s.raw_data
FROM assignment_pings ap
JOIN scraped_services s ON ap.scraped_id = s.id
WHERE ap.status = 'pending'
AND EXTRACT(EPOCH FROM (ap.expires_at - CURRENT_TIMESTAMP)) <= 0
AND s.automation_status = 'in_progress'
`);
for (const ping of expiredPings.rows) {
await client.query("UPDATE assignment_pings SET status = 'expired' WHERE id = $1", [ping.id]);
// 🛑 FIX: Extraer Gremio y CP del raw_data para buscar al SIGUIENTE operario CORRECTO
const raw = ping.raw_data || {};
const guildId = raw.guild_id || null;
// Lógica para extraer CP exactamente igual que al inicio
const todoElTexto = JSON.stringify(raw).toLowerCase().normalize("NFD").replace(/[\u0300-\u036f]/g, "");
const cpMatch = todoElTexto.match(/\b\d{5}\b/);
const cpFinal = cpMatch ? cpMatch[0] : "00000";
// Si por algún motivo no hay gremio guardado, abortamos la bolsa para este servicio
if (!guildId) {
await client.query("UPDATE scraped_services SET automation_status = 'failed' WHERE id = $1", [ping.scraped_id]);
continue;
}
const nextWorkerQ = await client.query(`
SELECT u.id, u.phone, u.full_name
FROM users u
JOIN user_guilds ug ON u.id = ug.user_id
WHERE u.owner_id = $1 AND u.status = 'active'
AND ug.guild_id = $3 AND u.zones::jsonb @> $4::jsonb
AND u.id NOT IN (SELECT user_id FROM assignment_pings WHERE scraped_id = $2)
LIMIT 1
`, [ping.owner_id, ping.scraped_id, guildId, JSON.stringify([{ cps: cpFinal }])]);
if (nextWorkerQ.rowCount > 0) {
const nextW = nextWorkerQ.rows[0];
const newToken = crypto.randomBytes(16).toString('hex');
await client.query(`INSERT INTO assignment_pings (scraped_id, user_id, token, expires_at) VALUES ($1, $2, $3, CURRENT_TIMESTAMP + INTERVAL '5 minutes')`, [ping.scraped_id, nextW.id, newToken]);
// 🛑 FIX: URL corregida a web.integrarepara.es
const mensaje = `🛠️ *SERVICIO DISPONIBLE*\nEl anterior compañero no respondió. Es tu turno:\n🔗 https://web.integrarepara.es/aceptar.html?t=${newToken}`;
const instanceName = `cliente_${ping.owner_id}`;
sendWhatsAppAuto(nextW.phone, mensaje, instanceName).catch(console.error);
} else {
await client.query("UPDATE scraped_services SET automation_status = 'failed' WHERE id = $1", [ping.scraped_id]);
}
}
// --- 2. 🚨 NUEVO: CAZADOR DE URGENCIAS QUE "ENTRAN SOLAS" 🚨 ---
// Buscamos servicios que sean urgentes y que sigan atascados en 'manual' o 'pending'
const pendingUrgent = await client.query(`
SELECT s.id, s.owner_id, s.raw_data, s.provider, s.service_ref, pc.auto_dispatch
FROM scraped_services s
LEFT JOIN provider_credentials pc ON s.owner_id = pc.owner_id AND s.provider = pc.provider
WHERE s.is_urgent = true
AND s.automation_status IN ('manual', 'pending')
AND s.status != 'archived'
AND s.assigned_to IS NULL -- 👈 EL ESCUDO: Solo si NO tiene operario asignado
`);
for (const svc of pendingUrgent.rows) {
// Comprobamos si el interruptor de ese proveedor está encendido (1, '1', 't' o true)
const isAutoOn = svc.auto_dispatch === true || svc.auto_dispatch === 1 || svc.auto_dispatch === '1' || svc.auto_dispatch === 't';
// Sacamos el gremio desde el JSON interno
const finalGuildId = svc.raw_data && svc.raw_data.guild_id ? svc.raw_data.guild_id : null;
if (isAutoOn && finalGuildId) {
console.log(`⚡ [RELOJ] Cazador automático ha encontrado la urgencia #${svc.service_ref}. ¡Lanzando a bolsa!`);
// Extraemos el código postal si existe en el raw_data
const todoElTexto = JSON.stringify(svc.raw_data || {}).toLowerCase().normalize("NFD").replace(/[\u0300-\u036f]/g, "");
const cpMatch = todoElTexto.match(/\b\d{5}\b/);
const cpFinal = cpMatch ? cpMatch[0] : "00000";
// 🔥 Disparamos la bolsa y guardamos el resultado
const dispatchResult = await dispatchToBolsa(svc.id, finalGuildId, cpFinal, svc.owner_id, null);
// 🛑 ESCUDO ANTI-BUCLE: Si falla porque no hay operarios en ese CP,
// marcamos la urgencia como "failed" para que el reloj deje de intentarlo eternamente.
if (!dispatchResult.ok) {
console.log(`🛑 [ESCUDO] Abortando bucle para #${svc.service_ref}. Marcado como 'failed'.`);
await client.query("UPDATE scraped_services SET automation_status = 'failed' WHERE id = $1", [svc.id]);
}
}
}
} catch (e) {
console.error("Reloj:", e.message);
} finally {
client.release(); // <-- Liberamos la conexión SIEMPRE
}
}, 60000); // Se ejecuta en bucle cada 60.000 milisegundos (1 minuto)
const port = process.env.PORT || 3000;
autoUpdateDB().then(() => { app.listen(port, "0.0.0.0", () => console.log(`🚀 Server OK en puerto ${port}`)); });
Reference in New Issue View Git Blame Copy Permalink