BEGIN;

ALTER TABLE auth.login
    ADD COLUMN IF NOT EXISTS authz_version bigint;

UPDATE auth.login
SET authz_version = 1
WHERE authz_version IS NULL;

ALTER TABLE auth.login
    ALTER COLUMN authz_version SET DEFAULT 1;

ALTER TABLE auth.login
    ALTER COLUMN authz_version SET NOT NULL;

COMMENT ON COLUMN auth.login.authz_version IS 'Version monotona de autorizacion por login. Se incrementa cuando cambian founder, scope o permisos efectivos para invalidar cache de autorizaciones sin logout.';

CREATE TABLE IF NOT EXISTS auth.permission_catalog (
    permission_code integer PRIMARY KEY,
    category_id integer NOT NULL CHECK (category_id BETWEEN 1 AND 13),
    action_id integer NOT NULL CHECK (action_id BETWEEN 1 AND 4),
    category_key text NOT NULL,
    category_label text NOT NULL,
    action_key text NOT NULL,
    action_label text NOT NULL,
    display_order integer NOT NULL,
    active boolean NOT NULL DEFAULT true,
    legacy_observed boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT permission_catalog_permission_code_format_chk
        CHECK (
            permission_code = (category_id * 10 + action_id)
            AND permission_code BETWEEN 11 AND 134
        )
);

COMMENT ON TABLE auth.permission_catalog IS 'Catalogo canonico de permisos RBAC del ERP. Congela la semantica permission_code -> categoria + accion para backend, runtime y UIs.';
COMMENT ON COLUMN auth.permission_catalog.permission_code IS 'Codigo entero canonico de permiso (ej. 53 = Configuracion Punto de Venta / Actualizar).';
COMMENT ON COLUMN auth.permission_catalog.category_id IS 'Categoria canonica del permiso.';
COMMENT ON COLUMN auth.permission_catalog.action_id IS 'Accion CRUD canonica del permiso.';
COMMENT ON COLUMN auth.permission_catalog.category_key IS 'Clave estable kebab/snake-friendly de la categoria.';
COMMENT ON COLUMN auth.permission_catalog.category_label IS 'Etiqueta visible de la categoria.';
COMMENT ON COLUMN auth.permission_catalog.action_key IS 'Clave estable de la accion.';
COMMENT ON COLUMN auth.permission_catalog.action_label IS 'Etiqueta visible de la accion.';
COMMENT ON COLUMN auth.permission_catalog.display_order IS 'Orden canonico de render/recorrido en UI y QA.';
COMMENT ON COLUMN auth.permission_catalog.active IS 'TRUE cuando el permiso forma parte del catalogo vigente.';
COMMENT ON COLUMN auth.permission_catalog.legacy_observed IS 'TRUE si el permiso ya existia en arrays legacy observados en platform.configuraciones/workforce.usuarios al momento del backfill inicial.';

CREATE INDEX IF NOT EXISTS permission_catalog_active_display_order_idx
    ON auth.permission_catalog (active, display_order);

WITH categories (category_id, category_key, category_label, legacy_observed) AS (
    VALUES
        (1,  'ventas', 'Ventas', true),
        (2,  'reportes', 'Reportes', true),
        (3,  'clientes', 'Clientes', true),
        (4,  'inventario', 'Inventario', true),
        (5,  'configuracion_punto_venta', 'Configuracion Punto de Venta', true),
        (6,  'configuracion_empleado_digital', 'Configuracion Empleado Digital', true),
        (7,  'personal', 'Personal', true),
        (8,  'informacion_negocio_empleado_digital', 'Informacion del Negocio Empleado Digital', true),
        (9,  'capacidades_empleado_digital', 'Capacidades del Empleado Digital', true),
        (10, 'personalizacion_empleado_digital', 'Personalizacion del Empleado Digital', true),
        (11, 'pedidos_citas', 'Pedidos y Citas', true),
        (12, 'meta_plantillas', 'Meta Plantillas', true),
        (13, 'firmas_personal', 'Firmas del Personal', false)
),
actions (action_id, action_key, action_label) AS (
    VALUES
        (1, 'create', 'Crear'),
        (2, 'view', 'Ver'),
        (3, 'update', 'Actualizar'),
        (4, 'delete', 'Eliminar')
)
INSERT INTO auth.permission_catalog (
    permission_code,
    category_id,
    action_id,
    category_key,
    category_label,
    action_key,
    action_label,
    display_order,
    active,
    legacy_observed
)
SELECT
    (c.category_id * 10 + a.action_id) AS permission_code,
    c.category_id,
    a.action_id,
    c.category_key,
    c.category_label,
    a.action_key,
    a.action_label,
    ((c.category_id - 1) * 4) + a.action_id AS display_order,
    true AS active,
    c.legacy_observed
