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_insert_columns 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;

    SELECT string_agg(format('%I', column_name), ', ' ORDER BY ordinal_position)
      INTO v_insert_columns
    FROM information_schema.columns
    WHERE table_schema = p_source_schema
      AND table_name = p_table_name
      AND is_generated = 'NEVER';

    EXECUTE format(
        'INSERT INTO %I.%I (%s) %s SELECT %s FROM %I.%I',
        p_target_schema,
        p_table_name,
        v_insert_columns,
        v_override,
        v_insert_columns,
        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.trig_refresca_jornadas_config()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    rec record;
BEGIN
    IF TG_OP = 'DELETE' THEN
        RETURN NULL;
    END IF;
FOR rec IN
        SELECT DISTINCT u.id_login
        FROM usuarios u
        JOIN login l ON l.id_login = u.id_login
        WHERE l.business_id = COALESCE(NEW.business_id,OLD.business_id)
          AND COALESCE(u.soft_delete,false) = false
    LOOP
        PERFORM refresca_jornadas_sucursal(rec.id_login);
    END LOOP;
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.notas_cliente_soft_delete_stamp()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
  IF NEW.soft_delete = TRUE AND (OLD.soft_delete IS DISTINCT FROM TRUE) THEN
    NEW.soft_delete_date := NOW();
  ELSIF NEW.soft_delete = FALSE THEN
    NEW.soft_delete_date := NULL;
  END IF;
  RETURN NEW;
END;
$function$;

SELECT pg_temp.restore_compat_table('public_legacy', 'platform', 'configuraciones');
SELECT pg_temp.restore_compat_table('public_legacy', 'platform', 'estadisticas', 'id_estadistica', 'public_legacy', 'estadisticas_id_estadistica_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'platform', 'notas_internas', 'id_nota', 'public_legacy', 'notas_cliente_id_nota_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'platform', 'opi_prompts', 'id', 'public_legacy', 'opi_prompts_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'ai', 'analisis_conversacion', 'id_analisis', 'public_legacy', 'analisis_conversacion_id_analisis_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'ai', 'conocimientos_empleado_digital', 'id', 'public_legacy', 'conocimientos_empleado_digital_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'ai', 'empleado_digital', 'id_empleado', 'public_legacy', 'empleado_digital_id_empleado_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'ai', 'estadisticas_costos_ia', 'id', 'public_legacy', 'estadisticas_costos_ia_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'crm', 'clientes', 'id_cliente', 'public_legacy', 'clientes_id_cliente_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'crm', 'preguntas_clasificacion_multi', 'pregunta_id', 'public_legacy', 'preguntas_clasificacion_multi_pregunta_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'crm', 'reportes', 'id_reporte', 'public_legacy', 'reportes_id_reporte_seq');

ALTER TABLE IF EXISTS public_legacy.planes_pago
    DROP CONSTRAINT IF EXISTS fk_planes_cliente;
ALTER TABLE IF EXISTS public_legacy.planes_pago
    ADD CONSTRAINT fk_planes_cliente
    FOREIGN KEY (id_cliente)
    REFERENCES public_legacy.clientes(id_cliente)
    ON DELETE RESTRICT
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.planes_pago
    VALIDATE CONSTRAINT fk_planes_cliente;

ALTER TABLE IF EXISTS public_legacy.pagos_planes
    DROP CONSTRAINT IF EXISTS fk_pago_pagador;
ALTER TABLE IF EXISTS public_legacy.pagos_planes
    ADD CONSTRAINT fk_pago_pagador
    FOREIGN KEY (id_cliente_pagador)
    REFERENCES public_legacy.clientes(id_cliente)
    ON DELETE RESTRICT
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.pagos_planes
    VALIDATE CONSTRAINT fk_pago_pagador;

ALTER TABLE IF EXISTS public_legacy.ventas
    DROP CONSTRAINT IF EXISTS fk_ventas_cliente;
ALTER TABLE IF EXISTS public_legacy.ventas
    ADD CONSTRAINT fk_ventas_cliente
    FOREIGN KEY (id_cliente)
    REFERENCES public_legacy.clientes(id_cliente)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.ventas
    VALIDATE CONSTRAINT fk_ventas_cliente;

ALTER TABLE ONLY public_legacy.notas_internas
    ADD CONSTRAINT fk_notas_estadistica FOREIGN KEY (id_estadistica) REFERENCES public_legacy.estadisticas(id_estadistica) ON DELETE RESTRICT;
ALTER TABLE ONLY public_legacy.notas_internas
    ADD CONSTRAINT notas_cliente_id_cliente_fkey FOREIGN KEY (id_cliente) REFERENCES public_legacy.clientes(id_cliente);
ALTER TABLE ONLY public_legacy.notas_internas
    ADD CONSTRAINT fk_notas_plan FOREIGN KEY (id_plan_pago) REFERENCES public_legacy.planes_pago(id_plan) ON DELETE RESTRICT;
ALTER TABLE ONLY public_legacy.notas_internas
    ADD CONSTRAINT fk_notas_venta FOREIGN KEY (id_venta) REFERENCES public_legacy.ventas(id_venta) ON DELETE RESTRICT;
ALTER TABLE ONLY public_legacy.reportes
    ADD CONSTRAINT reportes_id_cliente_fkey FOREIGN KEY (id_cliente) REFERENCES public_legacy.clientes(id_cliente) ON UPDATE CASCADE ON DELETE SET NULL;

SELECT pg_temp.restore_public_view('configuraciones', 'platform');
SELECT pg_temp.restore_public_view('estadisticas', 'platform');
SELECT pg_temp.restore_public_view('notas_internas', 'platform');
SELECT pg_temp.restore_public_view('opi_prompts', 'platform');
SELECT pg_temp.restore_public_view('analisis_conversacion', 'ai');
SELECT pg_temp.restore_public_view('conocimientos_empleado_digital', 'ai');
SELECT pg_temp.restore_public_view('empleado_digital', 'ai');
SELECT pg_temp.restore_public_view('estadisticas_costos_ia', 'ai');
SELECT pg_temp.restore_public_view('clientes', 'crm');
SELECT pg_temp.restore_public_view('preguntas_clasificacion_multi', 'crm');
SELECT pg_temp.restore_public_view('reportes', 'crm');

CREATE TRIGGER trg_refresca_jornadas_config AFTER INSERT OR UPDATE ON public_legacy.configuraciones FOR EACH ROW EXECUTE FUNCTION trig_refresca_jornadas_config();
CREATE TRIGGER notas_cliente_before_update_soft_delete BEFORE UPDATE ON public_legacy.notas_internas FOR EACH ROW EXECUTE FUNCTION notas_cliente_soft_delete_stamp();
CREATE TRIGGER trg_opi_prompts_updated_at BEFORE UPDATE ON public_legacy.opi_prompts FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

COMMIT;
