-- ============================================================================
-- Migration 009: Sistema de Subcategorias para categorias_conocimientos_clientes
-- Fecha: 2026-01-26
-- Plan: SISTEMA_SUBCATEGORIAS (RUN 1, FASE 1)
-- ============================================================================
-- IMPORTANTE: Este script es idempotente - puede ejecutarse multiples veces
-- ============================================================================

-- ============================================================================
-- SECCION 1: COLUMNAS NUEVAS
-- ============================================================================

-- 1.1 parent_id: FK auto-referencial para jerarquia (max 1 nivel)
ALTER TABLE categorias_conocimientos_clientes
ADD COLUMN IF NOT EXISTS parent_id INTEGER REFERENCES categorias_conocimientos_clientes(category_id);

-- 1.2 modo_asignacion: Como se asigna la subcategoria al cliente
ALTER TABLE categorias_conocimientos_clientes
ADD COLUMN IF NOT EXISTS modo_asignacion VARCHAR(20) DEFAULT 'automatico';

-- 1.3 preguntas_subcategorias: JSONB para preguntas de clasificacion
ALTER TABLE categorias_conocimientos_clientes
ADD COLUMN IF NOT EXISTS preguntas_subcategorias JSONB;

-- 1.4 Agregar CHECK constraint para modo_asignacion (separado por compatibilidad)
ALTER TABLE categorias_conocimientos_clientes
DROP CONSTRAINT IF EXISTS chk_modo_asignacion;

ALTER TABLE categorias_conocimientos_clientes
ADD CONSTRAINT chk_modo_asignacion
CHECK (modo_asignacion IN ('automatico', 'pregunta_binaria', 'oculta'));

-- ============================================================================
-- SECCION 2: INDICES
-- ============================================================================

-- 2.1 Indice para busqueda por parent_id
CREATE INDEX IF NOT EXISTS idx_cat_cli_con_parent_id
ON categorias_conocimientos_clientes(parent_id);

-- 2.2 Indice para encontrar hijos de un padre (activos)
CREATE INDEX IF NOT EXISTS idx_cat_cli_con_parent_children
ON categorias_conocimientos_clientes(parent_id, category_id)
WHERE soft_delete IS NOT TRUE;

-- 2.3 Indice para filtrar por modo_asignacion (activos)
CREATE INDEX IF NOT EXISTS idx_cat_cli_con_modo_asignacion
ON categorias_conocimientos_clientes(modo_asignacion)
WHERE soft_delete IS NOT TRUE;

-- 2.4 Indice GIN para preguntas_subcategorias JSONB
CREATE INDEX IF NOT EXISTS idx_cat_cli_con_preguntas_jsonb
ON categorias_conocimientos_clientes USING GIN (preguntas_subcategorias);

-- ============================================================================
-- SECCION 3: FUNCION get_category_descendants
-- Retorna una categoria y sus subcategorias (max 1 nivel de profundidad)
-- ============================================================================

CREATE OR REPLACE FUNCTION get_category_descendants(p_category_id INTEGER)
RETURNS TABLE(category_id INTEGER, name VARCHAR, depth INTEGER) AS $$
BEGIN
    RETURN QUERY
    WITH RECURSIVE descendants AS (
        -- Base: la categoria padre
        SELECT c.category_id, c.name, 0 as depth
        FROM categorias_conocimientos_clientes c
        WHERE c.category_id = p_category_id AND c.soft_delete IS NOT TRUE

        UNION ALL

        -- Recursivo: hijos directos (solo 1 nivel)
        SELECT c.category_id, c.name, d.depth + 1
        FROM categorias_conocimientos_clientes c
        INNER JOIN descendants d ON c.parent_id = d.category_id
        WHERE c.soft_delete IS NOT TRUE AND d.depth < 1
    )
    SELECT descendants.category_id, descendants.name, descendants.depth FROM descendants;
END;
$$ LANGUAGE plpgsql STABLE;

-- ============================================================================
-- SECCION 4: FUNCION expand_client_categories
-- Expande un array de category_ids para incluir todas sus subcategorias
-- ============================================================================

CREATE OR REPLACE FUNCTION expand_client_categories(category_ids INTEGER[])
RETURNS INTEGER[] AS $$
DECLARE
    expanded INTEGER[];
    cat_id INTEGER;
    descendant_ids INTEGER[];
