\set previous_version 'v1.8.1'
\set next_version 'v1.9.0'
SELECT openreplay_version()                       AS current_version,
       openreplay_version() = :'previous_version' AS valid_previous,
       openreplay_version() = :'next_version'     AS is_next
\gset

\if :valid_previous
\echo valid previous DB version :'previous_version', starting DB upgrade to :'next_version'
BEGIN;
SELECT format($fn_def$
CREATE OR REPLACE FUNCTION openreplay_version()
    RETURNS text AS
$$
SELECT '%1$s'
$$ LANGUAGE sql IMMUTABLE;
$fn_def$, :'next_version')
\gexec

--

ALTER TABLE IF EXISTS public.tenants
    ADD COLUMN IF NOT EXISTS last_telemetry bigint NOT NULL DEFAULT CAST(EXTRACT(epoch FROM date_trunc('day', now())) * 1000 AS BIGINT),
    DROP COLUMN IF EXISTS version_number;

CREATE TABLE IF NOT EXISTS public.sessions_notes
(
    note_id    integer generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    message    text                        NOT NULL,
    created_at timestamp without time zone NOT NULL default (now() at time zone 'utc'),
    user_id    integer                     NULL REFERENCES public.users (user_id) ON DELETE SET NULL,
    deleted_at timestamp without time zone NULL     DEFAULT NULL,
    tag        text                        NULL,
    session_id bigint                      NOT NULL REFERENCES public.sessions (session_id) ON DELETE CASCADE,
    project_id integer                     NOT NULL REFERENCES public.projects (project_id) ON DELETE CASCADE,
    timestamp  integer                     NOT NULL DEFAULT -1,
    is_public  boolean                     NOT NULL DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS public.errors_tags
(
    key        text                        NOT NULL,
    value      text                        NOT NULL,
    created_at timestamp without time zone NOT NULL default (now() at time zone 'utc'),
    error_id   text                        NOT NULL REFERENCES public.errors (error_id) ON DELETE CASCADE,
    session_id bigint                      NOT NULL,
    message_id bigint                      NOT NULL,
    FOREIGN KEY (session_id, message_id) REFERENCES events.errors (session_id, message_id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS errors_tags_error_id_idx ON public.errors_tags (error_id);
CREATE INDEX IF NOT EXISTS errors_tags_session_id_idx ON public.errors_tags (session_id);
CREATE INDEX IF NOT EXISTS errors_tags_message_id_idx ON public.errors_tags (message_id);

UPDATE metrics
SET default_config=default_config || '{"col":4}'
WHERE metric_type = 'funnel';

UPDATE dashboard_widgets
SET config=config || '{"col":4}'
WHERE metric_id IN (SELECT metric_id FROM metrics WHERE metric_type = 'funnel');

CREATE OR REPLACE FUNCTION notify_integration() RETURNS trigger AS
$$
BEGIN
    IF NEW IS NULL THEN
        PERFORM pg_notify('integration',
                          jsonb_build_object('project_id', OLD.project_id, 'provider', OLD.provider, 'options',
                                             null)::text);
    ELSIF (OLD IS NULL) OR (OLD.options <> NEW.options) THEN
        PERFORM pg_notify('integration', row_to_json(NEW)::text);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

DROP INDEX IF EXISTS events_common.requests_url_idx;
DROP INDEX IF EXISTS events_common.requests_url_gin_idx;
DROP INDEX IF EXISTS events_common.requests_url_gin_idx2;

DROP INDEX IF EXISTS events.resources_url_gin_idx;
DROP INDEX IF EXISTS events.resources_url_idx;

UPDATE metrics
SET default_config=default_config || '{
  "col": 4
}'::jsonb
WHERE NOT is_predefined
  AND (metric_type = 'funnel' OR (metric_type = 'table' AND metric_of IN ('SESSIONS', 'js_exception')));

COMMIT;

CREATE INDEX CONCURRENTLY IF NOT EXISTS requests_session_id_status_code_nn_idx ON events_common.requests (session_id, status_code) WHERE status_code IS NOT NULL;

\elif :is_next
\echo new version detected :'next_version', nothing to do
\else
\warn skipping DB upgrade of :'next_version', expected previous version :'previous_version', found :'current_version'
\endif