FROM categories c
CROSS JOIN actions a
ON CONFLICT (permission_code) DO UPDATE
SET
    category_id = EXCLUDED.category_id,
    action_id = EXCLUDED.action_id,
    category_key = EXCLUDED.category_key,
    category_label = EXCLUDED.category_label,
    action_key = EXCLUDED.action_key,
    action_label = EXCLUDED.action_label,
    display_order = EXCLUDED.display_order,
    active = EXCLUDED.active,
    legacy_observed = EXCLUDED.legacy_observed,
    updated_at = CURRENT_TIMESTAMP;

CREATE TABLE IF NOT EXISTS auth.role_permission_profiles (
    profile_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    business_id varchar(100) NOT NULL,
    sucursal_id integer NOT NULL CHECK (sucursal_id > 0),
    role_id integer NOT NULL CHECK (role_id IN (1, 2, 3)),
    role_key text NOT NULL,
    source_platform_role_id integer NOT NULL,
    source_table text NOT NULL DEFAULT 'platform.configuraciones',
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT role_permission_profiles_scope_uq UNIQUE (business_id, sucursal_id, role_id)
);

COMMENT ON TABLE auth.role_permission_profiles IS 'Perfil canonico base por negocio, sucursal y rol. Se backfillea desde platform.configuraciones y queda listo para consumo backend.';
COMMENT ON COLUMN auth.role_permission_profiles.business_id IS 'Tenant del negocio.';
COMMENT ON COLUMN auth.role_permission_profiles.sucursal_id IS 'Sucursal canonica del perfil.';
COMMENT ON COLUMN auth.role_permission_profiles.role_id IS 'Rol canonico: 1 administrador, 2 gerente, 3 empleado.';
COMMENT ON COLUMN auth.role_permission_profiles.role_key IS 'Clave estable derivada del role_id.';
COMMENT ON COLUMN auth.role_permission_profiles.source_platform_role_id IS 'Valor legacy de platform.configuraciones.role_id asociado a la fila fuente.';
COMMENT ON COLUMN auth.role_permission_profiles.source_table IS 'Tabla legacy usada para backfill y compat temporal.';

CREATE INDEX IF NOT EXISTS role_permission_profiles_business_role_idx
    ON auth.role_permission_profiles (business_id, role_id, sucursal_id);

CREATE TABLE IF NOT EXISTS auth.role_permission_profile_items (
    profile_id bigint NOT NULL REFERENCES auth.role_permission_profiles (profile_id) ON DELETE CASCADE,
    permission_code integer NOT NULL REFERENCES auth.permission_catalog (permission_code),
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (profile_id, permission_code)
);

COMMENT ON TABLE auth.role_permission_profile_items IS 'Detalle normalizado de permisos que pertenecen al perfil base de un rol por sucursal.';
COMMENT ON COLUMN auth.role_permission_profile_items.profile_id IS 'FK al perfil base de rol+sucursal.';
COMMENT ON COLUMN auth.role_permission_profile_items.permission_code IS 'Permiso canonico asignado al perfil.';

CREATE INDEX IF NOT EXISTS role_permission_profile_items_permission_idx
    ON auth.role_permission_profile_items (permission_code);

CREATE TABLE IF NOT EXISTS auth.user_permission_overrides (
    override_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    business_id varchar(100) NOT NULL,
    id_login integer NOT NULL REFERENCES auth.login (id_login) ON DELETE CASCADE,
    id_usuario integer NOT NULL REFERENCES workforce.usuarios (id_usuario) ON DELETE CASCADE,
    sucursal_id integer NOT NULL CHECK (sucursal_id > 0),
    role_id integer NOT NULL CHECK (role_id IN (1, 2, 3)),
    permission_code integer NOT NULL REFERENCES auth.permission_catalog (permission_code),
    override_mode text NOT NULL CHECK (override_mode IN ('allow', 'deny')),
    source_table text NOT NULL DEFAULT 'workforce.usuarios',
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT user_permission_overrides_scope_uq UNIQUE (business_id, id_login, sucursal_id, permission_code)
);