BEGIN
    expanded := category_ids;

    -- Por cada categoria, agregar sus descendientes
    FOREACH cat_id IN ARRAY category_ids LOOP
        SELECT array_agg(gcd.category_id) INTO descendant_ids
        FROM get_category_descendants(cat_id) gcd
        WHERE gcd.category_id != cat_id;

        IF descendant_ids IS NOT NULL THEN
            expanded := expanded || descendant_ids;
        END IF;
    END LOOP;

    -- Eliminar duplicados
    SELECT array_agg(DISTINCT e) INTO expanded FROM unnest(expanded) e;

    RETURN expanded;
END;
$$ LANGUAGE plpgsql STABLE;

-- ============================================================================
-- SECCION 5: FUNCION para validar profundidad (maximo 1 nivel)
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_validate_category_depth()
RETURNS TRIGGER AS $$
BEGIN
    -- Si tiene parent_id, verificar que el padre NO tenga parent_id
    IF NEW.parent_id IS NOT NULL THEN
        IF EXISTS (
            SELECT 1 FROM categorias_conocimientos_clientes
            WHERE category_id = NEW.parent_id AND parent_id IS NOT NULL
        ) THEN
            RAISE EXCEPTION 'Las subcategorias no pueden tener hijos (maximo 1 nivel de profundidad)';
        END IF;
    END IF;

    -- Si esta categoria tiene hijos, no permitir que sea subcategoria
    IF NEW.parent_id IS NOT NULL AND EXISTS (
        SELECT 1 FROM categorias_conocimientos_clientes
        WHERE parent_id = NEW.category_id AND soft_delete IS NOT TRUE
    ) THEN
        RAISE EXCEPTION 'No se puede convertir en subcategoria: esta categoria ya tiene hijos';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- SECCION 6: TRIGGER de validacion de profundidad
-- ============================================================================

DROP TRIGGER IF EXISTS trg_cat_cli_validate_depth ON categorias_conocimientos_clientes;

CREATE TRIGGER trg_cat_cli_validate_depth
    BEFORE INSERT OR UPDATE OF parent_id ON categorias_conocimientos_clientes
    FOR EACH ROW
    EXECUTE FUNCTION fn_validate_category_depth();

-- ============================================================================
-- SECCION 7: FUNCION para cascade soft delete
-- ============================================================================

CREATE OR REPLACE FUNCTION fn_cascade_soft_delete_subcategories()
RETURNS TRIGGER AS $$
BEGIN
    -- Si se hace soft_delete del padre, propagar a subcategorias
    IF NEW.soft_delete = TRUE AND (OLD.soft_delete IS NULL OR OLD.soft_delete = FALSE) THEN
        UPDATE categorias_conocimientos_clientes
        SET soft_delete = TRUE,
            soft_delete_date = NOW()
        WHERE parent_id = NEW.category_id AND (soft_delete IS NULL OR soft_delete = FALSE);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================================================
-- SECCION 8: TRIGGER de cascade soft delete
-- ============================================================================

DROP TRIGGER IF EXISTS trg_cat_cli_cascade_soft_delete ON categorias_conocimientos_clientes;

CREATE TRIGGER trg_cat_cli_cascade_soft_delete
    AFTER UPDATE OF soft_delete ON categorias_conocimientos_clientes
    FOR EACH ROW
    WHEN (NEW.soft_delete = TRUE)
    EXECUTE FUNCTION fn_cascade_soft_delete_subcategories();

-- ============================================================================
-- SECCION 9: Comentarios de columnas
-- ============================================================================

COMMENT ON COLUMN categorias_conocimientos_clientes.parent_id IS
'FK a la categoria padre. NULL = categoria principal, NOT NULL = subcategoria. Maximo 1 nivel de profundidad.';

COMMENT ON COLUMN categorias_conocimientos_clientes.modo_asignacion IS
'Como se asigna la subcategoria: automatico (heredada del padre), pregunta_binaria (confirmar si/no), oculta (solo admin).';

COMMENT ON COLUMN categorias_conocimientos_clientes.preguntas_subcategorias IS
'JSONB con configuracion de preguntas para clasificar en subcategorias. Formato: {"texto": "...", "opciones": [...]}';

-- ============================================================================
-- FIN DE LA MIGRACION 009
-- ============================================================================
