-- Migration 24: Agregar columna year para almacenar año de operación del proyecto
-- Permite filtrado directo por año sin depender de BETWEEN sobre create_date

ALTER TABLE thread ADD COLUMN year TEXT DEFAULT NULL;

-- Popular año de threads existentes: primero intentar extraer del título (formato "Año: YYYY"),
-- si no existe, usar el año del create_date
UPDATE thread SET year = CASE
    WHEN title LIKE '%Año: ____%' AND SUBSTR(title, INSTR(title, 'Año: ') + 5, 4) GLOB '[0-9][0-9][0-9][0-9]'
        THEN SUBSTR(title, INSTR(title, 'Año: ') + 5, 4)
    ELSE SUBSTR(create_date, 1, 4)
END
WHERE year IS NULL;

-- Recrear thread_view incluyendo columna year
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.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;