COMMENT ON TABLE auth.user_permission_overrides IS 'Overrides canonicos por login+sucursal. Se calculan como diff deterministico entre workforce.usuarios.autorizaciones y el perfil base del rol.';
COMMENT ON COLUMN auth.user_permission_overrides.id_login IS 'Login canonico impactado por el override.';
COMMENT ON COLUMN auth.user_permission_overrides.id_usuario IS 'Fila workforce fuente que origino el override.';
COMMENT ON COLUMN auth.user_permission_overrides.sucursal_id IS 'Sucursal a la que aplica el override.';
COMMENT ON COLUMN auth.user_permission_overrides.role_id IS 'Rol canonico observado al momento del backfill.';
COMMENT ON COLUMN auth.user_permission_overrides.permission_code IS 'Permiso canonico afectado.';
COMMENT ON COLUMN auth.user_permission_overrides.override_mode IS 'allow agrega permiso sobre el perfil base; deny lo revoca explicitamente.';

CREATE INDEX IF NOT EXISTS user_permission_overrides_login_scope_idx
    ON auth.user_permission_overrides (business_id, id_login, sucursal_id, override_mode);

CREATE INDEX IF NOT EXISTS user_permission_overrides_user_scope_idx
    ON auth.user_permission_overrides (business_id, id_usuario, sucursal_id);

CREATE OR REPLACE FUNCTION auth.role_key_from_id(p_role_id integer)
RETURNS text
LANGUAGE SQL
IMMUTABLE
AS $$
    SELECT CASE p_role_id
        WHEN 1 THEN 'administrador'
        WHEN 2 THEN 'gerente'
        WHEN 3 THEN 'empleado'
        ELSE 'desconocido'
    END;
$$;

CREATE OR REPLACE FUNCTION auth.bump_authz_version_for_logins(p_login_ids integer[])
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
    v_rows integer := 0;
BEGIN
    IF p_login_ids IS NULL OR array_length(p_login_ids, 1) IS NULL THEN
        RETURN 0;
    END IF;

    UPDATE auth.login
    SET authz_version = COALESCE(authz_version, 0) + 1
    WHERE id_login = ANY (p_login_ids);

    GET DIAGNOSTICS v_rows = ROW_COUNT;
    RETURN v_rows;
END;
$$;

