SHiNE-server/SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v2.sql

BEGIN;

CREATE TABLE IF NOT EXISTS user_access_servers_current (
    user_login             TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
    server_login           TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
    server_url             TEXT NOT NULL,
    server_client_key      TEXT NOT NULL,
    user_record_number     INTEGER NOT NULL,
    user_updated_at_ms     BIGINT NOT NULL,
    server_record_number   INTEGER NOT NULL,
    server_updated_at_ms   BIGINT NOT NULL,
    refreshed_at_ms        BIGINT NOT NULL,
    PRIMARY KEY (user_login, server_login)
);

CREATE INDEX IF NOT EXISTS idx_user_access_servers_user
    ON user_access_servers_current(user_login);

CREATE INDEX IF NOT EXISTS idx_user_access_servers_server
    ON user_access_servers_current(server_login);

CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_user(p_user_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
    IF p_user_login IS NULL OR btrim(p_user_login) = '' THEN
        RETURN;
    END IF;

    DELETE FROM user_access_servers_current
    WHERE LOWER(user_login) = LOWER(p_user_login);

    INSERT INTO user_access_servers_current (
        user_login,
        server_login,
        server_url,
        server_client_key,
        user_record_number,
        user_updated_at_ms,
        server_record_number,
        server_updated_at_ms,
        refreshed_at_ms
    )
    SELECT
        u.login,
        s.login,
        s.server_address,
        s.client_key,
        u.record_number,
        u.updated_at_ms,
        s.record_number,
        s.updated_at_ms,
        CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
    FROM solana_user_pda_current u
    CROSS JOIN LATERAL (
        SELECT login_value
        FROM jsonb_array_elements_text(
            CASE
                WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
                ELSE u.access_servers_json::jsonb
            END
        ) WITH ORDINALITY AS access_server(login_value, ord)
        WHERE ord <= 2
    ) AS access_server
    JOIN solana_user_pda_current s
      ON LOWER(s.login) = LOWER(btrim(access_server.login_value))
     AND s.is_server = TRUE
     AND btrim(COALESCE(s.server_address, '')) <> ''
    WHERE LOWER(u.login) = LOWER(p_user_login)
    ON CONFLICT (user_login, server_login) DO UPDATE SET
        server_url = EXCLUDED.server_url,
        server_client_key = EXCLUDED.server_client_key,
        user_record_number = EXCLUDED.user_record_number,
        user_updated_at_ms = EXCLUDED.user_updated_at_ms,
        server_record_number = EXCLUDED.server_record_number,
        server_updated_at_ms = EXCLUDED.server_updated_at_ms,
        refreshed_at_ms = EXCLUDED.refreshed_at_ms;
END;
$$;

CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_for_server(p_server_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    affected_user RECORD;
BEGIN
    IF p_server_login IS NULL OR btrim(p_server_login) = '' THEN
        RETURN;
    END IF;

    DELETE FROM user_access_servers_current
    WHERE LOWER(server_login) = LOWER(p_server_login);

    FOR affected_user IN
        SELECT u.login
        FROM solana_user_pda_current u
        CROSS JOIN LATERAL (
            SELECT login_value
            FROM jsonb_array_elements_text(
                CASE
                    WHEN btrim(COALESCE(u.access_servers_json, '')) = '' THEN '[]'::jsonb
                    ELSE u.access_servers_json::jsonb
                END
            ) WITH ORDINALITY AS access_server(login_value, ord)
            WHERE ord <= 2
        ) AS access_server
        WHERE LOWER(btrim(access_server.login_value)) = LOWER(p_server_login)
    LOOP
        PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
    END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION shine_refresh_user_access_servers_all()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    affected_user RECORD;
BEGIN
    TRUNCATE TABLE user_access_servers_current;

    FOR affected_user IN
        SELECT login
        FROM solana_user_pda_current
    LOOP
        PERFORM shine_refresh_user_access_servers_for_user(affected_user.login);
    END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_row()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
        PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
        RETURN OLD;
    END IF;

    IF TG_OP = 'UPDATE' AND LOWER(OLD.login) <> LOWER(NEW.login) THEN
        PERFORM shine_refresh_user_access_servers_for_user(OLD.login);
        PERFORM shine_refresh_user_access_servers_for_server(OLD.login);
    END IF;

    PERFORM shine_refresh_user_access_servers_for_user(NEW.login);
    PERFORM shine_refresh_user_access_servers_for_server(NEW.login);
    RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    TRUNCATE TABLE user_access_servers_current;
    RETURN NULL;
END;
$$;

DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_row ON solana_user_pda_current;
CREATE TRIGGER trg_refresh_user_access_servers_row
AFTER INSERT OR UPDATE OR DELETE ON solana_user_pda_current
FOR EACH ROW
EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_row();

DROP TRIGGER IF EXISTS trg_refresh_user_access_servers_truncate ON solana_user_pda_current;
CREATE TRIGGER trg_refresh_user_access_servers_truncate
AFTER TRUNCATE ON solana_user_pda_current
FOR EACH STATEMENT
EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate();

SELECT shine_refresh_user_access_servers_all();

INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 2, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
ON CONFLICT (id) DO UPDATE SET
    schema_version = EXCLUDED.schema_version,
    updated_at_ms = EXCLUDED.updated_at_ms;

COMMIT;