-- Migración 30: Tareas, Seguimiento y Participantes para Proyectos Administrativos
-- Tablas independientes del sistema BEZ principal (Opción B)

-- Tareas del proyecto administrativo
CREATE TABLE IF NOT EXISTS admin_thread_task (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_thread_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    assignee TEXT,
    plan_date TEXT,
    state TEXT DEFAULT 'opened',
    created_by TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME,
    closed_by TEXT,
    FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_admin_task_thread ON admin_thread_task(admin_thread_id);
CREATE INDEX IF NOT EXISTS idx_admin_task_state ON admin_thread_task(state);

-- Seguimiento (comentarios) del proyecto administrativo
CREATE TABLE IF NOT EXISTS admin_thread_comment (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_thread_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    content_html TEXT,
    author TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_admin_comment_thread ON admin_thread_comment(admin_thread_id);

-- Participantes del proyecto administrativo
CREATE TABLE IF NOT EXISTS admin_thread_participant (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_thread_id INTEGER NOT NULL,
    user_id TEXT NOT NULL,
    role TEXT DEFAULT 'participant',
    added_by TEXT NOT NULL,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(admin_thread_id, user_id),
    FOREIGN KEY (admin_thread_id) REFERENCES admin_thread(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_admin_participant_thread ON admin_thread_participant(admin_thread_id);