BEGIN;

SET LOCAL lock_timeout = '10s';
SET LOCAL statement_timeout = '900000';
SET LOCAL idle_in_transaction_session_timeout = '900000';

CREATE SCHEMA IF NOT EXISTS public_legacy;

CREATE OR REPLACE FUNCTION pg_temp.restore_compat_table(
    p_target_schema text,
    p_source_schema text,
    p_table_name text,
    p_pk_column text DEFAULT NULL,
    p_sequence_schema text DEFAULT NULL,
    p_sequence_name text DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
    v_override text := '';
    v_target_sequence_qualified text;
    v_max_pk bigint;
BEGIN
    EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', p_target_schema, p_table_name);
    EXECUTE format('CREATE TABLE %I.%I (LIKE %I.%I INCLUDING ALL)', p_target_schema, p_table_name, p_source_schema, p_table_name);

    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = p_source_schema
          AND table_name = p_table_name
          AND is_identity = 'YES'
          AND identity_generation = 'ALWAYS'
    ) THEN
        v_override := 'OVERRIDING SYSTEM VALUE';
    END IF;

    EXECUTE format(
        'INSERT INTO %I.%I %s SELECT * FROM %I.%I',
        p_target_schema,
        p_table_name,
        v_override,
        p_source_schema,
        p_table_name
    );

    IF p_pk_column IS NOT NULL AND p_sequence_schema IS NOT NULL AND p_sequence_name IS NOT NULL THEN
        v_target_sequence_qualified := format('%I.%I', p_sequence_schema, p_sequence_name);
        IF EXISTS (
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = p_target_schema
              AND table_name = p_table_name
              AND column_name = p_pk_column
              AND is_identity = 'YES'
        ) THEN
            EXECUTE format(
                'ALTER TABLE %I.%I ALTER COLUMN %I DROP IDENTITY IF EXISTS',
                p_target_schema,
                p_table_name,
                p_pk_column
            );
        ELSE
            EXECUTE format(
                'ALTER TABLE %I.%I ALTER COLUMN %I DROP DEFAULT',
                p_target_schema,
                p_table_name,
                p_pk_column
            );
        END IF;
        EXECUTE format('DROP SEQUENCE IF EXISTS %I.%I CASCADE', p_sequence_schema, p_sequence_name);
        EXECUTE format('CREATE SEQUENCE %I.%I', p_sequence_schema, p_sequence_name);
        EXECUTE format(
            'ALTER TABLE %I.%I ALTER COLUMN %I SET DEFAULT nextval(%L::regclass)',
            p_target_schema,
            p_table_name,
            p_pk_column,
            v_target_sequence_qualified
        );
        EXECUTE format(
            'ALTER SEQUENCE %I.%I OWNED BY %I.%I.%I',
            p_sequence_schema,
            p_sequence_name,
            p_target_schema,
            p_table_name,
            p_pk_column
        );
        EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM %I.%I', p_pk_column, p_target_schema, p_table_name)
            INTO v_max_pk;
        IF v_max_pk > 0 THEN
            EXECUTE format('SELECT setval(%L, %s, true)', v_target_sequence_qualified, v_max_pk);
        ELSE
            EXECUTE format('SELECT setval(%L, 1, false)', v_target_sequence_qualified);
        END IF;
    END IF;
END;
$function$;

CREATE OR REPLACE FUNCTION pg_temp.restore_public_view(
    p_view_name text,
    p_source_schema text
)
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
    EXECUTE format('DROP VIEW IF EXISTS public.%I', p_view_name);
    EXECUTE format('CREATE VIEW public.%I AS SELECT * FROM %I.%I', p_view_name, p_source_schema, p_view_name);
END;
$function$;

CREATE OR REPLACE FUNCTION public.categorias_inherit_sucursal()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
  IF NEW.parent_id IS NULL THEN
    RETURN NEW;
  END IF;
  IF cardinality(NEW.sucursal) = 0 THEN
    SELECT sucursal INTO NEW.sucursal FROM categorias WHERE category_id = NEW.parent_id;
  END IF;
  RETURN NEW;
END;$function$;

