-- Migración 32: Campo proyecto_inicial para tracking de primer proyecto por cliente
-- Permite vincular cada proyecto con el primer proyecto creado para la misma razón social

ALTER TABLE thread ADD COLUMN proyecto_inicial INTEGER DEFAULT NULL;

-- Índice en thread_label(label_id) para optimizar queries de consulta por cliente
-- El PK existente (thread_id, label_id) solo optimiza búsquedas por thread_id
CREATE INDEX IF NOT EXISTS thread_label_ix_label_id ON thread_label (label_id);

-- Backfill determinista: para cada thread con cliente, asignar el MIN thread_id
-- de TODOS los labels asociados a ese thread (maneja correctamente N:M)
UPDATE thread SET proyecto_inicial = (
    SELECT MIN(sub.proyecto_minimo) FROM (
        SELECT MIN(tl2.thread_id) AS proyecto_minimo
        FROM thread_label tl1
        JOIN thread_label tl2 ON tl1.label_id = tl2.label_id
        JOIN thread t2 ON t2.id = tl2.thread_id AND t2.deleted_at IS NULL
        WHERE tl1.thread_id = thread.id
        GROUP BY tl1.label_id
    ) sub
)
WHERE id IN (SELECT thread_id FROM thread_label);

-- Recrear thread_view incluyendo columna proyecto_inicial
DROP VIEW IF EXISTS thread_view;

CREATE VIEW thread_view AS
SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by,
       thread.private, thread.lock, thread.type,
       thread.year,
       thread.proyecto_inicial,
       thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date,
       thread.title, thread.content, thread.content_html,
       thread.task_count, thread.task_count_closed, thread.task_sum_cost,
       thread.deleted_at,
       label.id AS label_id,
       label.name AS label_name,
       (SELECT MAX(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority,
       CASE WHEN thread.state = 'proposal' THEN 0
            WHEN thread.state = 'opened' AND thread.task_count = 0 THEN 1
            WHEN thread.state = 'opened' THEN 2
            WHEN thread.state = 'closed' THEN 3
            WHEN thread.state = 'rejected' THEN 4 END AS sort,
       CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done'
            ELSE thread.state END AS state,
       (SELECT COUNT(*) FROM task WHERE type = 'corrective' AND thread_id=thread.id) AS corrective_count,
       (SELECT COUNT(*) FROM task WHERE type = 'preventive' AND thread_id=thread.id) AS preventive_count
FROM thread
     LEFT JOIN thread_label ON thread.id = thread_label.thread_id
     LEFT JOIN label ON label.id = thread_label.label_id
WHERE thread.deleted_at IS NULL;