BEGIN;

CREATE INDEX IF NOT EXISTS sessions_user_id_useridNN_idx ON sessions (user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS sessions_uid_projectid_startts_sessionid_uidNN_durGTZ_idx ON sessions (user_id, project_id, start_ts, session_id) WHERE user_id IS NOT NULL AND duration > 0;
CREATE INDEX IF NOT EXISTS pages_base_path_base_pathLNGT2_idx ON events.pages (base_path) WHERE length(base_path) > 2;

CREATE INDEX IF NOT EXISTS users_tenant_id_deleted_at_N_idx ON users (tenant_id) WHERE deleted_at ISNULL;
CREATE INDEX IF NOT EXISTS issues_issue_id_timestamp_idx ON events_common.issues (issue_id, timestamp);
CREATE INDEX IF NOT EXISTS issues_timestamp_idx ON events_common.issues (timestamp);
CREATE INDEX IF NOT EXISTS issues_project_id_issue_id_idx ON public.issues (project_id, issue_id);

CREATE TABLE roles
(
    role_id     integer generated BY DEFAULT AS IDENTITY PRIMARY KEY,
    tenant_id   integer   NOT NULL REFERENCES tenants (tenant_id) ON DELETE CASCADE,
    name        text      NOT NULL,
    description text               DEFAULT NULL,
    permissions text[]    NOT NULL DEFAULT '{}',
    protected   bool      NOT NULL DEFAULT FALSE,
    created_at  timestamp NOT NULL DEFAULT timezone('utc'::text, now()),
    deleted_at  timestamp NULL     DEFAULT NULL
);

INSERT INTO roles(tenant_id, name, description, permissions, protected)
SELECT *
FROM (SELECT tenant_id FROM tenants) AS tenants,
     (VALUES ('Owner', 'Owner',
              '{"SESSION_REPLAY", "DEV_TOOLS", "ERRORS", "METRICS", "ASSIST_LIVE", "ASSIST_CALL"}'::text[], TRUE),
             ('Member', 'Member',
              '{"SESSION_REPLAY", "DEV_TOOLS", "ERRORS", "METRICS", "ASSIST_LIVE", "ASSIST_CALL"}'::text[], FALSE)
     ) AS default_roles(name, description, permissions, protected);


ALTER TABLE users
    ADD COLUMN role_id integer REFERENCES roles (role_id) ON DELETE SET NULL;

UPDATE users
SET role_id = r.role_id
FROM (SELECT tenant_id, role_id
      FROM tenants
               INNER JOIN roles USING (tenant_id)
      WHERE roles.name = 'Owner') AS r(tenant_id, role_id)
WHERE users.tenant_id = r.tenant_id
  AND users.role = 'owner';

UPDATE users
SET role_id = r.role_id
FROM (SELECT tenant_id, role_id
      FROM tenants
               INNER JOIN roles USING (tenant_id)
      WHERE roles.name = 'Member') AS r(tenant_id, role_id)
WHERE users.tenant_id = r.tenant_id
  AND users.role != 'owner';

DO
$$
    BEGIN
        IF NOT EXISTS(SELECT 1 FROM pg_type WHERE typname = 'user_origin') THEN
            CREATE TYPE user_origin AS ENUM ('saml');
        END IF;
    END
$$;
ALTER TABLE public.users
    ADD COLUMN IF NOT EXISTS origin      user_origin NULL DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS internal_id text        NULL DEFAULT NULL;



ALTER TABLE public.users
    ALTER COLUMN origin TYPE text;
DROP TYPE IF EXISTS user_origin;
COMMIT;