CREATE OR REPLACE FUNCTION public.categorias_set_path()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
  IF NEW.parent_id IS NULL THEN
    NEW.path := NEW.category_id::text::ltree;
  ELSE
    NEW.path := (SELECT path FROM categorias WHERE category_id = NEW.parent_id) || NEW.category_id::text;
  END IF;
  RETURN NEW;
END;$function$;

CREATE OR REPLACE FUNCTION public.enforce_item_categoria_sucursal()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
  cat integer[];
  art integer[];
BEGIN
  SELECT sucursal INTO cat FROM categorias WHERE category_id = NEW.category_id;
  SELECT sucursal INTO art FROM inventario WHERE id_item = NEW.item_id;
  IF cardinality(cat)=0 OR cardinality(art)=0 THEN
    RETURN NEW;
  END IF;
  IF NOT cat && art THEN
    RAISE EXCEPTION 'La categoría no pertenece a las sucursales del artículo';
  END IF;
  RETURN NEW;
END;$function$;

CREATE OR REPLACE FUNCTION public.fn_cascade_soft_delete_rules_v2()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.soft_delete = true AND OLD.soft_delete = false THEN
        UPDATE category_rules_v2
        SET soft_delete = true,
            soft_delete_date = NOW()
        WHERE source_category_id = NEW.category_id
          AND soft_delete = false;
    END IF;
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_cascade_soft_delete_variantes_on_category_delete()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.soft_delete = true AND (OLD.soft_delete IS NOT TRUE) THEN
        UPDATE inventario_variantes_categoria
        SET soft_delete = true,
            soft_delete_date = NOW()
        WHERE category_id = NEW.category_id
          AND soft_delete IS NOT TRUE;
    END IF;
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_cat_v2_updated_at()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_clean_preguntas_on_category_deactivate()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.is_active = FALSE AND OLD.is_active = TRUE THEN
        UPDATE preguntas_clasificacion_multi
        SET categorias_participantes = array_remove(categorias_participantes, NEW.category_id),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND soft_delete IS NOT TRUE
          AND categorias_participantes @> ARRAY[NEW.category_id];
    END IF;
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_clean_v2_category_from_jsonb()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.soft_delete = TRUE AND (OLD.soft_delete IS NULL OR OLD.soft_delete = FALSE) THEN
        UPDATE clientes
        SET categorias = (
                SELECT COALESCE(jsonb_agg(elem), '[]'::jsonb)
                FROM jsonb_array_elements(categorias) AS elem
                WHERE elem::int != NEW.category_id
            ),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND categorias @> to_jsonb(NEW.category_id);

        UPDATE conocimientos_empleado_digital
        SET categorias = (
                SELECT COALESCE(jsonb_agg(elem), '[]'::jsonb)
                FROM jsonb_array_elements(categorias) AS elem
                WHERE elem::int != NEW.category_id
            ),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND categorias @> to_jsonb(NEW.category_id);

        UPDATE preguntas_clasificacion_multi
        SET categorias_participantes = array_remove(categorias_participantes, NEW.category_id),
            updated_at = NOW()
        WHERE business_id = NEW.business_id
          AND soft_delete IS NOT TRUE
          AND categorias_participantes @> ARRAY[NEW.category_id];
    END IF;
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_ivc_updated_at()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_rules_v2_updated_at()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_validate_category_rule_v2()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_cycle_found BOOLEAN := FALSE;
    v_contradiction_found BOOLEAN := FALSE;
    v_visited INTEGER[];
    v_current INTEGER;
    v_next INTEGER;