CREATE OR REPLACE FUNCTION auth.refresh_role_permission_profiles(
    p_business_id text DEFAULT NULL,
    p_sucursal_id integer DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM auth.role_permission_profile_items i
    USING auth.role_permission_profiles p
    WHERE i.profile_id = p.profile_id
      AND (p_business_id IS NULL OR p.business_id = p_business_id)
      AND (p_sucursal_id IS NULL OR p.sucursal_id = p_sucursal_id);

    DELETE FROM auth.role_permission_profiles p
    WHERE (p_business_id IS NULL OR p.business_id = p_business_id)
      AND (p_sucursal_id IS NULL OR p.sucursal_id = p_sucursal_id);

    INSERT INTO auth.role_permission_profiles (
        business_id,
        sucursal_id,
        role_id,
        role_key,
        source_platform_role_id,
        source_table,
        created_at,
        updated_at
    )
    SELECT
        src.business_id,
        src.sucursal_id,
        src.role_id,
        auth.role_key_from_id(src.role_id),
        src.source_platform_role_id,
        'platform.configuraciones',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    FROM (
        SELECT DISTINCT
            c.business_id,
            s.sucursal_id,
            role_map.role_id,
            c.role_id AS source_platform_role_id
        FROM platform.configuraciones c
        CROSS JOIN LATERAL unnest(COALESCE(NULLIF(c.sucursal, ARRAY[]::int[]), ARRAY[]::int[])) AS s(sucursal_id)
        CROSS JOIN (VALUES (1), (2), (3)) AS role_map(role_id)
        WHERE c.soft_delete IS NOT TRUE
          AND (p_business_id IS NULL OR c.business_id = p_business_id)
          AND (p_sucursal_id IS NULL OR s.sucursal_id = p_sucursal_id)
    ) src
    ORDER BY src.business_id, src.sucursal_id, src.role_id;

    INSERT INTO auth.role_permission_profile_items (profile_id, permission_code)
    SELECT
        p.profile_id,
        src.permission_code
    FROM (
        SELECT
            c.business_id,
            s.sucursal_id,
            1 AS role_id,
            unnest(COALESCE(c.autorizaciones_administrador, ARRAY[]::int[])) AS permission_code
        FROM platform.configuraciones c
        CROSS JOIN LATERAL unnest(COALESCE(NULLIF(c.sucursal, ARRAY[]::int[]), ARRAY[]::int[])) AS s(sucursal_id)
        WHERE c.soft_delete IS NOT TRUE
          AND (p_business_id IS NULL OR c.business_id = p_business_id)
          AND (p_sucursal_id IS NULL OR s.sucursal_id = p_sucursal_id)

        UNION ALL

        SELECT
            c.business_id,
            s.sucursal_id,
            2 AS role_id,
            unnest(COALESCE(c.autorizaciones_gerente, ARRAY[]::int[])) AS permission_code
        FROM platform.configuraciones c
        CROSS JOIN LATERAL unnest(COALESCE(NULLIF(c.sucursal, ARRAY[]::int[]), ARRAY[]::int[])) AS s(sucursal_id)
        WHERE c.soft_delete IS NOT TRUE
          AND (p_business_id IS NULL OR c.business_id = p_business_id)
          AND (p_sucursal_id IS NULL OR s.sucursal_id = p_sucursal_id)

        UNION ALL

        SELECT
            c.business_id,
            s.sucursal_id,
            3 AS role_id,
            unnest(COALESCE(c.autorizaciones_empleado, ARRAY[]::int[])) AS permission_code
        FROM platform.configuraciones c
        CROSS JOIN LATERAL unnest(COALESCE(NULLIF(c.sucursal, ARRAY[]::int[]), ARRAY[]::int[])) AS s(sucursal_id)
        WHERE c.soft_delete IS NOT TRUE
          AND (p_business_id IS NULL OR c.business_id = p_business_id)
          AND (p_sucursal_id IS NULL OR s.sucursal_id = p_sucursal_id)
    ) src
    JOIN auth.role_permission_profiles p
      ON p.business_id = src.business_id
     AND p.sucursal_id = src.sucursal_id
     AND p.role_id = src.role_id
    JOIN auth.permission_catalog pc
      ON pc.permission_code = src.permission_code
    GROUP BY p.profile_id, src.permission_code;
END;
$$;

CREATE OR REPLACE FUNCTION auth.refresh_user_permission_overrides(
    p_business_id text DEFAULT NULL,
    p_login_id integer DEFAULT NULL,
    p_sucursal_id integer DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM auth.user_permission_overrides uo
    WHERE (p_business_id IS NULL OR uo.business_id = p_business_id)
      AND (p_login_id IS NULL OR uo.id_login = p_login_id)
      AND (p_sucursal_id IS NULL OR uo.sucursal_id = p_sucursal_id);

    WITH role_permissions AS (
        SELECT
            p.business_id,
            p.sucursal_id,
            p.role_id,
            COALESCE(
                array_agg(i.permission_code ORDER BY i.permission_code)
                    FILTER (WHERE i.permission_code IS NOT NULL),
                ARRAY[]::int[]
            ) AS role_auths
        FROM auth.role_permission_profiles p
        LEFT JOIN auth.role_permission_profile_items i
          ON i.profile_id = p.profile_id
        WHERE (p_business_id IS NULL OR p.business_id = p_business_id)
          AND (p_sucursal_id IS NULL OR p.sucursal_id = p_sucursal_id)
        GROUP BY p.business_id, p.sucursal_id, p.role_id
    ),
    user_scope AS (
        SELECT
            u.id_usuario,
            u.id_login,
            u.business_id,
            COALESCE(u.rol, 3) AS role_id,
            COALESCE(u.autorizaciones, ARRAY[]::int[]) AS user_auths,
            COALESCE(
                NULLIF(u.sucursal, ARRAY[]::int[]),
                CASE
                    WHEN u.sucursal_dedicada IS NOT NULL THEN ARRAY[u.sucursal_dedicada]::int[]
                    ELSE ARRAY[]::int[]
                END
            ) AS sucursales
        FROM workforce.usuarios u
        WHERE u.soft_delete IS NOT TRUE
          AND u.id_login IS NOT NULL
          AND (p_business_id IS NULL OR u.business_id = p_business_id)
          AND (p_login_id IS NULL OR u.id_login = p_login_id)
    ),
    user_branch AS (
        SELECT
            us.id_usuario,
            us.id_login,
            us.business_id,
            us.role_id,
            us.user_auths,
            s.sucursal_id,
            COALESCE(rp.role_auths, ARRAY[]::int[]) AS role_auths
        FROM user_scope us
        CROSS JOIN LATERAL unnest(us.sucursales) AS s(sucursal_id)
        LEFT JOIN role_permissions rp
          ON rp.business_id = us.business_id
         AND rp.sucursal_id = s.sucursal_id
         AND rp.role_id = us.role_id
        WHERE (p_sucursal_id IS NULL OR s.sucursal_id = p_sucursal_id)
    ),
    allow_rows AS (
        SELECT
            ub.business_id,
            ub.id_login,
            ub.id_usuario,
            ub.sucursal_id,
            ub.role_id,
            ac.permission_code,
            'allow'::text AS override_mode
        FROM user_branch ub
        CROSS JOIN LATERAL (
            SELECT permission_code
            FROM (
                SELECT unnest(ub.user_auths) AS permission_code
                EXCEPT
                SELECT unnest(ub.role_auths) AS permission_code
            ) diff_allow
        ) ac
    ),
    deny_rows AS (
        SELECT
            ub.business_id,
            ub.id_login,
            ub.id_usuario,
            ub.sucursal_id,
            ub.role_id,
            dc.permission_code,
            'deny'::text AS override_mode
        FROM user_branch ub
        CROSS JOIN LATERAL (
            SELECT permission_code
            FROM (
                SELECT unnest(ub.role_auths) AS permission_code
                EXCEPT
                SELECT unnest(ub.user_auths) AS permission_code
            ) diff_deny
        ) dc
    ),
    combined AS (
        SELECT * FROM allow_rows
        UNION ALL
        SELECT * FROM deny_rows
    )
    INSERT INTO auth.user_permission_overrides (
        business_id,
        id_login,
        id_usuario,
        sucursal_id,
        role_id,
        permission_code,
        override_mode,
        source_table,
        created_at,
        updated_at
    )
    SELECT
        c.business_id,
        c.id_login,
        c.id_usuario,
        c.sucursal_id,
        c.role_id,
        c.permission_code,
        c.override_mode,
        'workforce.usuarios',
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
    FROM combined c
    JOIN auth.permission_catalog pc
      ON pc.permission_code = c.permission_code;
END;
$$;

CREATE OR REPLACE VIEW auth.v_role_permission_profiles AS
SELECT
    p.business_id,
    p.sucursal_id,
    p.role_id,
    p.role_key,
    p.source_platform_role_id,
    COALESCE(
        array_agg(i.permission_code ORDER BY i.permission_code)
            FILTER (WHERE i.permission_code IS NOT NULL),
        ARRAY[]::int[]
    ) AS permissions
FROM auth.role_permission_profiles p
LEFT JOIN auth.role_permission_profile_items i
  ON i.profile_id = p.profile_id
GROUP BY
    p.business_id,
    p.sucursal_id,
    p.role_id,
    p.role_key,
    p.source_platform_role_id;

COMMENT ON VIEW auth.v_role_permission_profiles IS 'Vista agregada del perfil base por negocio+sucursal+rol. Devuelve permissions[] listas para bootstrap/QA sin perder el detalle normalizado.';

CREATE OR REPLACE VIEW auth.v_user_permission_overrides AS
SELECT
    uo.business_id,
    uo.id_login,
    uo.id_usuario,
    uo.sucursal_id,
    uo.role_id,
    COALESCE(
        array_agg(uo.permission_code ORDER BY uo.permission_code)
            FILTER (WHERE uo.override_mode = 'allow'),
        ARRAY[]::int[]
    ) AS allow_permissions,
    COALESCE(
        array_agg(uo.permission_code ORDER BY uo.permission_code)
            FILTER (WHERE uo.override_mode = 'deny'),
        ARRAY[]::int[]
    ) AS deny_permissions
FROM auth.user_permission_overrides uo
GROUP BY
    uo.business_id,
    uo.id_login,
    uo.id_usuario,
    uo.sucursal_id,
    uo.role_id;

COMMENT ON VIEW auth.v_user_permission_overrides IS 'Vista agregada de overrides por login+sucursal. Separa permisos allow/deny calculados desde workforce.usuarios.';

CREATE OR REPLACE VIEW auth.v_login_authorization_context AS
WITH login_scope AS (
    SELECT
        l.business_id,
        l.id_login,
        COALESCE(l.fundador, false) AS fundador,
        COALESCE(l.session_version, '0') AS session_version,
        COALESCE(l.authz_version, 1) AS authz_version,
        COALESCE(
            NULLIF(l.sucursal, ARRAY[]::int[]),
            CASE
                WHEN l.pref_sucursal IS NOT NULL THEN ARRAY[l.pref_sucursal]::int[]
                ELSE ARRAY[]::int[]
            END
        ) AS login_sucursales
    FROM auth.login l
    WHERE l.soft_delete IS NOT TRUE
      AND l.activo IS DISTINCT FROM FALSE
),
user_scope AS (
    SELECT
        u.id_usuario,
        u.id_login,
        u.business_id,
        COALESCE(u.rol, 3) AS role_id,
        COALESCE(
            NULLIF(u.sucursal, ARRAY[]::int[]),
            CASE
                WHEN u.sucursal_dedicada IS NOT NULL THEN ARRAY[u.sucursal_dedicada]::int[]
                ELSE ARRAY[]::int[]
            END
        ) AS user_sucursales
    FROM workforce.usuarios u
    WHERE u.soft_delete IS NOT TRUE
      AND u.id_login IS NOT NULL
),
user_branch AS (
    SELECT
        us.id_usuario,
        us.id_login,
        us.business_id,
        us.role_id,
        s.sucursal_id
    FROM user_scope us
    CROSS JOIN LATERAL unnest(us.user_sucursales) AS s(sucursal_id)
)
SELECT
    ls.business_id,
    ls.id_login,
    ub.id_usuario,
    ub.sucursal_id,
    ub.role_id,
    auth.role_key_from_id(ub.role_id) AS role_key,
    ls.fundador,
    ls.session_version,
    ls.authz_version,
    COALESCE(rp.permissions, ARRAY[]::int[]) AS base_permissions,
    COALESCE(uo.allow_permissions, ARRAY[]::int[]) AS allow_permissions,
    COALESCE(uo.deny_permissions, ARRAY[]::int[]) AS deny_permissions,
    CASE
        WHEN ls.fundador IS TRUE THEN (
            SELECT COALESCE(array_agg(pc.permission_code ORDER BY pc.permission_code), ARRAY[]::int[])
            FROM auth.permission_catalog pc
            WHERE pc.active IS TRUE
        )
        ELSE ARRAY(
            SELECT perm_code
            FROM (
                SELECT unnest(COALESCE(rp.permissions, ARRAY[]::int[])) AS perm_code
                UNION
                SELECT unnest(COALESCE(uo.allow_permissions, ARRAY[]::int[])) AS perm_code
            ) effective
            WHERE perm_code NOT IN (
                SELECT unnest(COALESCE(uo.deny_permissions, ARRAY[]::int[]))
            )
            ORDER BY perm_code
        )
    END AS effective_permissions
FROM login_scope ls
JOIN user_branch ub
  ON ub.business_id = ls.business_id
 AND ub.id_login = ls.id_login
LEFT JOIN auth.v_role_permission_profiles rp
  ON rp.business_id = ub.business_id
 AND rp.sucursal_id = ub.sucursal_id
 AND rp.role_id = ub.role_id
LEFT JOIN auth.v_user_permission_overrides uo
  ON uo.business_id = ub.business_id
 AND uo.id_login = ub.id_login
 AND uo.id_usuario = ub.id_usuario
 AND uo.sucursal_id = ub.sucursal_id
WHERE ls.fundador IS TRUE
   OR ub.sucursal_id = ANY (ls.login_sucursales);

COMMENT ON VIEW auth.v_login_authorization_context IS 'Contexto canonico por login+sucursal para el evaluador backend. Resuelve base_permissions, overrides, founder, authz_version y effective_permissions.';

CREATE OR REPLACE FUNCTION auth.get_effective_permissions(
    p_business_id varchar,
    p_login_id integer,
    p_sucursal_id integer
)
RETURNS int[]
LANGUAGE SQL
STABLE
AS $$
    WITH login_ctx AS (
        SELECT
            l.business_id,
            l.id_login,
            COALESCE(l.fundador, false) AS fundador
        FROM auth.login l
        WHERE l.business_id = p_business_id
          AND l.id_login = p_login_id
          AND l.soft_delete IS NOT TRUE
          AND l.activo IS DISTINCT FROM FALSE
        LIMIT 1
    ),
    branch_ctx AS (
        SELECT v.effective_permissions
        FROM auth.v_login_authorization_context v
        WHERE v.business_id = p_business_id
          AND v.id_login = p_login_id
          AND v.sucursal_id = p_sucursal_id
        LIMIT 1
    )
    SELECT CASE
        WHEN EXISTS (SELECT 1 FROM login_ctx WHERE fundador IS TRUE) THEN (
            SELECT COALESCE(array_agg(pc.permission_code ORDER BY pc.permission_code), ARRAY[]::int[])
            FROM auth.permission_catalog pc
            WHERE pc.active IS TRUE
        )
        ELSE COALESCE((SELECT effective_permissions FROM branch_ctx), ARRAY[]::int[])
    END;
$$;

CREATE OR REPLACE FUNCTION auth.evaluate_permission(
    p_business_id varchar,
    p_login_id integer,
    p_sucursal_id integer,
    p_permission_code integer
)
RETURNS TABLE (
    allowed boolean,
    decision_origin text,
    founder boolean,
    permission_code integer,
    authz_version bigint,
    session_version text
)
LANGUAGE SQL
STABLE
AS $$
    WITH login_ctx AS (
        SELECT
            l.business_id,
            l.id_login,
            COALESCE(l.fundador, false) AS fundador,
            COALESCE(l.authz_version, 1) AS authz_version,
            COALESCE(l.session_version, '0') AS session_version
        FROM auth.login l
        WHERE l.business_id = p_business_id
          AND l.id_login = p_login_id
          AND l.soft_delete IS NOT TRUE
          AND l.activo IS DISTINCT FROM FALSE
        LIMIT 1
    ),
    branch_ctx AS (
        SELECT
            v.base_permissions,
            v.allow_permissions,
            v.deny_permissions,
            v.authz_version,
            v.session_version
        FROM auth.v_login_authorization_context v
        WHERE v.business_id = p_business_id
          AND v.id_login = p_login_id
          AND v.sucursal_id = p_sucursal_id
        LIMIT 1
    )
    SELECT
        CASE
            WHEN EXISTS (SELECT 1 FROM login_ctx WHERE fundador IS TRUE) THEN true
            WHEN EXISTS (SELECT 1 FROM branch_ctx WHERE p_permission_code = ANY (deny_permissions)) THEN false
            WHEN EXISTS (SELECT 1 FROM branch_ctx WHERE p_permission_code = ANY (allow_permissions)) THEN true
            WHEN EXISTS (SELECT 1 FROM branch_ctx WHERE p_permission_code = ANY (base_permissions)) THEN true
            ELSE false
        END AS allowed,
        CASE
            WHEN EXISTS (SELECT 1 FROM login_ctx WHERE fundador IS TRUE) THEN 'founder'
            WHEN EXISTS (SELECT 1 FROM branch_ctx WHERE p_permission_code = ANY (deny_permissions)) THEN 'override_deny'
            WHEN EXISTS (SELECT 1 FROM branch_ctx WHERE p_permission_code = ANY (allow_permissions)) THEN 'override_allow'
            WHEN EXISTS (SELECT 1 FROM branch_ctx WHERE p_permission_code = ANY (base_permissions)) THEN 'role'
            ELSE 'none'
        END AS decision_origin,
        COALESCE((SELECT fundador FROM login_ctx LIMIT 1), false) AS founder,
        p_permission_code AS permission_code,
        COALESCE(
            (SELECT authz_version FROM branch_ctx LIMIT 1),
            (SELECT authz_version FROM login_ctx LIMIT 1),
            0::bigint
        ) AS authz_version,
        COALESCE(
            (SELECT session_version FROM branch_ctx LIMIT 1),
            (SELECT session_version FROM login_ctx LIMIT 1),
            '0'
        ) AS session_version;
$$;

CREATE OR REPLACE FUNCTION auth.trg_sync_platform_configuraciones_authz()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_business_id text;
    v_login_ids integer[];
BEGIN
    IF TG_OP = 'DELETE' THEN
        v_business_id := OLD.business_id;
    ELSE
        v_business_id := NEW.business_id;
    END IF;

    IF v_business_id IS NULL OR v_business_id = '' THEN
        RETURN NULL;
    END IF;

    PERFORM auth.refresh_role_permission_profiles(v_business_id, NULL);
    PERFORM auth.refresh_user_permission_overrides(v_business_id, NULL, NULL);

    SELECT array_agg(DISTINCT l.id_login ORDER BY l.id_login)
    INTO v_login_ids
    FROM auth.login l
    WHERE l.business_id = v_business_id;

    PERFORM auth.bump_authz_version_for_logins(v_login_ids);
    RETURN NULL;
END;
$$;

CREATE OR REPLACE FUNCTION auth.trg_sync_workforce_usuarios_authz()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_old_business_id text := NULL;
    v_new_business_id text := NULL;
    v_old_login_id integer := NULL;
    v_new_login_id integer := NULL;
BEGIN
    IF TG_OP IN ('UPDATE', 'DELETE') THEN
        v_old_business_id := OLD.business_id;
        v_old_login_id := OLD.id_login;
    END IF;

    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        v_new_business_id := NEW.business_id;
        v_new_login_id := NEW.id_login;
    END IF;

    IF TG_OP = 'UPDATE'
       AND v_old_business_id IS NOT DISTINCT FROM v_new_business_id
       AND v_old_login_id IS NOT DISTINCT FROM v_new_login_id
       AND v_new_business_id IS NOT NULL
       AND v_new_business_id <> ''
       AND v_new_login_id IS NOT NULL THEN
        PERFORM auth.refresh_user_permission_overrides(v_new_business_id, v_new_login_id, NULL);
        PERFORM auth.bump_authz_version_for_logins(ARRAY[v_new_login_id]);
        RETURN NULL;
    END IF;

    IF TG_OP IN ('UPDATE', 'DELETE') AND v_old_business_id IS NOT NULL AND v_old_business_id <> '' AND v_old_login_id IS NOT NULL THEN
        PERFORM auth.refresh_user_permission_overrides(v_old_business_id, v_old_login_id, NULL);
        PERFORM auth.bump_authz_version_for_logins(ARRAY[v_old_login_id]);
    END IF;

    IF TG_OP IN ('INSERT', 'UPDATE') AND v_new_business_id IS NOT NULL AND v_new_business_id <> '' AND v_new_login_id IS NOT NULL THEN
        PERFORM auth.refresh_user_permission_overrides(v_new_business_id, v_new_login_id, NULL);
        PERFORM auth.bump_authz_version_for_logins(ARRAY[v_new_login_id]);
    END IF;

    RETURN NULL;
END;
$$;

CREATE OR REPLACE FUNCTION auth.trg_bump_login_authz_version()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.authz_version := COALESCE(OLD.authz_version, 0) + 1;
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_sync_platform_configuraciones_authz ON platform.configuraciones;
CREATE TRIGGER trg_sync_platform_configuraciones_authz
AFTER INSERT OR DELETE OR UPDATE OF business_id, sucursal, autorizaciones_administrador, autorizaciones_gerente, autorizaciones_empleado, soft_delete
ON platform.configuraciones
FOR EACH ROW
EXECUTE FUNCTION auth.trg_sync_platform_configuraciones_authz();

DROP TRIGGER IF EXISTS trg_sync_workforce_usuarios_authz ON workforce.usuarios;
CREATE TRIGGER trg_sync_workforce_usuarios_authz
AFTER INSERT OR DELETE OR UPDATE OF business_id, id_login, rol, sucursal, sucursal_dedicada, autorizaciones, soft_delete
ON workforce.usuarios
FOR EACH ROW
EXECUTE FUNCTION auth.trg_sync_workforce_usuarios_authz();

DROP TRIGGER IF EXISTS trg_bump_login_authz_version ON auth.login;
CREATE TRIGGER trg_bump_login_authz_version
BEFORE UPDATE OF business_id, fundador, sucursal, pref_sucursal, activo, soft_delete
ON auth.login
FOR EACH ROW
WHEN (
    OLD.business_id IS DISTINCT FROM NEW.business_id
    OR OLD.fundador IS DISTINCT FROM NEW.fundador
    OR OLD.sucursal IS DISTINCT FROM NEW.sucursal
    OR OLD.pref_sucursal IS DISTINCT FROM NEW.pref_sucursal
    OR OLD.activo IS DISTINCT FROM NEW.activo
    OR OLD.soft_delete IS DISTINCT FROM NEW.soft_delete
)
EXECUTE FUNCTION auth.trg_bump_login_authz_version();

SELECT auth.refresh_role_permission_profiles(NULL, NULL);
SELECT auth.refresh_user_permission_overrides(NULL, NULL, NULL);

ANALYZE auth.permission_catalog;
ANALYZE auth.role_permission_profiles;
ANALYZE auth.role_permission_profile_items;
ANALYZE auth.user_permission_overrides;
ANALYZE auth.login;
ANALYZE platform.configuraciones;
ANALYZE workforce.usuarios;

COMMIT;
