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.apply_block_user(p_login integer, p_range tstzrange, p_estado_int integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
    PERFORM apply_block_user(
        p_login,
        p_range,
        CASE
            WHEN p_estado_int = 2 THEN 'EXTRA'
            ELSE 'CITA'
        END,
        FALSE
    );
END;
$function$;

CREATE OR REPLACE FUNCTION public.apply_block_user(p_login integer, p_range tstzrange, p_destino text, p_remove boolean DEFAULT false)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_business character varying(100);
    col_name   text;
    v_sql      text;
BEGIN
    SELECT business_id INTO v_business FROM login WHERE id_login = p_login;

    INSERT INTO disponibilidad_usuarios(id_login,business_id)
    VALUES (p_login,v_business)
    ON CONFLICT (id_login) DO NOTHING;

    IF p_destino = 'CITA' THEN
        col_name := 'disp_confirmada';
    ELSE
        col_name := 'disponibilidad_trabajo_extra';
    END IF;
IF p_remove THEN
        v_sql := format(
            'UPDATE disponibilidad_usuarios
             SET %I = COALESCE(%I,''{}''::tstzmultirange) - $1::tstzmultirange
             WHERE id_login = $2',
            col_name,col_name);
    ELSE
        v_sql := format(
            'UPDATE disponibilidad_usuarios
             SET %I = COALESCE(%I,''{}''::tstzmultirange) + $1::tstzmultirange
             WHERE id_login = $2',
            col_name,col_name);
    END IF;

    EXECUTE v_sql USING p_range,p_login;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fill_jornada_sucursal_from_login(p_login integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_rows jsonb;
BEGIN
    SELECT jsonb_agg(
             jsonb_build_object(
               'horario', cfg.horario,
               'sucursal', s
             )
           )
    INTO  v_rows
    FROM  login l
    JOIN  configuraciones cfg
          ON cfg.business_id = l.business_id
         AND COALESCE(cfg.soft_delete,false) = false
    CROSS JOIN LATERAL unnest(cfg.sucursal) AS s
    WHERE l.id_login = p_login
      AND s = ANY(l.sucursal);
UPDATE disponibilidad_usuarios
    SET    jornada_sucursal = COALESCE(v_rows,'[]'::jsonb)
    WHERE  id_login = p_login;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_remover_sucursal_global(p_business_id character varying, p_sucursal_id integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
 r record;
 v_sql text;
 has_soft boolean;
 has_soft_date boolean;

BEGIN
 FOR r IN
     SELECT table_schema, table_name
     FROM information_schema.columns c1
     JOIN information_schema.columns c2 USING(table_schema, table_name)
     WHERE c1.column_name = 'business_id'
       AND c2.column_name = 'sucursal'
       AND c1.table_schema = 'public'
     GROUP BY table_schema, table_name
 LOOP
     SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = r.table_schema AND table_name = r.table_name AND column_name = 'soft_delete')
       INTO has_soft;

SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema = r.table_schema AND table_name = r.table_name AND column_name = 'soft_delete_date')
       INTO has_soft_date;
     v_sql := 'UPDATE ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) ||
              ' SET sucursal = CASE WHEN sucursal @> array[' || p_sucursal_id ||
              '] AND cardinality(sucursal) > 1 THEN array_remove(sucursal,' || p_sucursal_id ||
              ') ELSE sucursal END';
IF has_soft THEN
         v_sql := v_sql || ', soft_delete = CASE WHEN (sucursal @> array[' || p_sucursal_id ||
                 ']) AND cardinality(sucursal) = 1 THEN true ELSE soft_delete END';
     END IF;
IF has_soft_date THEN
         v_sql := v_sql || ', soft_delete_date = CASE WHEN (sucursal @> array[' || p_sucursal_id ||
                 ']) AND cardinality(sucursal) = 1 THEN now() ELSE soft_delete_date END';
     END IF;
v_sql := v_sql || ' WHERE business_id = $1 AND sucursal @> array[' || p_sucursal_id || ']';
     EXECUTE v_sql USING p_business_id;
 END LOOP;
END;
$function$;

CREATE OR REPLACE FUNCTION public.hhmm_to_time(p_hhmm text)
 RETURNS time without time zone
 LANGUAGE sql
 IMMUTABLE
AS $function$
SELECT make_time((substring(p_hhmm,1,2))::int,
                 (substring(p_hhmm,3,2))::int,
                 0);
$function$;

CREATE OR REPLACE FUNCTION public.jornada_json_to_ranges(p_jornada jsonb, p_day date, p_tz interval DEFAULT '-06:00:00'::interval)
 RETURNS SETOF tstzrange
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
DECLARE
    elem  jsonb;

dow   text := extract(isodow FROM p_day)::int::text;
    r_ini timestamptz;
    r_fin timestamptz;
BEGIN
    IF p_jornada IS NULL THEN
        RETURN;
    END IF;


FOR elem IN SELECT * FROM jsonb_array_elements(p_jornada) LOOP
        IF elem->'horario' ? dow THEN
            r_ini := (p_day + hhmm_to_time((elem->'horario'->dow->>0))) AT TIME ZONE p_tz;
            r_fin := (p_day + hhmm_to_time((elem->'horario'->dow->>1))) AT TIME ZONE p_tz;
            IF r_ini < r_fin THEN
                RETURN NEXT tstzrange(r_ini,r_fin,'[]');
            END IF;
        END IF;
    END LOOP;
END;
$function$;

CREATE OR REPLACE FUNCTION public.manual_sync_disponibilidad()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    rec RECORD;

BEGIN
    FOR rec IN SELECT id_login, business_id, soft_delete FROM login LOOP
        INSERT INTO disponibilidad_usuarios(
            id_login,
            business_id,
            soft_delete,
            soft_delete_date,
            disp_confirmada,
            jornada_usuario,
            jornada_sucursal,
            disponibilidad_trabajo_extra
        )
        VALUES(
            rec.id_login,
            rec.business_id,
            rec.soft_delete,
            CASE WHEN rec.soft_delete THEN NOW() ELSE NULL END,
            '[]'::jsonb,
            '[]'::jsonb,
            '[]'::jsonb,
            '[]'::jsonb
        )
        ON CONFLICT (id_login) DO UPDATE
            SET soft_delete      = EXCLUDED.soft_delete,
                soft_delete_date = EXCLUDED.soft_delete_date,
                business_id      = EXCLUDED.business_id;
        PERFORM refresca_jornadas(rec.id_login);
        PERFORM fill_jornada_sucursal_from_login(rec.id_login);

END LOOP;

UPDATE disponibilidad_usuarios d
    SET disp_confirmada = COALESCE(
        (
            SELECT jsonb_agg(
                jsonb_build_array(
                    jsonb_build_array('excepciones_vacaciones', format('PK:%s', e.id)),
                    jsonb_build_array(
                        to_char(e.inicio,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                        to_char(e.fin,'YYYY-MM-DD"T"HH24:MI:SSOF')
                    )
                )
            )
            FROM excepciones_vacaciones e
            WHERE e.id_login = d.id_login AND e.soft_delete = false AND e.tipo IN (1,3)
        ),
        '[]'::jsonb
    );

UPDATE disponibilidad_usuarios d
    SET disponibilidad_trabajo_extra = COALESCE(
        (
            SELECT jsonb_agg(
                jsonb_build_array(
                    jsonb_build_array('excepciones_vacaciones', format('PK:%s', e.id)),
                    jsonb_build_array(
                        to_char(e.inicio,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                        to_char(e.fin,'YYYY-MM-DD"T"HH24:MI:SSOF')
                    )
                )
            )
            FROM excepciones_vacaciones e
            WHERE e.id_login = d.id_login AND e.soft_delete = false AND e.tipo = 2
        ),
        '[]'::jsonb
    );
END;
$function$;

CREATE OR REPLACE FUNCTION public.verificar_disponibilidad_periodos(p_business_id character varying)
 RETURNS TABLE(id_login integer, disp_confirmada jsonb, jornada_usuario jsonb, jornada_sucursal jsonb, disponibilidad_trabajo_extra jsonb, business_id character varying, soft_delete boolean, soft_delete_date timestamp with time zone)
 LANGUAGE sql
 STABLE
AS $function$
SELECT *
FROM workforce.verificar_disponibilidad_periodos(p_business_id);
$function$;

CREATE OR REPLACE FUNCTION public.verificar_disponibilidad_periodos(p_business_id character varying, p_inicio timestamp with time zone, p_fin timestamp with time zone)
 RETURNS TABLE(id_login integer, disp_confirmada jsonb, jornada_usuario jsonb, jornada_sucursal jsonb, disponibilidad_trabajo_extra jsonb, business_id character varying, soft_delete boolean, soft_delete_date timestamp with time zone)
 LANGUAGE sql
 STABLE
AS $function$
SELECT *
FROM workforce.verificar_disponibilidad_periodos(p_business_id, p_inicio, p_fin);
$function$;

CREATE OR REPLACE FUNCTION public.refresca_jornadas(p_login integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_rows jsonb;
BEGIN
    SELECT jsonb_agg(jsonb_build_object(
             'horario', u.horario,
             'sucursal', s.suc
           ))
    INTO v_rows
    FROM usuarios u
    CROSS JOIN LATERAL unnest(u.sucursal) AS s(suc)
    WHERE u.id_login = p_login
      AND COALESCE(u.soft_delete, false) = false;
    UPDATE disponibilidad_usuarios
    SET jornada_usuario = COALESCE(v_rows, '[]'::jsonb)
    WHERE id_login = p_login;

END;
$function$;

CREATE OR REPLACE FUNCTION public.refresca_jornadas_sucursal(p_login integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_business varchar(100);
    v_rows jsonb;
BEGIN
    SELECT business_id
    INTO v_business
    FROM usuarios
    WHERE id_login = p_login;
    SELECT jsonb_agg(jsonb_build_object(
             'horario', cfg.horario,
             'sucursal', s
           ))
    INTO v_rows
    FROM configuraciones cfg
    CROSS JOIN LATERAL unnest(cfg.sucursal) AS s
    WHERE cfg.business_id = v_business
      AND COALESCE(cfg.soft_delete,false) = false;
    UPDATE disponibilidad_usuarios
    SET jornada_sucursal = COALESCE(v_rows,'[]'::jsonb)
    WHERE id_login = p_login;

END;
$function$;

CREATE OR REPLACE FUNCTION public.refresca_jornadas_trigger()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    sucs integer[];
    v_business varchar(100);
rec record;
BEGIN
    IF TG_OP = 'DELETE' THEN
        sucs := OLD.sucursal;
        v_business := OLD.business_id;
    ELSE
        sucs := NEW.sucursal;
        v_business := NEW.business_id;
    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 = v_business
          AND COALESCE(u.soft_delete, false) = false
          AND u.sucursal && sucs
    LOOP
        PERFORM refresca_jornadas(rec.id_login);
END LOOP;
    RETURN NULL;
END;
$function$;

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

CREATE OR REPLACE FUNCTION public.sync_availability_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    meta jsonb;
    times jsonb;
    block jsonb;
BEGIN
    IF TG_OP <> 'INSERT' AND OLD.id_login_asignado IS NOT NULL THEN
        meta := jsonb_build_array('citas', format('PK:%s', OLD.id_cita));
        times := jsonb_build_array(
            to_char(OLD.inicio_cita,'YYYY-MM-DD"T"HH24:MI:SSOF'),
            to_char(OLD.fin_cita,'YYYY-MM-DD"T"HH24:MI:SSOF')
        );
        block := jsonb_build_array(meta, times);
        UPDATE disponibilidad_usuarios
        SET disp_confirmada = COALESCE(
            (SELECT jsonb_agg(e)
             FROM jsonb_array_elements(disp_confirmada) e
             WHERE e->0 <> meta),
            '[]'::jsonb
        )
        WHERE id_login = OLD.id_login_asignado;
    END IF;
    IF TG_OP <> 'DELETE' AND NEW.id_login_asignado IS NOT NULL THEN
        meta := jsonb_build_array('citas', format('PK:%s', NEW.id_cita));
        times := jsonb_build_array(
            to_char(NEW.inicio_cita,'YYYY-MM-DD"T"HH24:MI:SSOF'),
            to_char(NEW.fin_cita,'YYYY-MM-DD"T"HH24:MI:SSOF')
        );
        block := jsonb_build_array(meta, times);
        UPDATE disponibilidad_usuarios
        SET disp_confirmada = (
            SELECT COALESCE(jsonb_agg(e),'[]'::jsonb)
            FROM (
                SELECT e
                FROM jsonb_array_elements(disp_confirmada) e
                WHERE e->0 <> meta
                UNION ALL
                SELECT block
            ) u
        )
        WHERE id_login = NEW.id_login_asignado;
    END IF;
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.sync_excepcion_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    meta  jsonb;
    block jsonb;
    rec_id integer := COALESCE(NEW.id, OLD.id);

BEGIN
    IF TG_OP <> 'INSERT'
       AND OLD.id_login IS NOT NULL
       AND OLD.soft_delete = false
       AND OLD.tipo IN (1,3) THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', rec_id));
        EXECUTE format(
            'UPDATE disponibilidad_usuarios
             SET disp_confirmada =
                 COALESCE((SELECT jsonb_agg(e)
                           FROM (SELECT e
                                 FROM jsonb_array_elements(disp_confirmada) e
                                 WHERE e->0 <> $1) s), ''[]''::jsonb)
             WHERE id_login = $2'
        ) USING meta, OLD.id_login;


END IF;
IF TG_OP <> 'DELETE'
       AND NEW.id_login IS NOT NULL
       AND NEW.soft_delete = false
       AND NEW.tipo IN (1,3) THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', rec_id));
        block := jsonb_build_array(
                    meta,
                    jsonb_build_array(
                        to_char(NEW.inicio,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                        to_char(NEW.fin,   'YYYY-MM-DD"T"HH24:MI:SSOF')
                    )
                 );

EXECUTE format(
            'UPDATE disponibilidad_usuarios
             SET disp_confirmada =
                 (SELECT COALESCE(jsonb_agg(e), ''[]''::jsonb)
                  FROM (SELECT e
                        FROM jsonb_array_elements(disp_confirmada) e
                        WHERE e->0 <> $1
                        UNION ALL
                        SELECT $2) z)
             WHERE id_login = $3'
        ) USING meta, block, NEW.id_login;
    END IF;
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.sync_login_dispo()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
    IF TG_OP = 'DELETE' THEN
        DELETE FROM disponibilidad_usuarios
        WHERE id_login = OLD.id_login;
RETURN NULL;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO disponibilidad_usuarios(
            id_login,
            business_id,
            soft_delete,
            soft_delete_date,
            disp_confirmada,
            jornada_usuario,
            jornada_sucursal,
            disponibilidad_trabajo_extra
        )
        VALUES (
            NEW.id_login,
            NEW.business_id,
            NEW.soft_delete,
            CASE WHEN NEW.soft_delete THEN NOW() ELSE NULL END,
            '[]'::jsonb,
            '[]'::jsonb,
            '[]'::jsonb,
            '[]'::jsonb
        )
        ON CONFLICT (id_login) DO UPDATE
            SET soft_delete      = EXCLUDED.soft_delete,
                soft_delete_date = EXCLUDED.soft_delete_date,
                business_id      = EXCLUDED.business_id;
        PERFORM refresca_jornadas(NEW.id_login);
        PERFORM fill_jornada_sucursal_from_login(NEW.id_login);
        RETURN NEW;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO disponibilidad_usuarios(
            id_login,
            business_id,
            soft_delete,
            soft_delete_date,
            disp_confirmada,
            jornada_usuario,
            jornada_sucursal,
            disponibilidad_trabajo_extra
        )
        VALUES (
            NEW.id_login,
            NEW.business_id,
            NEW.soft_delete,
            CASE WHEN NEW.soft_delete THEN NOW() ELSE NULL END,
            '[]'::jsonb,
            '[]'::jsonb,
            '[]'::jsonb,
            '[]'::jsonb
        )
        ON CONFLICT (id_login) DO UPDATE
            SET soft_delete      = EXCLUDED.soft_delete,
                soft_delete_date = EXCLUDED.soft_delete_date,
                business_id      = EXCLUDED.business_id;
        PERFORM refresca_jornadas(NEW.id_login);
        PERFORM fill_jornada_sucursal_from_login(NEW.id_login);
        RETURN NEW;
    END IF;
END;$function$;

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

CREATE OR REPLACE FUNCTION public.trig_refresca_disp_disponible()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    meta  jsonb;
    times jsonb;
    block jsonb;
BEGIN
    IF TG_TABLE_NAME = 'citas' THEN
        IF TG_OP <> 'INSERT' AND OLD.id_login_asignado IS NOT NULL THEN
            meta    := jsonb_build_array('citas', format('PK:%s', OLD.id_cita));
            times   := jsonb_build_array(
                         to_char(OLD.inicio_cita,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                         to_char(OLD.fin_cita,'YYYY-MM-DD"T"HH24:MI:SSOF')
                       );
            block   := jsonb_build_array(meta, times);
            UPDATE disponibilidad_usuarios
            SET disp_confirmada = COALESCE(
                  (SELECT jsonb_agg(e)
                   FROM (SELECT e FROM jsonb_array_elements(disp_confirmada) e
                         WHERE e->0 <> meta) s),
                  '[]'::jsonb
                )
            WHERE id_login = OLD.id_login_asignado;
        END IF;
        IF TG_OP <> 'DELETE' AND NEW.id_login_asignado IS NOT NULL THEN
            meta    := jsonb_build_array('citas', format('PK:%s', NEW.id_cita));
            times   := jsonb_build_array(
                         to_char(NEW.inicio_cita,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                         to_char(NEW.fin_cita,'YYYY-MM-DD"T"HH24:MI:SSOF')
                       );
            block   := jsonb_build_array(meta, times);
            UPDATE disponibilidad_usuarios
            SET disp_confirmada = (
                  SELECT COALESCE(jsonb_agg(e), '[]'::jsonb)
                  FROM (
                    SELECT e
                    FROM jsonb_array_elements(disp_confirmada) e
                    WHERE e->0 <> meta
                    UNION ALL
                    SELECT block
                  ) s
                )
            WHERE id_login = NEW.id_login_asignado;
        END IF;
    ELSE
        IF TG_OP = 'DELETE' AND OLD.tipo IN (1,3) THEN
            meta    := jsonb_build_array('excepciones_vacaciones', format('PK:%s', OLD.id));
            times   := jsonb_build_array(
                         to_char(OLD.inicio,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                         to_char(OLD.fin,'YYYY-MM-DD"T"HH24:MI:SSOF')
                       );
            block   := jsonb_build_array(meta, times);
            UPDATE disponibilidad_usuarios
            SET disp_confirmada = COALESCE(
                  (SELECT jsonb_agg(e)
                   FROM (SELECT e FROM jsonb_array_elements(disp_confirmada) e
                         WHERE e->0 <> meta) s),
                  '[]'::jsonb
                )
            WHERE id_login = OLD.id_login;
        ELSIF TG_OP <> 'DELETE' AND NEW.tipo IN (1,3) THEN
            meta    := jsonb_build_array('excepciones_vacaciones', format('PK:%s', NEW.id));
            times   := jsonb_build_array(
                         to_char(NEW.inicio,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                         to_char(NEW.fin,'YYYY-MM-DD"T"HH24:MI:SSOF')
                       );
            block   := jsonb_build_array(meta, times);
            UPDATE disponibilidad_usuarios
            SET disp_confirmada = (
                  SELECT COALESCE(jsonb_agg(e), '[]'::jsonb)
                  FROM (
                    SELECT e
                    FROM jsonb_array_elements(disp_confirmada) e
                    WHERE e->0 <> meta
                    UNION ALL
                    SELECT block
                  ) s
                )
            WHERE id_login = NEW.id_login;
        END IF;
    END IF;
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.trig_refresca_disp_exc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    meta      jsonb;
    rec_id    integer := COALESCE(NEW.id, OLD.id);
    rec_login integer := COALESCE(NEW.id_login, OLD.id_login);

BEGIN
    IF (TG_OP = 'DELETE'
        OR (TG_OP = 'UPDATE' AND OLD.soft_delete = false AND NEW.soft_delete = true))
       AND OLD.tipo IN (1,3) THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', rec_id));
        UPDATE disponibilidad_usuarios
        SET disp_confirmada =
            COALESCE(
                (SELECT jsonb_agg(e)
                 FROM jsonb_array_elements(disp_confirmada) e
                 WHERE e->0 <> meta),
                '[]'::jsonb)
        WHERE id_login = rec_login;
        RETURN NULL;
    END IF;

IF (TG_OP = 'INSERT'
        OR (TG_OP = 'UPDATE' AND NEW.soft_delete = false))
       AND NEW.tipo IN (1,3) THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', NEW.id));
        UPDATE disponibilidad_usuarios
        SET disp_confirmada =
            (SELECT COALESCE(jsonb_agg(e), '[]'::jsonb)
             FROM (
                 SELECT e
                 FROM jsonb_array_elements(disp_confirmada) e
                 WHERE e->0 <> meta
                 UNION ALL
                 SELECT jsonb_build_array(
                            meta,
                            jsonb_build_array(
                                to_char(NEW.inicio,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                                to_char(NEW.fin,   'YYYY-MM-DD"T"HH24:MI:SSOF')
                            )
                        )
             ) s)
        WHERE id_login = NEW.id_login;

RETURN NULL;
    END IF;
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.trig_refresca_disp_trabajo()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    meta jsonb;
    times jsonb;
    block jsonb;
BEGIN
    IF TG_OP = 'DELETE' AND OLD.tipo = 2 THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', OLD.id));
        times := jsonb_build_array(
            to_char(OLD.inicio, 'YYYY-MM-DD"T"HH24:MI:SSOF'),
            to_char(OLD.fin,    'YYYY-MM-DD"T"HH24:MI:SSOF')
        );
        block := jsonb_build_array(meta, times);
        UPDATE disponibilidad_usuarios
        SET disponibilidad_trabajo_extra = COALESCE(
            (
                SELECT jsonb_agg(e)
                FROM (
                    SELECT e
                    FROM jsonb_array_elements(disponibilidad_trabajo_extra) e
                    WHERE e->0 <> meta
                ) s
            ),
            '[]'::jsonb
        )
        WHERE id_login = OLD.id_login;
    ELSIF TG_OP <> 'DELETE' AND NEW.tipo = 2 THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', NEW.id));
        times := jsonb_build_array(
            to_char(NEW.inicio, 'YYYY-MM-DD"T"HH24:MI:SSOF'),
            to_char(NEW.fin,    'YYYY-MM-DD"T"HH24:MI:SSOF')
        );
        block := jsonb_build_array(meta, times);
        UPDATE disponibilidad_usuarios
        SET disponibilidad_trabajo_extra = (
            SELECT COALESCE(jsonb_agg(e), '[]'::jsonb)
            FROM (
                SELECT e
                FROM jsonb_array_elements(disponibilidad_trabajo_extra) e
                WHERE e->0 <> meta
                UNION ALL
                SELECT block
            ) s
        )
        WHERE id_login = NEW.id_login;
    END IF;
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.trig_refresca_jornadas_usuarios()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
    IF TG_OP = 'DELETE' THEN
        DELETE FROM disponibilidad_usuarios WHERE id_login = OLD.id_login;
RETURN NULL;
    END IF;
    PERFORM refresca_jornadas(NEW.id_login);
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.trig_refresca_trabajo_exc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    meta      jsonb;
    rec_id    integer := COALESCE(NEW.id, OLD.id);
    rec_login integer := COALESCE(NEW.id_login, OLD.id_login);

BEGIN
    IF (TG_OP = 'DELETE'
        OR (TG_OP = 'UPDATE' AND OLD.soft_delete = false AND NEW.soft_delete = true))
       AND OLD.tipo = 2 THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', rec_id));
        UPDATE disponibilidad_usuarios
        SET disponibilidad_trabajo_extra =
            COALESCE(
                (SELECT jsonb_agg(e)
                 FROM jsonb_array_elements(disponibilidad_trabajo_extra) e
                 WHERE e->0 <> meta),
                '[]'::jsonb)
        WHERE id_login = rec_login;
        RETURN NULL;
    END IF;

IF (TG_OP = 'INSERT'
        OR (TG_OP = 'UPDATE' AND NEW.soft_delete = false))
       AND NEW.tipo = 2 THEN
        meta := jsonb_build_array('excepciones_vacaciones', format('PK:%s', NEW.id));
        UPDATE disponibilidad_usuarios
        SET disponibilidad_trabajo_extra =
            (SELECT COALESCE(jsonb_agg(e), '[]'::jsonb)
             FROM (
                 SELECT e
                 FROM jsonb_array_elements(disponibilidad_trabajo_extra) e
                 WHERE e->0 <> meta
                 UNION ALL
                 SELECT jsonb_build_array(
                            meta,
                            jsonb_build_array(
                                to_char(NEW.inicio,'YYYY-MM-DD"T"HH24:MI:SSOF'),
                                to_char(NEW.fin,   'YYYY-MM-DD"T"HH24:MI:SSOF')
                            )
                        )
             ) s)
        WHERE id_login = NEW.id_login;

RETURN NULL;
    END IF;
    RETURN NULL;
END;$function$;

CREATE OR REPLACE FUNCTION public.trig_sync_autorizaciones()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE
    v_auth integer[];
BEGIN
    IF NEW.rol IS DISTINCT FROM OLD.rol THEN
        SELECT CASE NEW.rol
                 WHEN 1 THEN c.autorizaciones_administrador
                 WHEN 2 THEN c.autorizaciones_gerente
                 WHEN 3 THEN c.autorizaciones_empleado
               END
          INTO v_auth
          FROM configuraciones c
         WHERE c.business_id = NEW.business_id
           AND COALESCE(c.soft_delete,false)=false
           AND NEW.sucursal[1] = ANY (c.sucursal)
         LIMIT 1;

        NEW.autorizaciones := v_auth;
    END IF;

    RETURN NEW;
END;$function$;

SELECT pg_temp.restore_compat_table('public_legacy', 'auth', 'auth_remember_tokens', 'id', 'public_legacy', 'auth_remember_tokens_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'auth', 'codigos_activacion', 'id', 'public_legacy', 'codigos_activacion_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'auth', 'login', 'id_login');
SELECT pg_temp.restore_compat_table('public_legacy', 'auth', 'login_attempt_tracker');
SELECT pg_temp.restore_compat_table('public_legacy', 'workforce', 'disponibilidad_usuarios', 'id_login');
SELECT pg_temp.restore_compat_table('public_legacy', 'workforce', 'excepciones_vacaciones', 'id', 'public_legacy', 'excepciones_vacaciones_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'workforce', 'usuarios', 'id_usuario', 'public_legacy', 'usuarios_id_usuario_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'messaging', 'numeros_whatsapp_business', 'id', 'public_legacy', 'numeros_whatsapp_business_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'messaging', 'whatsapp_templates', 'id', 'public_legacy', 'whatsapp_templates_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'messaging', 'whatsapp_templates_for_staff', 'id', 'public_legacy', 'whatsapp_templates_for_staff_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'citas', 'id_cita', 'public_legacy', 'citas_id_cita_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'comprobantes_transacciones', 'id', 'public_legacy', 'comprobantes_transacciones_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'configuraciones_planes_pago', 'id', 'public_legacy', 'configuraciones_planes_pago_id_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'modelos_planes_pago', 'id_modelo', 'public_legacy', 'modelos_planes_pago_id_modelo_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'pagos_planes', 'id_pago', 'public_legacy', 'pagos_planes_id_pago_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'planes_pago', 'id_plan', 'public_legacy', 'planes_pago_id_plan_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'transacciones', 'id_transaccion', 'public_legacy', 'transacciones_id_transaccion_seq');
SELECT pg_temp.restore_compat_table('public_legacy', 'commerce', 'ventas', 'id_venta', 'public_legacy', 'ventas_id_venta_seq');
SELECT pg_temp.restore_compat_table('public', 'testing', 'backend_agent_test', 'id_test', 'public', 'backend_agent_test_id_test_seq');

ALTER TABLE IF EXISTS public_legacy.auth_remember_tokens
    DROP CONSTRAINT IF EXISTS auth_remember_tokens_login_id_fkey;
ALTER TABLE IF EXISTS public_legacy.auth_remember_tokens
    ADD CONSTRAINT auth_remember_tokens_login_id_fkey
    FOREIGN KEY (login_id)
    REFERENCES public_legacy.login(id_login)
    ON DELETE CASCADE
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.auth_remember_tokens
    VALIDATE CONSTRAINT auth_remember_tokens_login_id_fkey;

ALTER TABLE IF EXISTS public_legacy.disponibilidad_usuarios
    DROP CONSTRAINT IF EXISTS fk_dispo_login;
ALTER TABLE IF EXISTS public_legacy.disponibilidad_usuarios
    ADD CONSTRAINT fk_dispo_login
    FOREIGN KEY (id_login)
    REFERENCES public_legacy.login(id_login)
    ON DELETE CASCADE
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.disponibilidad_usuarios
    VALIDATE CONSTRAINT fk_dispo_login;

ALTER TABLE IF EXISTS public_legacy.comprobantes_transacciones
    DROP CONSTRAINT IF EXISTS fk_comprobante_transaccion;
ALTER TABLE IF EXISTS public_legacy.comprobantes_transacciones
    ADD CONSTRAINT fk_comprobante_transaccion
    FOREIGN KEY (id_transaccion)
    REFERENCES public_legacy.transacciones(id_transaccion)
    ON DELETE CASCADE
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.comprobantes_transacciones
    VALIDATE CONSTRAINT fk_comprobante_transaccion;

ALTER TABLE IF EXISTS public_legacy.comprobantes_transacciones
    DROP CONSTRAINT IF EXISTS fk_comprobantes_venta;
ALTER TABLE IF EXISTS public_legacy.comprobantes_transacciones
    ADD CONSTRAINT fk_comprobantes_venta
    FOREIGN KEY (id_venta)
    REFERENCES public_legacy.ventas(id_venta)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.comprobantes_transacciones
    VALIDATE CONSTRAINT fk_comprobantes_venta;

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 crm.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.pagos_planes
    DROP CONSTRAINT IF EXISTS fk_pago_plan;
ALTER TABLE IF EXISTS public_legacy.pagos_planes
    ADD CONSTRAINT fk_pago_plan
    FOREIGN KEY (id_plan_pago)
    REFERENCES public_legacy.planes_pago(id_plan)
    ON DELETE RESTRICT
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.pagos_planes
    VALIDATE CONSTRAINT fk_pago_plan;

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 crm.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.planes_pago
    DROP CONSTRAINT IF EXISTS fk_planes_pago_transaccion;
ALTER TABLE IF EXISTS public_legacy.planes_pago
    ADD CONSTRAINT fk_planes_pago_transaccion
    FOREIGN KEY (id_transaccion_origen)
    REFERENCES public_legacy.transacciones(id_transaccion)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.planes_pago
    VALIDATE CONSTRAINT fk_planes_pago_transaccion;

ALTER TABLE IF EXISTS public_legacy.planes_pago
    DROP CONSTRAINT IF EXISTS fk_planes_pago_venta;
ALTER TABLE IF EXISTS public_legacy.planes_pago
    ADD CONSTRAINT fk_planes_pago_venta
    FOREIGN KEY (id_venta)
    REFERENCES public_legacy.ventas(id_venta)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.planes_pago
    VALIDATE CONSTRAINT fk_planes_pago_venta;

ALTER TABLE IF EXISTS public_legacy.transacciones
    DROP CONSTRAINT IF EXISTS fk_transacciones_plan_pago;
ALTER TABLE IF EXISTS public_legacy.transacciones
    ADD CONSTRAINT fk_transacciones_plan_pago
    FOREIGN KEY (id_plan_pago)
    REFERENCES public_legacy.planes_pago(id_plan)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.transacciones
    VALIDATE CONSTRAINT fk_transacciones_plan_pago;

ALTER TABLE IF EXISTS public_legacy.transacciones
    DROP CONSTRAINT IF EXISTS fk_transacciones_venta;
ALTER TABLE IF EXISTS public_legacy.transacciones
    ADD CONSTRAINT fk_transacciones_venta
    FOREIGN KEY (id_venta)
    REFERENCES public_legacy.ventas(id_venta)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.transacciones
    VALIDATE CONSTRAINT fk_transacciones_venta;

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 crm.clientes(id_cliente)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.ventas
    VALIDATE CONSTRAINT fk_ventas_cliente;

ALTER TABLE IF EXISTS public_legacy.ventas
    DROP CONSTRAINT IF EXISTS fk_ventas_plan_pago;
ALTER TABLE IF EXISTS public_legacy.ventas
    ADD CONSTRAINT fk_ventas_plan_pago
    FOREIGN KEY (id_plan_pago)
    REFERENCES public_legacy.planes_pago(id_plan)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE IF EXISTS public_legacy.ventas
    VALIDATE CONSTRAINT fk_ventas_plan_pago;

SELECT pg_temp.restore_public_view('auth_remember_tokens', 'auth');
SELECT pg_temp.restore_public_view('codigos_activacion', 'auth');
SELECT pg_temp.restore_public_view('login', 'auth');
SELECT pg_temp.restore_public_view('login_attempt_tracker', 'auth');
SELECT pg_temp.restore_public_view('disponibilidad_usuarios', 'workforce');
SELECT pg_temp.restore_public_view('excepciones_vacaciones', 'workforce');
SELECT pg_temp.restore_public_view('usuarios', 'workforce');
SELECT pg_temp.restore_public_view('numeros_whatsapp_business', 'messaging');
SELECT pg_temp.restore_public_view('whatsapp_templates', 'messaging');
SELECT pg_temp.restore_public_view('whatsapp_templates_for_staff', 'messaging');
SELECT pg_temp.restore_public_view('citas', 'commerce');
SELECT pg_temp.restore_public_view('comprobantes_transacciones', 'commerce');
SELECT pg_temp.restore_public_view('configuraciones_planes_pago', 'commerce');
SELECT pg_temp.restore_public_view('modelos_planes_pago', 'commerce');
SELECT pg_temp.restore_public_view('pagos_planes', 'commerce');
SELECT pg_temp.restore_public_view('planes_pago', 'commerce');
SELECT pg_temp.restore_public_view('transacciones', 'commerce');
SELECT pg_temp.restore_public_view('ventas', 'commerce');

CREATE TRIGGER trg_refresca_disp_citas AFTER INSERT OR DELETE OR UPDATE ON public_legacy.citas FOR EACH ROW EXECUTE FUNCTION trig_refresca_disp_disponible();
CREATE TRIGGER trg_sync_availability_user AFTER INSERT OR DELETE OR UPDATE ON public_legacy.citas FOR EACH ROW EXECUTE FUNCTION sync_availability_user();
CREATE TRIGGER trg_refresca_disp_exc AFTER INSERT OR DELETE OR UPDATE ON public_legacy.excepciones_vacaciones FOR EACH ROW EXECUTE FUNCTION trig_refresca_disp_exc();
CREATE TRIGGER trg_refresca_trabajo_exc AFTER INSERT OR DELETE OR UPDATE ON public_legacy.excepciones_vacaciones FOR EACH ROW EXECUTE FUNCTION trig_refresca_trabajo_exc();
CREATE TRIGGER trg_sync_excepcion_user AFTER INSERT OR DELETE OR UPDATE ON public_legacy.excepciones_vacaciones FOR EACH ROW EXECUTE FUNCTION sync_excepcion_user();
CREATE TRIGGER trg_sync_login_dispo AFTER INSERT OR DELETE OR UPDATE OF soft_delete, business_id ON public_legacy.login FOR EACH ROW EXECUTE FUNCTION sync_login_dispo();
CREATE TRIGGER trg_modelos_pp_updated_at BEFORE UPDATE ON public_legacy.modelos_planes_pago FOR EACH ROW EXECUTE FUNCTION set_updated_at_timestamp();
CREATE TRIGGER trg_planes_pago_updated_at BEFORE UPDATE ON public_legacy.planes_pago FOR EACH ROW EXECUTE FUNCTION set_updated_at_timestamp();
CREATE TRIGGER trg_refresca_jornadas_usuarios AFTER INSERT OR DELETE OR UPDATE ON public_legacy.usuarios FOR EACH ROW EXECUTE FUNCTION trig_refresca_jornadas_usuarios();
CREATE TRIGGER trg_sync_autorizaciones BEFORE INSERT OR UPDATE OF rol ON public_legacy.usuarios FOR EACH ROW EXECUTE FUNCTION trig_sync_autorizaciones();
CREATE TRIGGER trg_ventas_updated_at BEFORE UPDATE ON public_legacy.ventas FOR EACH ROW EXECUTE FUNCTION trigger_set_updated_at();

COMMIT;