BEGIN
    IF NEW.rule_type = 'IMPLIES' THEN
        v_visited := ARRAY[NEW.source_category_id];
        v_current := NEW.target_category_id;

        WHILE v_current IS NOT NULL AND NOT v_cycle_found LOOP
            IF v_current = ANY(v_visited) THEN
                v_cycle_found := TRUE;
            ELSE
                v_visited := array_append(v_visited, v_current);
                SELECT target_category_id INTO v_next
                FROM category_rules_v2
                WHERE source_category_id = v_current
                  AND rule_type = 'IMPLIES'
                  AND soft_delete = false
                  AND business_id = NEW.business_id
                LIMIT 1;
                v_current := v_next;
            END IF;

            IF array_length(v_visited, 1) > 100 THEN
                RAISE EXCEPTION 'Profundidad máxima de inferencia excedida (100)';
            END IF;
        END LOOP;

        IF v_cycle_found THEN
            RAISE EXCEPTION 'No se puede crear esta regla: genera un ciclo de inferencias';
        END IF;
    END IF;

    IF NEW.rule_type IN ('IMPLIES', 'EXCLUDES') THEN
        SELECT EXISTS(
            SELECT 1 FROM category_rules_v2
            WHERE business_id = NEW.business_id
              AND source_category_id = NEW.source_category_id
              AND target_category_id = NEW.target_category_id
              AND rule_type != NEW.rule_type
              AND rule_type IN ('IMPLIES', 'EXCLUDES')
              AND soft_delete = false
        ) INTO v_contradiction_found;

        IF v_contradiction_found THEN
            RAISE EXCEPTION 'Configuración inválida: contradicción entre reglas IMPLIES y EXCLUDES';
        END IF;
    END IF;

    RETURN NEW;
END;
$function$;

CREATE OR REPLACE FUNCTION public.jsonb_to_intarray(j jsonb)
 RETURNS integer[]
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT COALESCE(array_agg(value::int),'{}'::integer[]) FROM jsonb_array_elements_text(j) AS t(value);
$function$;

CREATE OR REPLACE FUNCTION public.trg_itemcategoria_rules()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
  conflict integer;
BEGIN
  IF TG_OP = 'DELETE' THEN
    RETURN OLD;
  END IF;

  SELECT 1 INTO conflict
  FROM category_rules r
  WHERE r.rule_type = 'excludes'
    AND r.category_id = NEW.category_id
    AND EXISTS (
      SELECT 1
      FROM item_categoria ic
      WHERE ic.item_id = NEW.item_id
        AND ic.category_id = r.target_category_id
    )
  LIMIT 1;

  IF conflict IS NOT NULL THEN
    RAISE EXCEPTION 'Regla de exclusión violada';
  END IF;

  INSERT INTO item_categoria(item_id, category_id)
  SELECT NEW.item_id, r.target_category_id
  FROM category_rules r
  WHERE r.rule_type = 'requires'
    AND r.category_id = NEW.category_id
    AND NOT EXISTS (
      SELECT 1
      FROM item_categoria ic
      WHERE ic.item_id = NEW.item_id
        AND ic.category_id = r.target_category_id
    );

  RETURN NEW;
END;$function$;

