Asistente SQL & Bases de Datos

SELECT * FROM leads
WHERE LOWER(email) LIKE '%@gmail.com%'
   OR LOWER(name) LIKE '%garcia%';

SELECT * FROM leads
WHERE
  LOWER(email) LIKE '%@gmail.com%'
  OR LOWER(name) LIKE '%garcia%';

SELECT
  id, email, name, company,
  status, assigned_to, score
FROM leads
WHERE
  search_vector @@ to_tsquery('gmail | garcia')
ORDER BY ts_rank(search_vector, to_tsquery('gmail | garcia')) DESC
LIMIT 50;

-- 1. Añadir columna tsvector (nullable al inicio)
ALTER TABLE leads ADD COLUMN search_vector tsvector;

-- 2. Rellenar en batches (sin bloquear la tabla)
UPDATE leads SET
  search_vector = to_tsvector('simple',
    COALESCE(name, '') || ' ' || COALESCE(email, '') || ' ' || COALESCE(company, '')
  );

-- 3. Índice GIN (no bloquea escrituras)
CREATE INDEX CONCURRENTLY idx_leads_search ON leads USING GIN(search_vector);

-- 4. Trigger para mantener actualizado el vector
CREATE OR REPLACE FUNCTION leads_search_update() RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('simple',
    COALESCE(NEW.name, '') || ' ' || COALESCE(NEW.email, '') || ' ' || COALESCE(NEW.company, ''));
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER leads_search_trigger
  BEFORE INSERT OR UPDATE ON leads
  FOR EACH ROW EXECUTE FUNCTION leads_search_update();

WITH weekly_leads AS (
  -- Leads asignados esta semana
  SELECT
    u.id AS user_id,
    u.full_name,
    COUNT(l.id)                                        AS total_leads,
    COUNT(l.id) FILTER (WHERE l.status = 'won')        AS won_leads,
    COUNT(l.id) FILTER (WHERE l.status IN ('won','lost')) AS closed_leads,
    MAX(l.updated_at) FILTER (WHERE l.status = 'won')  AS last_won_at
  FROM users u
  LEFT JOIN leads l ON l.assigned_to = u.id
    AND l.created_at >= date_trunc('week', NOW())
    AND l.created_at <  date_trunc('week', NOW()) + INTERVAL '7 days'
  WHERE u.active = TRUE AND u.role = 'sales_rep'
  GROUP BY u.id, u.full_name
),
activities_week AS (
  -- Actividades de la semana por usuario
  SELECT user_id, COUNT(*) AS activity_count
  FROM activities
  WHERE done_at >= date_trunc('week', NOW())
  GROUP BY user_id
),
last_won_lead AS (
  -- Nombre del último lead ganado por comercial
  SELECT DISTINCT ON (assigned_to)
    assigned_to, name AS last_won_name, company
  FROM leads
  WHERE status = 'won'
  ORDER BY assigned_to, updated_at DESC
)
SELECT
  wl.full_name                                             AS "Comercial",
  wl.total_leads                                           AS "Leads asignados",
  wl.won_leads                                             AS "Leads ganados",
  CASE WHEN wl.closed_leads > 0
       THEN ROUND(wl.won_leads::numeric / wl.closed_leads * 100, 1)
       ELSE 0 END                                           AS "Tasa conversión %",
  COALESCE(aw.activity_count, 0)                          AS "Actividades",
  COALESCE(lwl.last_won_name, '—')                       AS "Último lead ganado",
  RANK() OVER (ORDER BY wl.won_leads DESC)              AS "Ranking"
FROM weekly_leads wl
LEFT JOIN activities_week aw ON aw.user_id = wl.user_id
LEFT JOIN last_won_lead lwl ON lwl.assigned_to = wl.user_id
ORDER BY wl.won_leads DESC, wl.total_leads DESC;