SELECT pg_temp.restore_compat_table('public_legacy', 'ops', 'error_log', 'id_error', 'public_legacy', 'error_log_id_error_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'ops', 'pos_idempotency_requests', 'id', 'public_legacy', 'pos_idempotency_requests_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'ops', 'webhook_ingress_dlq', 'id', 'public_legacy', 'webhook_ingress_dlq_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'ops', 'webhook_ingress_events', 'id', 'public_legacy', 'webhook_ingress_events_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'catalog', 'categorias_combinables_v2', 'category_id', 'public_legacy', 'categorias_combinables_v2_category_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'catalog', 'category_rules_v2', 'rule_id', 'public_legacy', 'category_rules_v2_rule_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'catalog', 'entity_category_links', 'id_link', 'public_legacy', 'entity_category_links_id_link_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'catalog', 'inventario', 'id_item', 'public_legacy', 'inventario_id_item_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'catalog', 'inventario_variantes_categoria', 'id_variante', 'public_legacy', 'inventario_variantes_categoria_id_variante_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'catalog', 'inventario_variantes_resolucion_log', 'id_log', 'public_legacy', 'inventario_variantes_resolucion_log_id_log_seq');

ALTER TABLE ONLY public_legacy.category_rules_v2
    ADD CONSTRAINT category_rules_v2_source_category_id_fkey FOREIGN KEY (source_category_id) REFERENCES public_legacy.categorias_combinables_v2(category_id);
ALTER TABLE ONLY public_legacy.category_rules_v2
    ADD CONSTRAINT category_rules_v2_target_category_id_fkey FOREIGN KEY (target_category_id) REFERENCES public_legacy.categorias_combinables_v2(category_id);
ALTER TABLE ONLY public_legacy.entity_category_links
    ADD CONSTRAINT fk_ecl_category FOREIGN KEY (category_id) REFERENCES public_legacy.categorias_combinables_v2(category_id);
ALTER TABLE ONLY public_legacy.inventario_variantes_categoria
    ADD CONSTRAINT fk_ivc_categoria FOREIGN KEY (category_id) REFERENCES public_legacy.categorias_combinables_v2(category_id);
ALTER TABLE ONLY public_legacy.inventario_variantes_categoria
    ADD CONSTRAINT fk_ivc_inventario FOREIGN KEY (id_item) REFERENCES public_legacy.inventario(id_item);

CREATE SEQUENCE IF NOT EXISTS tmp_fk_test.entity_category_links_id_link_seq;
SELECT setval(
    'tmp_fk_test.entity_category_links_id_link_seq',
    GREATEST(COALESCE((SELECT MAX(id_link) FROM tmp_fk_test.entity_category_links), 0), 1),
    COALESCE((SELECT MAX(id_link) FROM tmp_fk_test.entity_category_links), 0) > 0
);
ALTER TABLE tmp_fk_test.entity_category_links
    ALTER COLUMN id_link SET DEFAULT nextval('tmp_fk_test.entity_category_links_id_link_seq'::regclass);
ALTER SEQUENCE tmp_fk_test.entity_category_links_id_link_seq
    OWNED BY tmp_fk_test.entity_category_links.id_link;

SELECT pg_temp.restore_public_view('error_log', 'ops');
SELECT pg_temp.restore_public_view('pos_idempotency_requests', 'ops');
SELECT pg_temp.restore_public_view('webhook_ingress_dlq', 'ops');
SELECT pg_temp.restore_public_view('webhook_ingress_events', 'ops');
SELECT pg_temp.restore_public_view('categorias_combinables_v2', 'catalog');
SELECT pg_temp.restore_public_view('category_rules_v2', 'catalog');
SELECT pg_temp.restore_public_view('entity_category_links', 'catalog');
SELECT pg_temp.restore_public_view('inventario', 'catalog');
SELECT pg_temp.restore_public_view('inventario_variantes_categoria', 'catalog');
SELECT pg_temp.restore_public_view('inventario_variantes_resolucion_log', 'catalog');

CREATE TRIGGER trg_cascade_soft_delete_rules_v2 AFTER UPDATE ON public_legacy.categorias_combinables_v2 FOR EACH ROW WHEN (new.soft_delete = true AND old.soft_delete = false) EXECUTE FUNCTION fn_cascade_soft_delete_rules_v2();
CREATE TRIGGER trg_cascade_soft_delete_variantes_v2 AFTER UPDATE ON public_legacy.categorias_combinables_v2 FOR EACH ROW WHEN (new.soft_delete = true AND old.soft_delete IS DISTINCT FROM true) EXECUTE FUNCTION fn_cascade_soft_delete_variantes_on_category_delete();
CREATE TRIGGER trg_cat_v2_updated_at BEFORE UPDATE ON public_legacy.categorias_combinables_v2 FOR EACH ROW EXECUTE FUNCTION fn_cat_v2_updated_at();
CREATE TRIGGER trg_clean_preguntas_on_category_deactivate AFTER UPDATE OF is_active ON public_legacy.categorias_combinables_v2 FOR EACH ROW WHEN (new.is_active = false AND old.is_active = true) EXECUTE FUNCTION fn_clean_preguntas_on_category_deactivate();
CREATE TRIGGER trg_clean_v2_category_from_jsonb AFTER UPDATE OF soft_delete ON public_legacy.categorias_combinables_v2 FOR EACH ROW WHEN (new.soft_delete = true) EXECUTE FUNCTION fn_clean_v2_category_from_jsonb();
CREATE TRIGGER trg_rules_v2_updated_at BEFORE UPDATE ON public_legacy.category_rules_v2 FOR EACH ROW EXECUTE FUNCTION fn_rules_v2_updated_at();
CREATE TRIGGER trg_validate_category_rule_v2 BEFORE INSERT OR UPDATE ON public_legacy.category_rules_v2 FOR EACH ROW WHEN (new.soft_delete = false) EXECUTE FUNCTION fn_validate_category_rule_v2();
CREATE TRIGGER trg_ivc_updated_at BEFORE UPDATE ON public_legacy.inventario_variantes_categoria FOR EACH ROW EXECUTE FUNCTION fn_ivc_updated_at();

COMMIT;
