-- This file is auto-generated by "make db-schema"; DO NOT EDIT
-- DATA=50f009ab810d42724027d31ec49f2584a1c8ecd647194c2455de853975d40bd0  -
-- DISK=2f8c154346b509ddac6e6d36a7a4f1bc86c047dcd1f98eb3ee5846bdf4bcf687  -
-- PSQL=2f8c154346b509ddac6e6d36a7a4f1bc86c047dcd1f98eb3ee5846bdf4bcf687  -
--
-- pgdump-lite database dump
--

-- Extensions

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Enums

CREATE TYPE engine_processing_type AS ENUM (
	'cleanup',
	'compat',
	'escalation',
	'heartbeat',
	'message',
	'metrics',
	'np_cycle',
	'rotation',
	'schedule',
	'signals',
	'status_update',
	'verify'
);

CREATE TYPE enum_alert_log_event AS ENUM (
	'acknowledged',
	'assignment_changed',
	'closed',
	'created',
	'duplicate_suppressed',
	'escalated',
	'escalation_request',
	'no_notification_sent',
	'notification_sent',
	'policy_updated',
	'reopened',
	'response_received',
	'status_changed'
);

CREATE TYPE enum_alert_log_subject_type AS ENUM (
	'channel',
	'heartbeat_monitor',
	'integration_key',
	'user'
);

CREATE TYPE enum_alert_source AS ENUM (
	'email',
	'generic',
	'grafana',
	'manual',
	'prometheusAlertmanager',
	'site24x7',
	'universal'
);

CREATE TYPE enum_alert_status AS ENUM (
	'active',
	'closed',
	'triggered'
);

CREATE TYPE enum_heartbeat_state AS ENUM (
	'healthy',
	'inactive',
	'unhealthy'
);

CREATE TYPE enum_integration_keys_type AS ENUM (
	'email',
	'generic',
	'grafana',
	'prometheusAlertmanager',
	'site24x7',
	'universal'
);

CREATE TYPE enum_limit_type AS ENUM (
	'calendar_subscriptions_per_user',
	'contact_methods_per_user',
	'ep_actions_per_step',
	'ep_steps_per_policy',
	'heartbeat_monitors_per_service',
	'integration_keys_per_service',
	'notification_rules_per_user',
	'participants_per_rotation',
	'pending_signals_per_dest_per_service',
	'pending_signals_per_service',
	'rules_per_schedule',
	'targets_per_schedule',
	'unacked_alerts_per_service',
	'user_overrides_per_schedule'
);

CREATE TYPE enum_notif_channel_type AS ENUM (
	'DEST',
	'SLACK',
	'SLACK_USER_GROUP',
	'WEBHOOK'
);

CREATE TYPE enum_outgoing_messages_status AS ENUM (
	'bundled',
	'delivered',
	'failed',
	'pending',
	'queued_remotely',
	'read',
	'sending',
	'sent'
);

CREATE TYPE enum_outgoing_messages_type AS ENUM (
	'alert_notification',
	'alert_notification_bundle',
	'alert_status_update',
	'alert_status_update_bundle',
	'schedule_on_call_notification',
	'signal_message',
	'test_notification',
	'verification_message'
);

CREATE TYPE enum_rotation_type AS ENUM (
	'daily',
	'hourly',
	'monthly',
	'weekly'
);

CREATE TYPE enum_switchover_state AS ENUM (
	'idle',
	'in_progress',
	'use_next_db'
);

CREATE TYPE enum_throttle_type AS ENUM (
	'notifications',
	'notifications_2'
);

CREATE TYPE enum_user_contact_method_type AS ENUM (
	'DEST',
	'EMAIL',
	'PUSH',
	'SLACK_DM',
	'SMS',
	'VOICE',
	'WEBHOOK'
);

CREATE TYPE enum_user_role AS ENUM (
	'admin',
	'unknown',
	'user'
);

CREATE TYPE river_job_state AS ENUM (
	'available',
	'cancelled',
	'completed',
	'discarded',
	'pending',
	'retryable',
	'running',
	'scheduled'
);

-- Functions

CREATE OR REPLACE FUNCTION public.aquire_user_contact_method_lock(_client_id uuid, _alert_id bigint, _contact_method_id uuid)
 RETURNS uuid
 LANGUAGE plpgsql
AS $function$
        DECLARE
            lock_id UUID = gen_random_uuid();
        BEGIN
            DELETE FROM user_contact_method_locks WHERE alert_id = _alert_id
                AND contact_method_id = _contact_method_id
                AND (timestamp + '5 minutes'::interval) < now();

            INSERT INTO user_contact_method_locks (id, alert_id, contact_method_id, client_id) 
                VALUES (lock_id, _alert_id, _contact_method_id, _client_id)
                RETURNING id INTO lock_id;

            INSERT INTO sent_notifications (id, alert_id, contact_method_id, cycle_id, notification_rule_id)
			SELECT lock_id, _alert_id, _contact_method_id, cycle_id, notification_rule_id
			FROM needs_notification_sent n
			WHERE n.alert_id = _alert_id AND n.contact_method_id = _contact_method_id
			ON CONFLICT DO NOTHING;

            RETURN lock_id;
        END;
    $function$
;

CREATE OR REPLACE FUNCTION public.cm_type_val_to_dest(typename enum_user_contact_method_type, value text)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF typeName = 'EMAIL' THEN
        RETURN jsonb_build_object('Type', 'builtin-smtp-email', 'Args', jsonb_build_object('email_address', value));
    ELSIF typeName = 'VOICE' THEN
        RETURN jsonb_build_object('Type', 'builtin-twilio-voice', 'Args', jsonb_build_object('phone_number', value));
    ELSIF typeName = 'SMS' THEN
        RETURN jsonb_build_object('Type', 'builtin-twilio-sms', 'Args', jsonb_build_object('phone_number', value));
    ELSIF typeName = 'WEBHOOK' THEN
        RETURN jsonb_build_object('Type', 'builtin-webhook', 'Args', jsonb_build_object('webhook_url', value));
    ELSIF typeName = 'SLACK_DM' THEN
        RETURN jsonb_build_object('Type', 'builtin-slack-dm', 'Args', jsonb_build_object('slack_user_id', value));
    ELSE
        -- throw an error
        RAISE EXCEPTION 'Unknown contact method type: %', typeName;
    END IF;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.date_bin(p_bin_interval interval, p_ts timestamp with time zone, p_origin timestamp with time zone)
 RETURNS timestamp with time zone
 LANGUAGE sql
 IMMUTABLE
AS $function$
            SELECT
                to_timestamp (
floor((extract(epoch FROM p_ts ) - extract(epoch FROM p_origin ) ) / extract(epoch FROM p_bin_interval ) ) * extract(epoch FROM p_bin_interval ) + extract(epoch FROM p_origin )
                );
    $function$
;

CREATE OR REPLACE FUNCTION public.escalate_alerts()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
        BEGIN
            UPDATE alerts
            SET escalation_level = escalation_level + 1, last_escalation = now()
            FROM alert_escalation_policy_snapshots e
            WHERE (last_escalation + e.step_delay) < now()
                AND status = 'triggered'
                AND id = e.alert_id
                AND e.step_number = (escalation_level % e.step_max)
                AND (e.repeat = -1 OR (escalation_level+1) / e.step_max <= e.repeat);
        END;
    $function$
;

CREATE OR REPLACE FUNCTION public.fn_advance_or_end_rot_on_part_del()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    new_part UUID;
    active_part UUID;
BEGIN

    SELECT rotation_participant_id
    INTO active_part
    FROM rotation_state
    WHERE rotation_id = OLD.rotation_id;

    IF active_part != OLD.id THEN
        RETURN OLD;
    END IF;

    IF OLD.rotation_id NOT IN (
       SELECT id FROM rotations
    ) THEN
        DELETE FROM rotation_state
        WHERE rotation_id = OLD.rotation_id;
    END IF;

    SELECT id
    INTO new_part
    FROM rotation_participants
    WHERE
        rotation_id = OLD.rotation_id AND
        id != OLD.id AND
        position IN (0, OLD.position+1)
    ORDER BY position DESC
    LIMIT 1;

     IF new_part ISNULL THEN
        DELETE FROM rotation_state
        WHERE rotation_id = OLD.rotation_id;
    ELSE
        UPDATE rotation_state
        SET rotation_participant_id = new_part
        WHERE rotation_id = OLD.rotation_id;
    END IF;

    RETURN OLD;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_clear_dedup_on_close()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.dedup_key = NULL;
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_clear_ep_state_on_alert_close()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    DELETE FROM escalation_policy_state
    WHERE alert_id = NEW.id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_clear_ep_state_on_svc_ep_change()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE escalation_policy_state
    SET
        escalation_policy_id = NEW.escalation_policy_id,
        escalation_policy_step_id = NULL,
        loop_count = 0,
        last_escalation = NULL,
        next_escalation = NULL,
        force_escalation = false,
        escalation_policy_step_number = 0
    WHERE service_id = NEW.id
    ;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_clear_next_esc_on_alert_ack()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    UPDATE escalation_policy_state
    SET next_escalation = null
    WHERE alert_id = NEW.id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_cm_compat_set_type_val_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.dest ->> 'Type' = 'builtin-smtp-email' THEN
        NEW.type = 'EMAIL';
        NEW.value = NEW.dest -> 'Args' ->> 'email_address';
    ELSIF NEW.dest ->> 'Type' = 'builtin-twilio-voice' THEN
        NEW.type = 'VOICE';
        NEW.value = NEW.dest -> 'Args' ->> 'phone_number';
    ELSIF NEW.dest ->> 'Type' = 'builtin-twilio-sms' THEN
        NEW.type = 'SMS';
        NEW.value = NEW.dest -> 'Args' ->> 'phone_number';
    ELSIF NEW.dest ->> 'Type' = 'builtin-webhook' THEN
        NEW.type = 'WEBHOOK';
        NEW.value = NEW.dest -> 'Args' ->> 'webhook_url';
    ELSIF NEW.dest ->> 'Type' = 'builtin-slack-dm' THEN
        NEW.type = 'SLACK_DM';
        NEW.value = NEW.dest -> 'Args' ->> 'slack_user_id';
    ELSE
        NEW.type = 'DEST';
        NEW.value = gen_random_uuid()::text;
    END IF;
    RETURN new;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_cm_set_dest_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.dest = cm_type_val_to_dest(NEW.type, NEW.value);
    RETURN new;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_cm_set_not_pending_on_verify()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.pending = FALSE;
  RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_decr_ep_step_count_on_del()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE escalation_policies
    SET step_count = step_count - 1
    WHERE id = OLD.escalation_policy_id;

    RETURN OLD;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_decr_ep_step_number_on_delete()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    LOCK escalation_policy_steps IN EXCLUSIVE MODE;

    UPDATE escalation_policy_steps
    SET step_number = step_number - 1
    WHERE
        escalation_policy_id = OLD.escalation_policy_id AND
        step_number > OLD.step_number;

    RETURN OLD;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_decr_part_count_on_del()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE rotations
    SET participant_count = participant_count - 1
    WHERE id = OLD.rotation_id;

    RETURN OLD;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_decr_rot_part_position_on_delete()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    LOCK rotation_participants IN EXCLUSIVE MODE;

    UPDATE rotation_participants
    SET position = position - 1
    WHERE
        rotation_id = OLD.rotation_id AND
        position > OLD.position;

    RETURN OLD;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_disable_inserts()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE EXCEPTION 'inserts are disabled on this table';
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_alert_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'unacked_alerts_per_service';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM alerts
    WHERE service_id = NEW.service_id AND "status" = 'triggered';

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='unacked_alerts_per_service_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_calendar_subscriptions_per_user_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'calendar_subscriptions_per_user';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM user_calendar_subscriptions
    WHERE user_id = NEW.user_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='calendar_subscriptions_per_user_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_contact_method_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'contact_methods_per_user';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM user_contact_methods
    WHERE user_id = NEW.user_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='contact_methods_per_user_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_ep_step_action_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'ep_actions_per_step';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM escalation_policy_actions
    WHERE escalation_policy_step_id = NEW.escalation_policy_step_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='ep_actions_per_step_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_ep_step_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'ep_steps_per_policy';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM escalation_policy_steps
    WHERE escalation_policy_id = NEW.escalation_policy_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='ep_steps_per_policy_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_ep_step_number_no_gaps()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_pos INT := -1;
    step_count INT := 0;
BEGIN
    IF NEW.escalation_policy_id != OLD.escalation_policy_id THEN
        RAISE 'must not change escalation_policy_id of existing step';
    END IF;

    SELECT max(step_number), count(*)
    INTO max_pos, step_count
    FROM escalation_policy_steps
    WHERE escalation_policy_id = NEW.escalation_policy_id;

    IF max_pos >= step_count THEN
        RAISE 'must not have gap in step_numbers';
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_heartbeat_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'heartbeat_monitors_per_service';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM heartbeat_monitors
    WHERE service_id = NEW.service_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='heartbeat_monitors_per_service_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_integration_key_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'integration_keys_per_service';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM integration_keys
    WHERE service_id = NEW.service_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='integration_keys_per_service_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_notification_rule_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'notification_rules_per_user';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM user_notification_rules
    WHERE user_id = NEW.user_id;

    IF max_count != -1 AND val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='notification_rules_per_user_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_rot_part_position_no_gaps()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_pos INT := -1;
    part_count INT := 0;
BEGIN
    IF NEW.rotation_id != OLD.rotation_id THEN
        RAISE 'must not change rotation_id of existing participant';
    END IF;

    SELECT max(position), count(*)
    INTO max_pos, part_count
    FROM rotation_participants
    WHERE rotation_id = NEW.rotation_id;

    IF max_pos >= part_count THEN
        RAISE 'must not have gap in participant positions';
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_rotation_participant_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'participants_per_rotation';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM rotation_participants
    WHERE rotation_id = NEW.rotation_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='participants_per_rotation_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_schedule_rule_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'rules_per_schedule';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM schedule_rules
    WHERE schedule_id = NEW.schedule_id;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='rules_per_schedule_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_schedule_target_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'targets_per_schedule';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM (
        SELECT DISTINCT tgt_user_id, tgt_rotation_id
        FROM schedule_rules
        WHERE schedule_id = NEW.schedule_id
    ) as tmp;

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='targets_per_schedule_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_signals_per_dest_per_service_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count int := - 1;
    val_count int := 0;
BEGIN
    SELECT
        INTO max_count max
    FROM
        config_limits
    WHERE
        id = 'pending_signals_per_dest_per_service';
    IF max_count = - 1 THEN
        RETURN NEW;
    END IF;
    SELECT
        INTO val_count COUNT(*)
    FROM
        pending_signals
    WHERE
        service_id = NEW.service_id
        AND dest_id = NEW.dest_id
        AND message_id IS NULL;
    IF val_count > max_count THEN
        RAISE 'limit exceeded'
        USING ERRCODE = 'check_violation', CONSTRAINT = 'pending_signals_per_dest_per_service_limit', HINT = 'max=' || max_count;
    END IF;
        RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_signals_per_service_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count int := - 1;
    val_count int := 0;
BEGIN
    SELECT
        INTO max_count max
    FROM
        config_limits
    WHERE
        id = 'pending_signals_per_service';
    IF max_count = - 1 THEN
        RETURN NEW;
    END IF;
    SELECT
        INTO val_count COUNT(*)
    FROM
        pending_signals
    WHERE
        service_id = NEW.service_id
        AND message_id IS NULL;
    IF val_count > max_count THEN
        RAISE 'limit exceeded'
        USING ERRCODE = 'check_violation', CONSTRAINT = 'pending_signals_per_service_limit', HINT = 'max=' || max_count;
    END IF;
        RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_status_update_same_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    _cm_user_id UUID;
BEGIN
    IF NEW.alert_status_log_contact_method_id ISNULL THEN
        RETURN NEW;
    END IF;

    SELECT INTO _cm_user_id user_id
    FROM user_contact_methods
    WHERE id = NEW.alert_status_log_contact_method_id;

    IF NEW.id != _cm_user_id THEN
        RAISE 'wrong user_id' USING ERRCODE='check_violation', CONSTRAINT='alert_status_user_id_match';
    END IF;
    
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_user_overide_no_conflict()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    conflict UUID := NULL;
BEGIN
    EXECUTE 'LOCK user_overrides IN EXCLUSIVE MODE';

    SELECT id INTO conflict
    FROM user_overrides
    WHERE
        id != NEW.id AND
        tgt_schedule_id = NEW.tgt_schedule_id AND
        (
            add_user_id in (NEW.remove_user_id, NEW.add_user_id) OR
            remove_user_id in (NEW.remove_user_id, NEW.add_user_id)
        ) AND
        (start_time, end_time) OVERLAPS (NEW.start_time, NEW.end_time)
    LIMIT 1;
  
    IF conflict NOTNULL THEN
        RAISE 'override conflict' USING ERRCODE='check_violation', CONSTRAINT='user_override_no_conflict_allowed', HINT='CONFLICTING_ID='||conflict::text;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_enforce_user_override_schedule_limit()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    max_count INT := -1;
    val_count INT := 0;
BEGIN
    SELECT INTO max_count max
    FROM config_limits
    WHERE id = 'user_overrides_per_schedule';

    IF max_count = -1 THEN
        RETURN NEW;
    END IF;

    SELECT INTO val_count COUNT(*)
    FROM user_overrides
    WHERE
        tgt_schedule_id = NEW.tgt_schedule_id AND
        end_time > now();

    IF val_count > max_count THEN
        RAISE 'limit exceeded' USING ERRCODE='check_violation', CONSTRAINT='user_overrides_per_schedule_limit', HINT='max='||max_count;
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_inc_ep_step_number_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    LOCK escalation_policy_steps IN EXCLUSIVE MODE;

    SELECT count(*)
    INTO NEW.step_number
    FROM escalation_policy_steps
    WHERE escalation_policy_id = NEW.escalation_policy_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_inc_rot_part_position_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    LOCK rotation_participants IN EXCLUSIVE MODE;

    SELECT count(*)
    INTO NEW.position
    FROM rotation_participants
    WHERE rotation_id = NEW.rotation_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_incr_ep_step_count_on_add()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE escalation_policies
    SET step_count = step_count + 1
    WHERE id = NEW.escalation_policy_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_incr_part_count_on_add()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE rotations
    SET participant_count = participant_count + 1
    WHERE id = NEW.rotation_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_insert_basic_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    INSERT INTO auth_subjects (provider_id, subject_id, user_id)
    VALUES ('basic', NEW.username, NEW.user_id)
    ON CONFLICT (provider_id, subject_id) DO UPDATE
    SET user_id = NEW.user_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_insert_ep_state_on_alert_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    INSERT INTO escalation_policy_state (alert_id, service_id, escalation_policy_id)
    SELECT NEW.id, NEW.service_id, svc.escalation_policy_id
    FROM services svc
    JOIN escalation_policies ep ON ep.id = svc.escalation_policy_id AND ep.step_count > 0
    WHERE svc.id = NEW.service_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_insert_ep_state_on_step_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    INSERT INTO escalation_policy_state (alert_id, service_id, escalation_policy_id)
    SELECT a.id, a.service_id, NEW.escalation_policy_id
    FROM alerts a
    JOIN services svc ON
        svc.id = a.service_id AND
        svc.escalation_policy_id = NEW.escalation_policy_id
    WHERE a.status != 'closed';

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_insert_message_status_history()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO message_status_history(message_id, status, timestamp, status_details)
        VALUES(NEW.id, NEW.last_status, NEW.last_status_at, NEW.status_details);
    RETURN new;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_insert_user_last_alert_log()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    INSERT INTO user_last_alert_log (user_id, alert_id, log_id, next_log_id)
    VALUES (NEW.sub_user_id, NEW.alert_id, NEW.id, NEW.id)
    ON CONFLICT DO NOTHING;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_lock_svc_on_force_escalation()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    -- lock service first
    PERFORM 1
    FROM services svc
    WHERE svc.id = NEW.service_id
    FOR UPDATE;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_nc_compat_set_type_val_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.dest ->> 'Type' = 'builtin-slack-channel' THEN
        NEW.type = 'SLACK';
        NEW.value = NEW.dest -> 'Args' ->> 'slack_channel_id';
    ELSIF NEW.dest ->> 'Type' = 'builtin-slack-usergroup' THEN
        NEW.type = 'SLACK_USER_GROUP';
        NEW.value =(NEW.dest -> 'Args' ->> 'slack_usergroup_id') || ':' ||(NEW.dest -> 'Args' ->> 'slack_channel_id');
    ELSIF NEW.dest ->> 'Type' = 'builtin-webhook' THEN
        NEW.type = 'WEBHOOK';
        NEW.value = NEW.dest -> 'Args' ->> 'webhook_url';
    ELSE
        NEW.type = 'DEST';
        NEW.value = gen_random_uuid()::text;
    END IF;
    RETURN new;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_nc_set_dest_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.dest = nc_type_val_to_dest(NEW.type, NEW.value);
    RETURN new;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_notification_rule_same_user()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    _cm_user_id UUID;
BEGIN
    SELECT INTO _cm_user_id user_id
    FROM user_contact_methods
    WHERE id = NEW.contact_method_id;

    IF NEW.user_id != _cm_user_id THEN
        RAISE 'wrong user_id' USING ERRCODE='check_violation', CONSTRAINT='notification_rule_user_id_match';
    END IF;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_notify_config_refresh()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        NOTIFY "/goalert/config-refresh";
        RETURN NEW;
    END;
    $function$
;

CREATE OR REPLACE FUNCTION public.fn_prevent_reopen()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
    BEGIN
        IF OLD.status = 'closed' THEN
            RAISE EXCEPTION 'cannot change status of closed alert';
        END IF;
        RETURN NEW;
    END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_set_ep_state_svc_id_on_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    SELECT service_id INTO NEW.service_id
    FROM alerts
    WHERE id = NEW.alert_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_set_rot_state_pos_on_active_change()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    SELECT position INTO NEW.position
    FROM rotation_participants
    WHERE id = NEW.rotation_participant_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_set_rot_state_pos_on_part_reorder()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    UPDATE rotation_state
    SET position = NEW.position
    WHERE rotation_participant_id = NEW.id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_start_rotation_on_first_part_add()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
    first_part UUID;
BEGIN
    SELECT id
    INTO first_part
    FROM rotation_participants
    WHERE rotation_id = NEW.rotation_id AND position = 0;

    INSERT INTO rotation_state (
        rotation_id, rotation_participant_id, shift_start
    ) VALUES (
        NEW.rotation_id, first_part, now()
    ) ON CONFLICT DO NOTHING;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_track_rotation_updates()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF TG_TABLE_NAME = 'rotations' THEN
        INSERT INTO entity_updates(entity_type, entity_id)
            VALUES('rotation', NEW.id);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO entity_updates(entity_type, entity_id)
            VALUES('rotation', OLD.rotation_id);
        RETURN OLD;
    ELSE
        INSERT INTO entity_updates(entity_type, entity_id)
            VALUES('rotation', NEW.rotation_id);
    END IF;
    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_trig_alert_on_force_escalation()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    UPDATE alerts
    SET "status" = 'triggered'
    WHERE id = NEW.alert_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.fn_update_user_last_alert_log()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

    UPDATE user_last_alert_log last
    SET next_log_id = NEW.id
    WHERE
        last.alert_id = NEW.alert_id AND
        NEW.id > last.next_log_id;

    RETURN NEW;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.move_escalation_policy_step(_id uuid, _new_pos integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
  DECLARE
    _old_pos INT;
    _epid UUID;
  BEGIN
    SELECT step_number, escalation_policy_id into _old_pos, _epid FROM escalation_policy_steps WHERE id = _id;
    IF _old_pos > _new_pos THEN
      UPDATE escalation_policy_steps
      SET step_number = step_number + 1
      WHERE escalation_policy_id = _epid
        AND step_number < _old_pos
        AND step_number >= _new_pos;
    ELSE
      UPDATE escalation_policy_steps
      SET step_number = step_number - 1
      WHERE escalation_policy_id = _epid
        AND step_number > _old_pos
        AND step_number <= _new_pos;
    END IF;
    UPDATE escalation_policy_steps
    SET step_number = _new_pos
    WHERE id = _id;
  END;
  $function$
;

CREATE OR REPLACE FUNCTION public.move_rotation_position(_id uuid, _new_pos integer)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
    DECLARE
        _old_pos INT;
        _rid UUID;
    BEGIN
        SELECT position,rotation_id into _old_pos, _rid FROM rotation_participants WHERE id = _id;
        IF _old_pos > _new_pos THEN
            UPDATE rotation_participants SET position = position + 1 WHERE rotation_id = _rid AND position < _old_pos AND position >= _new_pos;
        ELSE
            UPDATE rotation_participants SET position = position - 1 WHERE rotation_id = _rid AND position > _old_pos AND position <= _new_pos;
        END IF;
        UPDATE rotation_participants SET position = _new_pos WHERE id = _id;
    END;
    $function$
;

CREATE OR REPLACE FUNCTION public.nc_type_val_to_dest(typename enum_notif_channel_type, value text)
 RETURNS jsonb
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF typeName = 'SLACK' THEN
        RETURN jsonb_build_object('Type', 'builtin-slack-channel', 'Args', jsonb_build_object('slack_channel_id', value));
    ELSIF typeName = 'WEBHOOK' THEN
        RETURN jsonb_build_object('Type', 'builtin-webhook', 'Args', jsonb_build_object('webhook_url', value));
    ELSIF typeName = 'SLACK_USER_GROUP' THEN
        RETURN jsonb_build_object('Type', 'builtin-slack-usergroup', 'Args', jsonb_build_object('slack_usergroup_id', split_part(value, ':', 1), 'slack_channel_id', split_part(value, ':', 2)));
    ELSE
        -- throw an error
        RAISE EXCEPTION 'Unknown notification channel type: %', typeName;
    END IF;
END;
$function$
;

CREATE OR REPLACE FUNCTION public.release_user_contact_method_lock(_client_id uuid, _id uuid, success boolean)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
        BEGIN
            DELETE FROM user_contact_method_locks WHERE id = _id AND client_id = _client_id;
            IF success
            THEN
                UPDATE sent_notifications SET sent_at = now() WHERE id = _id;
            ELSE
                DELETE FROM sent_notifications WHERE id = _id;
            END IF;
        END;
    $function$
;

CREATE OR REPLACE FUNCTION public.remove_rotation_participant(_id uuid)
 RETURNS uuid
 LANGUAGE plpgsql
AS $function$
    DECLARE
        _old_pos INT;
        _rid UUID;
    BEGIN
        SELECT position,rotation_id into _old_pos, _rid FROM rotation_participants WHERE id = _id;
        DELETE FROM rotation_participants WHERE id = _id;
        UPDATE rotation_participants SET position = position - 1 WHERE rotation_id = _rid AND position > _old_pos;
        RETURN _rid;
    END;
    $function$
;

CREATE OR REPLACE FUNCTION public.river_job_state_in_bitmask(bitmask bit, state river_job_state)
 RETURNS boolean
 LANGUAGE sql
 IMMUTABLE
AS $function$
    SELECT CASE state
        WHEN 'available' THEN get_bit(bitmask, 7)
        WHEN 'cancelled' THEN get_bit(bitmask, 6)
        WHEN 'completed' THEN get_bit(bitmask, 5)
        WHEN 'discarded' THEN get_bit(bitmask, 4)
        WHEN 'pending' THEN get_bit(bitmask, 3)
        WHEN 'retryable' THEN get_bit(bitmask, 2)
        WHEN 'running' THEN get_bit(bitmask, 1)
        WHEN 'scheduled' THEN get_bit(bitmask, 0)
        ELSE 0
    END = 1;
$function$
;

CREATE OR REPLACE FUNCTION public.update_notification_cycles()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
        BEGIN
			INSERT INTO user_notification_cycles (user_id, alert_id, escalation_level)
			SELECT user_id, alert_id, escalation_level
			FROM on_call_alert_users
			WHERE status = 'triggered'
                AND user_id IS NOT NULL
			ON CONFLICT DO NOTHING;

			UPDATE user_notification_cycles c
			SET escalation_level = a.escalation_level
			FROM
				alerts a,
				user_notification_cycle_state s
			WHERE a.id = c.alert_id
				AND s.user_id = c.user_id
				AND s.alert_id = c.alert_id;

			DELETE FROM user_notification_cycles c
			WHERE (
				SELECT count(notification_rule_id)
				FROM user_notification_cycle_state s
				WHERE s.alert_id = c.alert_id AND s.user_id = c.user_id
				LIMIT 1
			) = 0
				AND c.escalation_level != (SELECT escalation_level FROM alerts WHERE id = c.alert_id);

        END;
    $function$
;

-- Tables

CREATE TABLE alert_data (
	alert_id bigint NOT NULL,
	id bigint DEFAULT nextval('alert_data_id_seq'::regclass) NOT NULL,
	metadata jsonb,
	CONSTRAINT alert_data_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT alert_data_id_key UNIQUE (id),
	CONSTRAINT alert_data_pkey PRIMARY KEY (alert_id)
);

CREATE UNIQUE INDEX alert_data_id_key ON public.alert_data USING btree (id);
CREATE UNIQUE INDEX alert_data_pkey ON public.alert_data USING btree (alert_id);


CREATE TABLE alert_feedback (
	alert_id bigint NOT NULL,
	id bigint DEFAULT nextval('alert_feedback_id_seq'::regclass) NOT NULL,
	noise_reason text NOT NULL,
	CONSTRAINT alert_feedback_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT alert_feedback_id_key UNIQUE (id),
	CONSTRAINT alert_feedback_pkey PRIMARY KEY (alert_id)
);

CREATE UNIQUE INDEX alert_feedback_id_key ON public.alert_feedback USING btree (id);
CREATE UNIQUE INDEX alert_feedback_pkey ON public.alert_feedback USING btree (alert_id);


CREATE TABLE alert_logs (
	alert_id bigint,
	event enum_alert_log_event NOT NULL,
	id bigint DEFAULT nextval('alert_logs_id_seq'::regclass) NOT NULL,
	message text NOT NULL,
	meta json,
	sub_channel_id uuid,
	sub_classifier text DEFAULT ''::text NOT NULL,
	sub_hb_monitor_id uuid,
	sub_integration_key_id uuid,
	sub_type enum_alert_log_subject_type,
	sub_user_id uuid,
	timestamp timestamp with time zone DEFAULT now(),
	CONSTRAINT alert_logs_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX alert_logs_pkey ON public.alert_logs USING btree (id);
CREATE INDEX idx_alert_logs_alert_event ON public.alert_logs USING btree (alert_id, event);
CREATE INDEX idx_alert_logs_alert_id ON public.alert_logs USING btree (alert_id);
CREATE INDEX idx_alert_logs_channel_id ON public.alert_logs USING btree (sub_channel_id);
CREATE INDEX idx_alert_logs_hb_id ON public.alert_logs USING btree (sub_hb_monitor_id);
CREATE INDEX idx_alert_logs_int_id ON public.alert_logs USING btree (sub_integration_key_id);
CREATE INDEX idx_alert_logs_user_id ON public.alert_logs USING btree (sub_user_id);
CREATE INDEX idx_closed_events ON public.alert_logs USING btree ("timestamp") WHERE (event = 'closed'::enum_alert_log_event);


CREATE TABLE alert_metrics (
	alert_id bigint NOT NULL,
	closed_at timestamp with time zone NOT NULL,
	escalated boolean DEFAULT false NOT NULL,
	id bigint DEFAULT nextval('alert_metrics_id_seq'::regclass) NOT NULL,
	service_id uuid NOT NULL,
	time_to_ack interval,
	time_to_close interval,
	CONSTRAINT alert_metrics_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT alert_metrics_id_key UNIQUE (id),
	CONSTRAINT alert_metrics_pkey PRIMARY KEY (alert_id)
);

CREATE INDEX alert_metrics_closed_date_idx ON public.alert_metrics USING btree (date(timezone('UTC'::text, closed_at)));
CREATE UNIQUE INDEX alert_metrics_id_key ON public.alert_metrics USING btree (id);
CREATE UNIQUE INDEX alert_metrics_pkey ON public.alert_metrics USING btree (alert_id);


CREATE TABLE alert_status_subscriptions (
	alert_id bigint NOT NULL,
	channel_id uuid,
	contact_method_id uuid,
	id bigint DEFAULT nextval('alert_status_subscriptions_id_seq'::regclass) NOT NULL,
	last_alert_status enum_alert_status NOT NULL,
	updated_at timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT alert_status_subscriptions_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT alert_status_subscriptions_channel_id_contact_method_id_ale_key UNIQUE (channel_id, contact_method_id, alert_id),
	CONSTRAINT alert_status_subscriptions_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES notification_channels(id) ON DELETE CASCADE,
	CONSTRAINT alert_status_subscriptions_check CHECK ((channel_id IS NULL) <> (contact_method_id IS NULL)),
	CONSTRAINT alert_status_subscriptions_contact_method_id_fkey FOREIGN KEY (contact_method_id) REFERENCES user_contact_methods(id) ON DELETE CASCADE,
	CONSTRAINT alert_status_subscriptions_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX alert_status_subscriptions_channel_id_contact_method_id_ale_key ON public.alert_status_subscriptions USING btree (channel_id, contact_method_id, alert_id);
CREATE UNIQUE INDEX alert_status_subscriptions_pkey ON public.alert_status_subscriptions USING btree (id);
CREATE INDEX alert_status_subscriptions_updated_at_idx ON public.alert_status_subscriptions USING btree (updated_at);


CREATE TABLE alerts (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	dedup_key text,
	details text DEFAULT ''::text NOT NULL,
	escalation_level integer DEFAULT 0 NOT NULL,
	id bigint DEFAULT nextval('alerts_id_seq'::regclass) NOT NULL,
	last_escalation timestamp with time zone DEFAULT now(),
	last_processed timestamp with time zone,
	service_id uuid,
	source enum_alert_source DEFAULT 'manual'::enum_alert_source NOT NULL,
	status enum_alert_status DEFAULT 'triggered'::enum_alert_status NOT NULL,
	summary text NOT NULL,
	CONSTRAINT alerts_pkey PRIMARY KEY (id),
	CONSTRAINT alerts_services_id_fkey FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
	CONSTRAINT dedup_key_only_for_open_alerts CHECK ((status = 'closed'::enum_alert_status) = (dedup_key IS NULL))
);

CREATE UNIQUE INDEX alerts_pkey ON public.alerts USING btree (id);
CREATE INDEX idx_alert_cleanup ON public.alerts USING btree (id, created_at) WHERE (status = 'closed'::enum_alert_status);
CREATE INDEX idx_alert_service_id ON public.alerts USING btree (service_id);
CREATE INDEX idx_dedup_alerts ON public.alerts USING btree (dedup_key);
CREATE UNIQUE INDEX idx_no_alert_duplicates ON public.alerts USING btree (service_id, dedup_key);
CREATE INDEX idx_search_alerts_summary_eng ON public.alerts USING gin (to_tsvector('english'::regconfig, replace(lower(summary), '.'::text, ' '::text)));
CREATE INDEX idx_unacked_alert_service ON public.alerts USING btree (status, service_id);

CREATE TRIGGER trg_10_clear_ep_state_on_alert_close AFTER UPDATE ON public.alerts FOR EACH ROW WHEN (((old.status <> new.status) AND (new.status = 'closed'::enum_alert_status))) EXECUTE FUNCTION fn_clear_ep_state_on_alert_close();
CREATE TRIGGER trg_10_insert_ep_state_on_alert_insert AFTER INSERT ON public.alerts FOR EACH ROW WHEN ((new.status <> 'closed'::enum_alert_status)) EXECUTE FUNCTION fn_insert_ep_state_on_alert_insert();
CREATE TRIGGER trg_20_clear_next_esc_on_alert_ack AFTER UPDATE ON public.alerts FOR EACH ROW WHEN (((new.status <> old.status) AND (old.status = 'active'::enum_alert_status))) EXECUTE FUNCTION fn_clear_next_esc_on_alert_ack();
CREATE TRIGGER trg_clear_dedup_on_close BEFORE UPDATE ON public.alerts FOR EACH ROW WHEN (((new.status <> old.status) AND (new.status = 'closed'::enum_alert_status))) EXECUTE FUNCTION fn_clear_dedup_on_close();
CREATE CONSTRAINT TRIGGER trg_enforce_alert_limit AFTER INSERT ON public.alerts NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_alert_limit();
CREATE TRIGGER trg_prevent_reopen BEFORE UPDATE OF status ON public.alerts FOR EACH ROW EXECUTE FUNCTION fn_prevent_reopen();


CREATE TABLE auth_basic_users (
	id bigint DEFAULT nextval('auth_basic_users_id_seq'::regclass) NOT NULL,
	password_hash text NOT NULL,
	user_id uuid NOT NULL,
	username text NOT NULL,
	CONSTRAINT auth_basic_users_pkey PRIMARY KEY (user_id),
	CONSTRAINT auth_basic_users_uniq_id UNIQUE (id),
	CONSTRAINT auth_basic_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
	CONSTRAINT auth_basic_users_username_key UNIQUE (username)
);

CREATE UNIQUE INDEX auth_basic_users_pkey ON public.auth_basic_users USING btree (user_id);
CREATE UNIQUE INDEX auth_basic_users_uniq_id ON public.auth_basic_users USING btree (id);
CREATE UNIQUE INDEX auth_basic_users_username_key ON public.auth_basic_users USING btree (username);

CREATE TRIGGER trg_insert_basic_user AFTER INSERT ON public.auth_basic_users FOR EACH ROW EXECUTE FUNCTION fn_insert_basic_user();


CREATE TABLE auth_link_requests (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	expires_at timestamp with time zone NOT NULL,
	id uuid NOT NULL,
	metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
	provider_id text NOT NULL,
	subject_id text NOT NULL,
	CONSTRAINT auth_link_requests_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX auth_link_requests_pkey ON public.auth_link_requests USING btree (id);


CREATE TABLE auth_nonce (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	id uuid NOT NULL,
	CONSTRAINT auth_nonce_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX auth_nonce_pkey ON public.auth_nonce USING btree (id);


CREATE TABLE auth_subjects (
	cm_id uuid,
	id bigint DEFAULT nextval('auth_subjects_id_seq'::regclass) NOT NULL,
	provider_id text NOT NULL,
	subject_id text NOT NULL,
	user_id uuid NOT NULL,
	CONSTRAINT auth_subjects_cm_id_fkey FOREIGN KEY (cm_id) REFERENCES user_contact_methods(id) ON DELETE CASCADE,
	CONSTRAINT auth_subjects_pkey PRIMARY KEY (provider_id, subject_id),
	CONSTRAINT auth_subjects_uniq_id UNIQUE (id),
	CONSTRAINT auth_subjects_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX auth_subjects_pkey ON public.auth_subjects USING btree (provider_id, subject_id);
CREATE UNIQUE INDEX auth_subjects_uniq_id ON public.auth_subjects USING btree (id);


CREATE TABLE auth_user_sessions (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	id uuid NOT NULL,
	last_access_at timestamp with time zone DEFAULT now() NOT NULL,
	user_agent text DEFAULT ''::text NOT NULL,
	user_id uuid,
	CONSTRAINT auth_user_sessions_pkey PRIMARY KEY (id),
	CONSTRAINT auth_user_sessions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX auth_user_sessions_pkey ON public.auth_user_sessions USING btree (id);


CREATE TABLE config (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	data bytea NOT NULL,
	id integer DEFAULT nextval('config_id_seq'::regclass) NOT NULL,
	schema integer NOT NULL,
	CONSTRAINT config_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX config_pkey ON public.config USING btree (id);

CREATE TRIGGER trg_config_update AFTER INSERT ON public.config FOR EACH ROW EXECUTE FUNCTION fn_notify_config_refresh();


CREATE TABLE config_limits (
	id enum_limit_type NOT NULL,
	max integer DEFAULT '-1'::integer NOT NULL,
	CONSTRAINT config_limits_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX config_limits_pkey ON public.config_limits USING btree (id);


CREATE TABLE engine_processing_versions (
	state jsonb DEFAULT '{}'::jsonb NOT NULL,
	type_id engine_processing_type NOT NULL,
	version integer DEFAULT 1 NOT NULL,
	CONSTRAINT engine_processing_versions_pkey PRIMARY KEY (type_id)
);

CREATE UNIQUE INDEX engine_processing_versions_pkey ON public.engine_processing_versions USING btree (type_id);


CREATE TABLE entity_updates (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	entity_id uuid NOT NULL,
	entity_type text NOT NULL,
	id bigint DEFAULT nextval('entity_updates_id_seq'::regclass) NOT NULL,
	CONSTRAINT entity_updates_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX entity_updates_pkey ON public.entity_updates USING btree (id);
CREATE INDEX idx_entity_updates_entity_type ON public.entity_updates USING btree (entity_type);


CREATE TABLE ep_step_on_call_users (
	end_time timestamp with time zone,
	ep_step_id uuid NOT NULL,
	id bigint DEFAULT nextval('ep_step_on_call_users_id_seq'::regclass) NOT NULL,
	start_time timestamp with time zone DEFAULT now() NOT NULL,
	user_id uuid NOT NULL,
	CONSTRAINT ep_step_on_call_users_ep_step_id_fkey FOREIGN KEY (ep_step_id) REFERENCES escalation_policy_steps(id) ON DELETE CASCADE,
	CONSTRAINT ep_step_on_call_users_uniq_id UNIQUE (id),
	CONSTRAINT ep_step_on_call_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX ep_step_on_call_users_uniq_id ON public.ep_step_on_call_users USING btree (id);
CREATE UNIQUE INDEX idx_ep_step_on_call ON public.ep_step_on_call_users USING btree (user_id, ep_step_id) WHERE (end_time IS NULL);


CREATE TABLE escalation_policies (
	description text DEFAULT ''::text NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	name text NOT NULL,
	repeat integer DEFAULT 0 NOT NULL,
	step_count integer DEFAULT 0 NOT NULL,
	CONSTRAINT escalation_policies_name_key UNIQUE (name),
	CONSTRAINT escalation_policies_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX escalation_policies_name ON public.escalation_policies USING btree (lower(name));
CREATE UNIQUE INDEX escalation_policies_name_key ON public.escalation_policies USING btree (name);
CREATE UNIQUE INDEX escalation_policies_pkey ON public.escalation_policies USING btree (id);
CREATE INDEX idx_search_escalation_policies_desc_eng ON public.escalation_policies USING gin (to_tsvector('english'::regconfig, replace(lower(description), '.'::text, ' '::text)));
CREATE INDEX idx_search_escalation_policies_name_eng ON public.escalation_policies USING gin (to_tsvector('english'::regconfig, replace(lower(name), '.'::text, ' '::text)));


CREATE TABLE escalation_policy_actions (
	channel_id uuid,
	escalation_policy_step_id uuid NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	rotation_id uuid,
	schedule_id uuid,
	user_id uuid,
	CONSTRAINT epa_no_duplicate_channels UNIQUE (escalation_policy_step_id, channel_id),
	CONSTRAINT epa_no_duplicate_rotations UNIQUE (escalation_policy_step_id, rotation_id),
	CONSTRAINT epa_no_duplicate_schedules UNIQUE (escalation_policy_step_id, schedule_id),
	CONSTRAINT epa_no_duplicate_users UNIQUE (escalation_policy_step_id, user_id),
	CONSTRAINT epa_there_can_only_be_one CHECK ((
CASE
    WHEN user_id IS NOT NULL THEN 1
    ELSE 0
END +
CASE
    WHEN schedule_id IS NOT NULL THEN 1
    ELSE 0
END +
CASE
    WHEN rotation_id IS NOT NULL THEN 1
    ELSE 0
END +
CASE
    WHEN channel_id IS NOT NULL THEN 1
    ELSE 0
END) = 1),
	CONSTRAINT escalation_policy_actions_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES notification_channels(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_actions_escalation_policy_step_id_fkey FOREIGN KEY (escalation_policy_step_id) REFERENCES escalation_policy_steps(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_actions_pkey PRIMARY KEY (id),
	CONSTRAINT escalation_policy_actions_rotation_id_fkey FOREIGN KEY (rotation_id) REFERENCES rotations(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_actions_schedule_id_fkey1 FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_actions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX epa_no_duplicate_channels ON public.escalation_policy_actions USING btree (escalation_policy_step_id, channel_id);
CREATE UNIQUE INDEX epa_no_duplicate_rotations ON public.escalation_policy_actions USING btree (escalation_policy_step_id, rotation_id);
CREATE UNIQUE INDEX epa_no_duplicate_schedules ON public.escalation_policy_actions USING btree (escalation_policy_step_id, schedule_id);
CREATE UNIQUE INDEX epa_no_duplicate_users ON public.escalation_policy_actions USING btree (escalation_policy_step_id, user_id);
CREATE UNIQUE INDEX escalation_policy_actions_pkey ON public.escalation_policy_actions USING btree (id);
CREATE INDEX idx_ep_action_steps ON public.escalation_policy_actions USING btree (escalation_policy_step_id);

CREATE CONSTRAINT TRIGGER trg_enforce_ep_step_action_limit AFTER INSERT ON public.escalation_policy_actions NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_ep_step_action_limit();


CREATE TABLE escalation_policy_state (
	alert_id bigint NOT NULL,
	escalation_policy_id uuid NOT NULL,
	escalation_policy_step_id uuid,
	escalation_policy_step_number integer DEFAULT 0 NOT NULL,
	force_escalation boolean DEFAULT false NOT NULL,
	id bigint DEFAULT nextval('escalation_policy_state_id_seq'::regclass) NOT NULL,
	last_escalation timestamp with time zone,
	loop_count integer DEFAULT 0 NOT NULL,
	next_escalation timestamp with time zone,
	service_id uuid NOT NULL,
	CONSTRAINT escalation_policy_state_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_state_escalation_policy_id_fkey FOREIGN KEY (escalation_policy_id) REFERENCES escalation_policies(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_state_escalation_policy_step_id_fkey FOREIGN KEY (escalation_policy_step_id) REFERENCES escalation_policy_steps(id) ON DELETE SET NULL,
	CONSTRAINT escalation_policy_state_pkey PRIMARY KEY (alert_id),
	CONSTRAINT escalation_policy_state_service_id_fkey FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_state_uniq_id UNIQUE (id),
	CONSTRAINT svc_ep_fkey FOREIGN KEY (service_id, escalation_policy_id) REFERENCES services(id, escalation_policy_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
);

CREATE INDEX escalation_policy_state_next_escalation_force_escalation_idx ON public.escalation_policy_state USING btree (next_escalation, force_escalation);
CREATE UNIQUE INDEX escalation_policy_state_pkey ON public.escalation_policy_state USING btree (alert_id);
CREATE UNIQUE INDEX escalation_policy_state_uniq_id ON public.escalation_policy_state USING btree (id);
CREATE INDEX idx_escalation_policy_state_policy_ids ON public.escalation_policy_state USING btree (escalation_policy_id, service_id);

CREATE TRIGGER trg_10_set_ep_state_svc_id_on_insert BEFORE INSERT ON public.escalation_policy_state FOR EACH ROW WHEN ((new.service_id IS NULL)) EXECUTE FUNCTION fn_set_ep_state_svc_id_on_insert();
CREATE TRIGGER trg_20_lock_svc_on_force_escalation BEFORE UPDATE ON public.escalation_policy_state FOR EACH ROW WHEN (((new.force_escalation <> old.force_escalation) AND new.force_escalation)) EXECUTE FUNCTION fn_lock_svc_on_force_escalation();
CREATE TRIGGER trg_30_trig_alert_on_force_escalation AFTER UPDATE ON public.escalation_policy_state FOR EACH ROW WHEN (((new.force_escalation <> old.force_escalation) AND new.force_escalation)) EXECUTE FUNCTION fn_trig_alert_on_force_escalation();


CREATE TABLE escalation_policy_steps (
	delay integer DEFAULT 1 NOT NULL,
	escalation_policy_id uuid NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	step_number integer DEFAULT '-1'::integer NOT NULL,
	CONSTRAINT escalation_policy_steps_escalation_policy_id_fkey FOREIGN KEY (escalation_policy_id) REFERENCES escalation_policies(id) ON DELETE CASCADE,
	CONSTRAINT escalation_policy_steps_escalation_policy_id_step_number_key UNIQUE (escalation_policy_id, step_number) DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT escalation_policy_steps_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX escalation_policy_steps_escalation_policy_id_step_number_key ON public.escalation_policy_steps USING btree (escalation_policy_id, step_number);
CREATE UNIQUE INDEX escalation_policy_steps_pkey ON public.escalation_policy_steps USING btree (id);
CREATE INDEX idx_ep_step_policies ON public.escalation_policy_steps USING btree (escalation_policy_id);

CREATE TRIGGER trg_10_decr_ep_step_count_on_del BEFORE DELETE ON public.escalation_policy_steps FOR EACH ROW EXECUTE FUNCTION fn_decr_ep_step_count_on_del();
CREATE TRIGGER trg_10_incr_ep_step_count_on_add BEFORE INSERT ON public.escalation_policy_steps FOR EACH ROW EXECUTE FUNCTION fn_incr_ep_step_count_on_add();
CREATE TRIGGER trg_10_insert_ep_state_on_step_insert AFTER INSERT ON public.escalation_policy_steps FOR EACH ROW WHEN ((new.step_number = 0)) EXECUTE FUNCTION fn_insert_ep_state_on_step_insert();
CREATE TRIGGER trg_decr_ep_step_number_on_delete AFTER DELETE ON public.escalation_policy_steps FOR EACH ROW EXECUTE FUNCTION fn_decr_ep_step_number_on_delete();
CREATE CONSTRAINT TRIGGER trg_enforce_ep_step_limit AFTER INSERT ON public.escalation_policy_steps NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_ep_step_limit();
CREATE CONSTRAINT TRIGGER trg_ep_step_number_no_gaps AFTER UPDATE ON public.escalation_policy_steps DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fn_enforce_ep_step_number_no_gaps();
CREATE TRIGGER trg_inc_ep_step_number_on_insert BEFORE INSERT ON public.escalation_policy_steps FOR EACH ROW EXECUTE FUNCTION fn_inc_ep_step_number_on_insert();


CREATE TABLE gorp_migrations (
	applied_at timestamp with time zone,
	id text NOT NULL,
	CONSTRAINT gorp_migrations_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX gorp_migrations_pkey ON public.gorp_migrations USING btree (id);


CREATE TABLE gql_api_key_usage (
	api_key_id uuid,
	id bigint DEFAULT nextval('gql_api_key_usage_id_seq'::regclass) NOT NULL,
	ip_address inet,
	used_at timestamp with time zone DEFAULT now() NOT NULL,
	user_agent text,
	CONSTRAINT gql_api_key_usage_api_key_id_fkey FOREIGN KEY (api_key_id) REFERENCES gql_api_keys(id) ON DELETE CASCADE,
	CONSTRAINT gql_api_key_usage_api_key_id_key UNIQUE (api_key_id),
	CONSTRAINT gql_api_key_usage_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX gql_api_key_usage_api_key_id_key ON public.gql_api_key_usage USING btree (api_key_id);
CREATE UNIQUE INDEX gql_api_key_usage_pkey ON public.gql_api_key_usage USING btree (id);


CREATE TABLE gql_api_keys (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	created_by uuid,
	deleted_at timestamp with time zone,
	deleted_by uuid,
	description text NOT NULL,
	expires_at timestamp with time zone NOT NULL,
	id uuid NOT NULL,
	name text NOT NULL,
	policy json NOT NULL,
	updated_at timestamp with time zone DEFAULT now() NOT NULL,
	updated_by uuid,
	CONSTRAINT gql_api_keys_created_by_fkey FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
	CONSTRAINT gql_api_keys_deleted_by_fkey FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL,
	CONSTRAINT gql_api_keys_pkey PRIMARY KEY (id),
	CONSTRAINT gql_api_keys_updated_by_fkey FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE UNIQUE INDEX gql_api_keys_name_key ON public.gql_api_keys USING btree (name) WHERE (deleted_at IS NULL);
CREATE UNIQUE INDEX gql_api_keys_pkey ON public.gql_api_keys USING btree (id);


CREATE TABLE heartbeat_monitors (
	additional_details text,
	heartbeat_interval interval NOT NULL,
	id uuid NOT NULL,
	last_heartbeat timestamp with time zone,
	last_state enum_heartbeat_state DEFAULT 'inactive'::enum_heartbeat_state NOT NULL,
	muted text,
	name text NOT NULL,
	service_id uuid NOT NULL,
	CONSTRAINT heartbeat_monitors_pkey PRIMARY KEY (id),
	CONSTRAINT heartbeat_monitors_service_id_fkey FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX heartbeat_monitor_name_service_id ON public.heartbeat_monitors USING btree (lower(name), service_id);
CREATE UNIQUE INDEX heartbeat_monitors_pkey ON public.heartbeat_monitors USING btree (id);
CREATE INDEX idx_heartbeat_monitor_service ON public.heartbeat_monitors USING btree (service_id);

CREATE CONSTRAINT TRIGGER trg_enforce_heartbeat_monitor_limit AFTER INSERT ON public.heartbeat_monitors NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_heartbeat_limit();


CREATE TABLE integration_keys (
	external_system_name text,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	name text NOT NULL,
	service_id uuid NOT NULL,
	type enum_integration_keys_type NOT NULL,
	CONSTRAINT integration_keys_pkey PRIMARY KEY (id),
	CONSTRAINT integration_keys_services_id_fkey FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_int_key_name_svc_ext ON public.integration_keys USING btree (lower(name), service_id, COALESCE(external_system_name, ''::text));
CREATE INDEX idx_integration_key_service ON public.integration_keys USING btree (service_id);
CREATE UNIQUE INDEX integration_keys_pkey ON public.integration_keys USING btree (id);

CREATE CONSTRAINT TRIGGER trg_enforce_integration_key_limit AFTER INSERT ON public.integration_keys NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_integration_key_limit();


CREATE TABLE keyring (
	id text NOT NULL,
	next_key bytea NOT NULL,
	next_rotation timestamp with time zone,
	rotation_count bigint NOT NULL,
	signing_key bytea NOT NULL,
	verification_keys bytea NOT NULL,
	CONSTRAINT keyring_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX keyring_pkey ON public.keyring USING btree (id);


CREATE TABLE labels (
	id bigint DEFAULT nextval('labels_id_seq'::regclass) NOT NULL,
	key text NOT NULL,
	tgt_service_id uuid NOT NULL,
	value text NOT NULL,
	CONSTRAINT labels_pkey PRIMARY KEY (id),
	CONSTRAINT labels_tgt_service_id_fkey FOREIGN KEY (tgt_service_id) REFERENCES services(id) ON DELETE CASCADE,
	CONSTRAINT labels_tgt_service_id_key_key UNIQUE (tgt_service_id, key)
);

CREATE INDEX idx_labels_service_id ON public.labels USING btree (tgt_service_id);
CREATE UNIQUE INDEX labels_pkey ON public.labels USING btree (id);
CREATE UNIQUE INDEX labels_tgt_service_id_key_key ON public.labels USING btree (tgt_service_id, key);


CREATE TABLE message_status_history (
	id bigint DEFAULT nextval('message_status_history_id_seq'::regclass) NOT NULL,
	message_id uuid NOT NULL,
	status enum_outgoing_messages_status NOT NULL,
	status_details text NOT NULL,
	timestamp timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT message_status_history_message_id_fkey FOREIGN KEY (message_id) REFERENCES outgoing_messages(id) ON DELETE CASCADE,
	CONSTRAINT message_status_history_pkey PRIMARY KEY (id)
);

CREATE INDEX message_status_history_message_id_idx ON public.message_status_history USING btree (message_id);
CREATE UNIQUE INDEX message_status_history_pkey ON public.message_status_history USING btree (id);


CREATE TABLE notification_channel_duplicates (
	id bigint DEFAULT nextval('notification_channel_duplicates_id_seq'::regclass) NOT NULL,
	new_id uuid NOT NULL,
	old_created_at timestamp with time zone NOT NULL,
	old_id uuid NOT NULL,
	CONSTRAINT notification_channel_duplicates_id_key UNIQUE (id),
	CONSTRAINT notification_channel_duplicates_pkey PRIMARY KEY (old_id)
);

CREATE UNIQUE INDEX notification_channel_duplicates_id_key ON public.notification_channel_duplicates USING btree (id);
CREATE UNIQUE INDEX notification_channel_duplicates_pkey ON public.notification_channel_duplicates USING btree (old_id);


CREATE TABLE notification_channels (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	dest jsonb NOT NULL,
	id uuid NOT NULL,
	meta jsonb DEFAULT '{}'::jsonb NOT NULL,
	name text NOT NULL,
	type enum_notif_channel_type NOT NULL,
	value text NOT NULL,
	CONSTRAINT nc_unique_type_value UNIQUE (type, value),
	CONSTRAINT notification_channels_dest_key UNIQUE (dest),
	CONSTRAINT notification_channels_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX nc_unique_type_value ON public.notification_channels USING btree (type, value);
CREATE UNIQUE INDEX notification_channels_dest_key ON public.notification_channels USING btree (dest);
CREATE UNIQUE INDEX notification_channels_pkey ON public.notification_channels USING btree (id);

CREATE TRIGGER trg_10_nc_compat_set_type_val_on_insert BEFORE INSERT ON public.notification_channels FOR EACH ROW WHEN ((new.dest IS NOT NULL)) EXECUTE FUNCTION fn_nc_compat_set_type_val_on_insert();
CREATE TRIGGER trg_10_nc_compat_set_type_val_on_update BEFORE UPDATE ON public.notification_channels FOR EACH ROW WHEN ((new.dest <> old.dest)) EXECUTE FUNCTION fn_nc_compat_set_type_val_on_insert();
CREATE TRIGGER trg_10_nc_set_dest_on_insert BEFORE INSERT ON public.notification_channels FOR EACH ROW WHEN ((new.dest IS NULL)) EXECUTE FUNCTION fn_nc_set_dest_on_insert();
CREATE TRIGGER trg_10_nc_set_dest_on_update AFTER UPDATE ON public.notification_channels FOR EACH ROW WHEN (((new.dest = old.dest) AND ((new.type <> 'DEST'::enum_notif_channel_type) AND ((new.value <> old.value) OR (new.type <> old.type))))) EXECUTE FUNCTION fn_nc_set_dest_on_insert();


CREATE TABLE notification_policy_cycles (
	alert_id integer NOT NULL,
	checked boolean DEFAULT true NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	last_tick timestamp with time zone,
	repeat_count integer DEFAULT 0 NOT NULL,
	started_at timestamp with time zone DEFAULT now() NOT NULL,
	user_id uuid NOT NULL,
	CONSTRAINT notification_policy_cycles_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT notification_policy_cycles_pkey PRIMARY KEY (id),
	CONSTRAINT notification_policy_cycles_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_np_cycle_alert_id ON public.notification_policy_cycles USING btree (alert_id);
CREATE UNIQUE INDEX notification_policy_cycles_pkey ON public.notification_policy_cycles USING btree (id);


CREATE TABLE outgoing_messages (
	alert_id bigint,
	alert_log_id bigint,
	channel_id uuid,
	contact_method_id uuid,
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	cycle_id uuid,
	escalation_policy_id uuid,
	fired_at timestamp with time zone,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	last_status enum_outgoing_messages_status DEFAULT 'pending'::enum_outgoing_messages_status NOT NULL,
	last_status_at timestamp with time zone DEFAULT now(),
	message_type enum_outgoing_messages_type NOT NULL,
	next_retry_at timestamp with time zone,
	provider_msg_id text,
	provider_seq integer DEFAULT 0 NOT NULL,
	retry_count integer DEFAULT 0 NOT NULL,
	schedule_id uuid,
	sending_deadline timestamp with time zone,
	sent_at timestamp with time zone,
	service_id uuid,
	src_value text,
	status_alert_ids bigint[],
	status_details text DEFAULT ''::text NOT NULL,
	user_id uuid,
	user_verification_code_id uuid,
	CONSTRAINT om_alert_svc_ep_ids CHECK (message_type <> 'alert_notification'::enum_outgoing_messages_type OR alert_id IS NOT NULL AND service_id IS NOT NULL AND escalation_policy_id IS NOT NULL),
	CONSTRAINT om_no_status_bundles CHECK (message_type <> 'alert_status_update_bundle'::enum_outgoing_messages_type OR last_status <> 'pending'::enum_outgoing_messages_status),
	CONSTRAINT om_pending_no_fired_no_sent CHECK (last_status <> 'pending'::enum_outgoing_messages_status OR fired_at IS NULL AND sent_at IS NULL),
	CONSTRAINT om_processed_no_fired_sent CHECK ((last_status = ANY (ARRAY['pending'::enum_outgoing_messages_status, 'sending'::enum_outgoing_messages_status, 'failed'::enum_outgoing_messages_status, 'bundled'::enum_outgoing_messages_status])) OR fired_at IS NULL AND sent_at IS NOT NULL),
	CONSTRAINT om_sending_deadline_reqd CHECK (last_status <> 'sending'::enum_outgoing_messages_status OR sending_deadline IS NOT NULL),
	CONSTRAINT om_sending_fired_no_sent CHECK (last_status <> 'sending'::enum_outgoing_messages_status OR fired_at IS NOT NULL AND sent_at IS NULL),
	CONSTRAINT om_status_alert_ids CHECK (message_type <> 'alert_status_update_bundle'::enum_outgoing_messages_type OR status_alert_ids IS NOT NULL),
	CONSTRAINT om_status_update_log_id CHECK (message_type <> 'alert_status_update'::enum_outgoing_messages_type OR alert_log_id IS NOT NULL),
	CONSTRAINT om_user_cm_or_channel CHECK (user_id IS NOT NULL AND contact_method_id IS NOT NULL AND channel_id IS NULL OR channel_id IS NOT NULL AND contact_method_id IS NULL AND user_id IS NULL),
	CONSTRAINT outgoing_messages_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_alert_log_id_fkey FOREIGN KEY (alert_log_id) REFERENCES alert_logs(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_channel_id_fkey FOREIGN KEY (channel_id) REFERENCES notification_channels(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_contact_method_id_fkey FOREIGN KEY (contact_method_id) REFERENCES user_contact_methods(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_cycle_id_fkey FOREIGN KEY (cycle_id) REFERENCES notification_policy_cycles(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_escalation_policy_id_fkey FOREIGN KEY (escalation_policy_id) REFERENCES escalation_policies(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_pkey PRIMARY KEY (id),
	CONSTRAINT outgoing_messages_schedule_id_fkey FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_service_id_fkey FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
	CONSTRAINT outgoing_messages_user_verification_code_id_fkey FOREIGN KEY (user_verification_code_id) REFERENCES user_verification_codes(id) ON DELETE CASCADE,
	CONSTRAINT verify_needs_id CHECK (message_type <> 'verification_message'::enum_outgoing_messages_type OR user_verification_code_id IS NOT NULL)
);

CREATE INDEX idx_om_alert_log_id ON public.outgoing_messages USING btree (alert_log_id);
CREATE INDEX idx_om_alert_sent ON public.outgoing_messages USING btree (alert_id, sent_at);
CREATE INDEX idx_om_cm_sent ON public.outgoing_messages USING btree (contact_method_id, sent_at);
CREATE INDEX idx_om_ep_sent ON public.outgoing_messages USING btree (escalation_policy_id, sent_at);
CREATE INDEX idx_om_last_status_sent ON public.outgoing_messages USING btree (last_status, sent_at);
CREATE INDEX idx_om_service_sent ON public.outgoing_messages USING btree (service_id, sent_at);
CREATE INDEX idx_om_user_sent ON public.outgoing_messages USING btree (user_id, sent_at);
CREATE INDEX idx_om_vcode_id ON public.outgoing_messages USING btree (user_verification_code_id);
CREATE INDEX idx_outgoing_messages_notif_cycle ON public.outgoing_messages USING btree (cycle_id);
CREATE UNIQUE INDEX idx_outgoing_messages_provider_msg_id ON public.outgoing_messages USING btree (provider_msg_id);
CREATE INDEX om_cm_time_test_verify_idx ON public.outgoing_messages USING btree (contact_method_id, created_at) WHERE (message_type = ANY (ARRAY['test_notification'::enum_outgoing_messages_type, 'verification_message'::enum_outgoing_messages_type]));
CREATE UNIQUE INDEX outgoing_messages_pkey ON public.outgoing_messages USING btree (id);

CREATE TRIGGER trg_insert_message_status_history AFTER INSERT ON public.outgoing_messages FOR EACH ROW EXECUTE FUNCTION fn_insert_message_status_history();
CREATE TRIGGER trg_update_message_status_history AFTER UPDATE OF last_status ON public.outgoing_messages FOR EACH ROW EXECUTE FUNCTION fn_insert_message_status_history();


CREATE TABLE pending_signals (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	dest_id uuid NOT NULL,
	id integer DEFAULT nextval('pending_signals_id_seq'::regclass) NOT NULL,
	message_id uuid,
	params jsonb NOT NULL,
	service_id uuid NOT NULL,
	CONSTRAINT pending_signals_dest_id_fkey FOREIGN KEY (dest_id) REFERENCES notification_channels(id) ON DELETE CASCADE,
	CONSTRAINT pending_signals_message_id_fkey FOREIGN KEY (message_id) REFERENCES outgoing_messages(id) ON DELETE CASCADE,
	CONSTRAINT pending_signals_message_id_key UNIQUE (message_id),
	CONSTRAINT pending_signals_pkey PRIMARY KEY (id),
	CONSTRAINT pending_signals_service_id_fkey FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
);

CREATE INDEX idx_pending_signals_service_dest_id ON public.pending_signals USING btree (service_id, dest_id);
CREATE UNIQUE INDEX pending_signals_message_id_key ON public.pending_signals USING btree (message_id);
CREATE UNIQUE INDEX pending_signals_pkey ON public.pending_signals USING btree (id);

CREATE CONSTRAINT TRIGGER trg_enforce_signals_per_dest_per_service_limit AFTER INSERT ON public.pending_signals NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_signals_per_dest_per_service_limit();
CREATE CONSTRAINT TRIGGER trg_enforce_signals_per_service_limit AFTER INSERT ON public.pending_signals NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_signals_per_service_limit();


CREATE TABLE region_ids (
	id integer DEFAULT nextval('region_ids_id_seq'::regclass) NOT NULL,
	name text NOT NULL,
	CONSTRAINT region_ids_id_key UNIQUE (id),
	CONSTRAINT region_ids_pkey PRIMARY KEY (name)
);

CREATE UNIQUE INDEX region_ids_id_key ON public.region_ids USING btree (id);
CREATE UNIQUE INDEX region_ids_pkey ON public.region_ids USING btree (name);


CREATE TABLE river_client (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	id text NOT NULL,
	metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
	paused_at timestamp with time zone,
	updated_at timestamp with time zone NOT NULL,
	CONSTRAINT name_length CHECK (char_length(id) > 0 AND char_length(id) < 128),
	CONSTRAINT river_client_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX river_client_pkey ON public.river_client USING btree (id);


CREATE TABLE river_client_queue (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	id bigint DEFAULT nextval('river_client_queue_id_seq'::regclass) NOT NULL,
	max_workers bigint DEFAULT 0 NOT NULL,
	metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
	name text NOT NULL,
	num_jobs_completed bigint DEFAULT 0 NOT NULL,
	num_jobs_running bigint DEFAULT 0 NOT NULL,
	river_client_id text NOT NULL,
	updated_at timestamp with time zone NOT NULL,
	CONSTRAINT name_length CHECK (char_length(name) > 0 AND char_length(name) < 128),
	CONSTRAINT num_jobs_completed_zero_or_positive CHECK (num_jobs_completed >= 0),
	CONSTRAINT num_jobs_running_zero_or_positive CHECK (num_jobs_running >= 0),
	CONSTRAINT river_client_queue_id_key UNIQUE (id),
	CONSTRAINT river_client_queue_pkey PRIMARY KEY (river_client_id, name),
	CONSTRAINT river_client_queue_river_client_id_fkey FOREIGN KEY (river_client_id) REFERENCES river_client(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX river_client_queue_id_key ON public.river_client_queue USING btree (id);
CREATE UNIQUE INDEX river_client_queue_pkey ON public.river_client_queue USING btree (river_client_id, name);


CREATE TABLE river_job (
	args jsonb NOT NULL,
	attempt smallint DEFAULT 0 NOT NULL,
	attempted_at timestamp with time zone,
	attempted_by text[],
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	errors jsonb[],
	finalized_at timestamp with time zone,
	id bigint DEFAULT nextval('river_job_id_seq'::regclass) NOT NULL,
	kind text NOT NULL,
	max_attempts smallint NOT NULL,
	metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
	priority smallint DEFAULT 1 NOT NULL,
	queue text DEFAULT 'default'::text NOT NULL,
	scheduled_at timestamp with time zone DEFAULT now() NOT NULL,
	state river_job_state DEFAULT 'available'::river_job_state NOT NULL,
	tags character varying(255)[] DEFAULT '{}'::character varying[] NOT NULL,
	unique_key bytea,
	unique_states bit(8),
	CONSTRAINT finalized_or_finalized_at_null CHECK (finalized_at IS NULL AND (state <> ALL (ARRAY['cancelled'::river_job_state, 'completed'::river_job_state, 'discarded'::river_job_state])) OR finalized_at IS NOT NULL AND (state = ANY (ARRAY['cancelled'::river_job_state, 'completed'::river_job_state, 'discarded'::river_job_state]))),
	CONSTRAINT kind_length CHECK (char_length(kind) > 0 AND char_length(kind) < 128),
	CONSTRAINT max_attempts_is_positive CHECK (max_attempts > 0),
	CONSTRAINT priority_in_range CHECK (priority >= 1 AND priority <= 4),
	CONSTRAINT queue_length CHECK (char_length(queue) > 0 AND char_length(queue) < 128),
	CONSTRAINT river_job_pkey PRIMARY KEY (id)
);

CREATE INDEX river_job_args_index ON public.river_job USING gin (args);
CREATE INDEX river_job_kind ON public.river_job USING btree (kind);
CREATE INDEX river_job_metadata_index ON public.river_job USING gin (metadata);
CREATE UNIQUE INDEX river_job_pkey ON public.river_job USING btree (id);
CREATE INDEX river_job_prioritized_fetching_index ON public.river_job USING btree (state, queue, priority, scheduled_at, id);
CREATE INDEX river_job_state_and_finalized_at_index ON public.river_job USING btree (state, finalized_at) WHERE (finalized_at IS NOT NULL);
CREATE UNIQUE INDEX river_job_unique_idx ON public.river_job USING btree (unique_key) WHERE ((unique_key IS NOT NULL) AND (unique_states IS NOT NULL) AND river_job_state_in_bitmask(unique_states, state));


CREATE TABLE river_leader (
	elected_at timestamp with time zone NOT NULL,
	expires_at timestamp with time zone NOT NULL,
	id bigint DEFAULT nextval('river_leader_id_seq'::regclass) NOT NULL,
	leader_id text NOT NULL,
	name text DEFAULT 'default'::text NOT NULL,
	CONSTRAINT leader_id_length CHECK (char_length(leader_id) > 0 AND char_length(leader_id) < 128),
	CONSTRAINT name_length CHECK (name = 'default'::text),
	CONSTRAINT river_leader_id_key UNIQUE (id),
	CONSTRAINT river_leader_pkey PRIMARY KEY (name)
);

CREATE UNIQUE INDEX river_leader_id_key ON public.river_leader USING btree (id);
CREATE UNIQUE INDEX river_leader_pkey ON public.river_leader USING btree (name);


CREATE TABLE river_queue (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	id bigint DEFAULT nextval('river_queue_id_seq'::regclass) NOT NULL,
	metadata jsonb DEFAULT '{}'::jsonb NOT NULL,
	name text NOT NULL,
	paused_at timestamp with time zone,
	updated_at timestamp with time zone NOT NULL,
	CONSTRAINT river_queue_id_key UNIQUE (id),
	CONSTRAINT river_queue_pkey PRIMARY KEY (name)
);

CREATE UNIQUE INDEX river_queue_id_key ON public.river_queue USING btree (id);
CREATE UNIQUE INDEX river_queue_pkey ON public.river_queue USING btree (name);


CREATE TABLE rotation_participants (
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	position integer NOT NULL,
	rotation_id uuid NOT NULL,
	user_id uuid NOT NULL,
	CONSTRAINT rotation_participants_pkey PRIMARY KEY (id),
	CONSTRAINT rotation_participants_rotation_id_fkey FOREIGN KEY (rotation_id) REFERENCES rotations(id) ON DELETE CASCADE,
	CONSTRAINT rotation_participants_rotation_id_position_key UNIQUE (rotation_id, "position") DEFERRABLE INITIALLY DEFERRED,
	CONSTRAINT rotation_participants_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_participant_rotation ON public.rotation_participants USING btree (rotation_id);
CREATE UNIQUE INDEX rotation_participants_pkey ON public.rotation_participants USING btree (id);
CREATE UNIQUE INDEX rotation_participants_rotation_id_position_key ON public.rotation_participants USING btree (rotation_id, "position");

CREATE TRIGGER trg_10_decr_part_count_on_del BEFORE DELETE ON public.rotation_participants FOR EACH ROW EXECUTE FUNCTION fn_decr_part_count_on_del();
CREATE TRIGGER trg_20_decr_rot_part_position_on_delete AFTER DELETE ON public.rotation_participants FOR EACH ROW EXECUTE FUNCTION fn_decr_rot_part_position_on_delete();
CREATE TRIGGER trg_30_advance_or_end_rot_on_part_del BEFORE DELETE ON public.rotation_participants FOR EACH ROW EXECUTE FUNCTION fn_advance_or_end_rot_on_part_del();
CREATE CONSTRAINT TRIGGER trg_enforce_rot_part_position_no_gaps AFTER UPDATE ON public.rotation_participants DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fn_enforce_rot_part_position_no_gaps();
CREATE CONSTRAINT TRIGGER trg_enforce_rotation_participant_limit AFTER INSERT ON public.rotation_participants NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_rotation_participant_limit();
CREATE TRIGGER trg_inc_rot_part_position_on_insert BEFORE INSERT ON public.rotation_participants FOR EACH ROW EXECUTE FUNCTION fn_inc_rot_part_position_on_insert();
CREATE TRIGGER trg_incr_part_count_on_add BEFORE INSERT ON public.rotation_participants FOR EACH ROW EXECUTE FUNCTION fn_incr_part_count_on_add();
CREATE TRIGGER trg_set_rot_state_pos_on_part_reorder BEFORE UPDATE ON public.rotation_participants FOR EACH ROW WHEN ((new."position" <> old."position")) EXECUTE FUNCTION fn_set_rot_state_pos_on_part_reorder();
CREATE TRIGGER trg_start_rotation_on_first_part_add AFTER INSERT ON public.rotation_participants FOR EACH ROW EXECUTE FUNCTION fn_start_rotation_on_first_part_add();
CREATE TRIGGER trg_track_rotation_part_updates AFTER INSERT OR DELETE OR UPDATE ON public.rotation_participants FOR EACH ROW EXECUTE FUNCTION fn_track_rotation_updates();


CREATE TABLE rotation_state (
	id bigint DEFAULT nextval('rotation_state_id_seq'::regclass) NOT NULL,
	position integer DEFAULT 0 NOT NULL,
	rotation_id uuid NOT NULL,
	rotation_participant_id uuid NOT NULL,
	shift_start timestamp with time zone NOT NULL,
	version integer DEFAULT 2 NOT NULL,
	CONSTRAINT rotation_state_pkey PRIMARY KEY (rotation_id),
	CONSTRAINT rotation_state_rotation_id_fkey FOREIGN KEY (rotation_id) REFERENCES rotations(id) ON DELETE CASCADE,
	CONSTRAINT rotation_state_rotation_participant_id_fkey FOREIGN KEY (rotation_participant_id) REFERENCES rotation_participants(id) DEFERRABLE,
	CONSTRAINT rotation_state_uniq_id UNIQUE (id)
);

CREATE UNIQUE INDEX rotation_state_pkey ON public.rotation_state USING btree (rotation_id);
CREATE UNIQUE INDEX rotation_state_uniq_id ON public.rotation_state USING btree (id);

CREATE TRIGGER trg_set_rot_state_pos_on_active_change BEFORE UPDATE ON public.rotation_state FOR EACH ROW WHEN ((new.rotation_participant_id <> old.rotation_participant_id)) EXECUTE FUNCTION fn_set_rot_state_pos_on_active_change();
CREATE TRIGGER trg_track_rotation_state_updates AFTER UPDATE ON public.rotation_state FOR EACH ROW EXECUTE FUNCTION fn_track_rotation_updates();


CREATE TABLE rotations (
	description text DEFAULT ''::text NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	last_processed timestamp with time zone,
	name text NOT NULL,
	participant_count integer DEFAULT 0 NOT NULL,
	shift_length bigint DEFAULT 1 NOT NULL,
	start_time timestamp with time zone DEFAULT now() NOT NULL,
	time_zone text NOT NULL,
	type enum_rotation_type NOT NULL,
	CONSTRAINT rotations_name_unique UNIQUE (name),
	CONSTRAINT rotations_pkey PRIMARY KEY (id),
	CONSTRAINT rotations_shift_length_check CHECK (shift_length > 0)
);

CREATE INDEX idx_search_rotations_desc_eng ON public.rotations USING gin (to_tsvector('english'::regconfig, replace(lower(description), '.'::text, ' '::text)));
CREATE INDEX idx_search_rotations_name_eng ON public.rotations USING gin (to_tsvector('english'::regconfig, replace(lower(name), '.'::text, ' '::text)));
CREATE UNIQUE INDEX rotations_name ON public.rotations USING btree (lower(name));
CREATE UNIQUE INDEX rotations_name_unique ON public.rotations USING btree (name);
CREATE UNIQUE INDEX rotations_pkey ON public.rotations USING btree (id);

CREATE TRIGGER trg_track_rotation_updates AFTER INSERT OR UPDATE ON public.rotations FOR EACH ROW EXECUTE FUNCTION fn_track_rotation_updates();


CREATE TABLE schedule_data (
	data jsonb NOT NULL,
	id bigint DEFAULT nextval('schedule_data_id_seq'::regclass) NOT NULL,
	last_cleanup_at timestamp with time zone,
	schedule_id uuid NOT NULL,
	CONSTRAINT schedule_data_id_key UNIQUE (id),
	CONSTRAINT schedule_data_pkey PRIMARY KEY (schedule_id),
	CONSTRAINT schedule_data_schedule_id_fkey FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX schedule_data_id_key ON public.schedule_data USING btree (id);
CREATE UNIQUE INDEX schedule_data_pkey ON public.schedule_data USING btree (schedule_id);


CREATE TABLE schedule_on_call_users (
	end_time timestamp with time zone,
	id bigint DEFAULT nextval('schedule_on_call_users_id_seq'::regclass) NOT NULL,
	schedule_id uuid NOT NULL,
	start_time timestamp with time zone DEFAULT now() NOT NULL,
	user_id uuid NOT NULL,
	CONSTRAINT schedule_on_call_users_check CHECK (end_time IS NULL OR end_time > start_time),
	CONSTRAINT schedule_on_call_users_schedule_id_fkey FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
	CONSTRAINT schedule_on_call_users_uniq_id UNIQUE (id),
	CONSTRAINT schedule_on_call_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_sched_oncall_times ON public.schedule_on_call_users USING spgist (tstzrange(start_time, end_time));
CREATE UNIQUE INDEX idx_schedule_on_call_once ON public.schedule_on_call_users USING btree (schedule_id, user_id) WHERE (end_time IS NULL);
CREATE UNIQUE INDEX schedule_on_call_users_uniq_id ON public.schedule_on_call_users USING btree (id);


CREATE TABLE schedule_rules (
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	end_time time without time zone DEFAULT '23:59:59'::time without time zone NOT NULL,
	friday boolean DEFAULT true NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	is_active boolean DEFAULT false NOT NULL,
	monday boolean DEFAULT true NOT NULL,
	saturday boolean DEFAULT true NOT NULL,
	schedule_id uuid NOT NULL,
	start_time time without time zone DEFAULT '00:00:00'::time without time zone NOT NULL,
	sunday boolean DEFAULT true NOT NULL,
	tgt_rotation_id uuid,
	tgt_user_id uuid,
	thursday boolean DEFAULT true NOT NULL,
	tuesday boolean DEFAULT true NOT NULL,
	wednesday boolean DEFAULT true NOT NULL,
	CONSTRAINT schedule_rules_check CHECK (tgt_user_id IS NULL AND tgt_rotation_id IS NOT NULL OR tgt_user_id IS NOT NULL AND tgt_rotation_id IS NULL),
	CONSTRAINT schedule_rules_pkey PRIMARY KEY (id),
	CONSTRAINT schedule_rules_schedule_id_fkey FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
	CONSTRAINT schedule_rules_tgt_rotation_id_fkey FOREIGN KEY (tgt_rotation_id) REFERENCES rotations(id) ON DELETE CASCADE,
	CONSTRAINT schedule_rules_tgt_user_id_fkey FOREIGN KEY (tgt_user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_rule_schedule ON public.schedule_rules USING btree (schedule_id);
CREATE INDEX idx_target_schedule ON public.schedule_rules USING btree (schedule_id, tgt_rotation_id, tgt_user_id);
CREATE UNIQUE INDEX schedule_rules_pkey ON public.schedule_rules USING btree (id);

CREATE CONSTRAINT TRIGGER trg_enforce_schedule_rule_limit AFTER INSERT ON public.schedule_rules NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_schedule_rule_limit();
CREATE CONSTRAINT TRIGGER trg_enforce_schedule_target_limit AFTER INSERT ON public.schedule_rules NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_schedule_target_limit();


CREATE TABLE schedules (
	description text DEFAULT ''::text NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	last_processed timestamp with time zone,
	name text NOT NULL,
	time_zone text NOT NULL,
	CONSTRAINT schedules_name_key UNIQUE (name),
	CONSTRAINT schedules_pkey PRIMARY KEY (id)
);

CREATE INDEX idx_search_schedules_desc_eng ON public.schedules USING gin (to_tsvector('english'::regconfig, replace(lower(description), '.'::text, ' '::text)));
CREATE INDEX idx_search_schedules_name_eng ON public.schedules USING gin (to_tsvector('english'::regconfig, replace(lower(name), '.'::text, ' '::text)));
CREATE UNIQUE INDEX schedules_name ON public.schedules USING btree (lower(name));
CREATE UNIQUE INDEX schedules_name_key ON public.schedules USING btree (name);
CREATE UNIQUE INDEX schedules_pkey ON public.schedules USING btree (id);


CREATE TABLE services (
	description text DEFAULT ''::text NOT NULL,
	escalation_policy_id uuid NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	maintenance_expires_at timestamp with time zone,
	name text NOT NULL,
	CONSTRAINT services_escalation_policy_id_fkey FOREIGN KEY (escalation_policy_id) REFERENCES escalation_policies(id),
	CONSTRAINT services_name_key UNIQUE (name),
	CONSTRAINT services_pkey PRIMARY KEY (id),
	CONSTRAINT svc_ep_uniq UNIQUE (id, escalation_policy_id)
);

CREATE INDEX idx_search_services_desc_eng ON public.services USING gin (to_tsvector('english'::regconfig, replace(lower(description), '.'::text, ' '::text)));
CREATE INDEX idx_search_services_name_eng ON public.services USING gin (to_tsvector('english'::regconfig, replace(lower(name), '.'::text, ' '::text)));
CREATE UNIQUE INDEX services_name ON public.services USING btree (lower(name));
CREATE UNIQUE INDEX services_name_key ON public.services USING btree (name);
CREATE UNIQUE INDEX services_pkey ON public.services USING btree (id);
CREATE UNIQUE INDEX svc_ep_uniq ON public.services USING btree (id, escalation_policy_id);

CREATE TRIGGER trg_10_clear_ep_state_on_svc_ep_change AFTER UPDATE ON public.services FOR EACH ROW WHEN ((old.escalation_policy_id <> new.escalation_policy_id)) EXECUTE FUNCTION fn_clear_ep_state_on_svc_ep_change();


CREATE TABLE switchover_log (
	data jsonb NOT NULL,
	id bigint NOT NULL,
	timestamp timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT switchover_log_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX switchover_log_pkey ON public.switchover_log USING btree (id);


CREATE TABLE switchover_state (
	current_state enum_switchover_state NOT NULL,
	db_id uuid DEFAULT gen_random_uuid() NOT NULL,
	ok boolean NOT NULL,
	CONSTRAINT switchover_state_ok_check CHECK (ok),
	CONSTRAINT switchover_state_pkey PRIMARY KEY (ok)
);

CREATE UNIQUE INDEX switchover_state_pkey ON public.switchover_state USING btree (ok);


CREATE TABLE twilio_sms_callbacks (
	alert_id bigint,
	callback_id uuid NOT NULL,
	code integer NOT NULL,
	id bigint DEFAULT nextval('twilio_sms_callbacks_id_seq'::regclass) NOT NULL,
	phone_number text NOT NULL,
	sent_at timestamp with time zone DEFAULT now() NOT NULL,
	service_id uuid,
	CONSTRAINT twilio_sms_callbacks_alert_id_fkey FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE CASCADE,
	CONSTRAINT twilio_sms_callbacks_service_id_fkey FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE,
	CONSTRAINT twilio_sms_callbacks_uniq_id UNIQUE (id)
);

CREATE INDEX idx_twilio_sms_alert_id ON public.twilio_sms_callbacks USING btree (alert_id);
CREATE UNIQUE INDEX idx_twilio_sms_codes ON public.twilio_sms_callbacks USING btree (phone_number, code);
CREATE INDEX idx_twilio_sms_service_id ON public.twilio_sms_callbacks USING btree (service_id);
CREATE UNIQUE INDEX twilio_sms_callbacks_uniq_id ON public.twilio_sms_callbacks USING btree (id);


CREATE TABLE twilio_sms_errors (
	error_message text NOT NULL,
	id bigint DEFAULT nextval('twilio_sms_errors_id_seq'::regclass) NOT NULL,
	occurred_at timestamp with time zone DEFAULT now() NOT NULL,
	outgoing boolean NOT NULL,
	phone_number text NOT NULL,
	CONSTRAINT twilio_sms_errors_uniq_id UNIQUE (id)
);

CREATE INDEX twilio_sms_errors_phone_number_outgoing_occurred_at_idx ON public.twilio_sms_errors USING btree (phone_number, outgoing, occurred_at);
CREATE UNIQUE INDEX twilio_sms_errors_uniq_id ON public.twilio_sms_errors USING btree (id);


CREATE TABLE twilio_voice_errors (
	error_message text NOT NULL,
	id bigint DEFAULT nextval('twilio_voice_errors_id_seq'::regclass) NOT NULL,
	occurred_at timestamp with time zone DEFAULT now() NOT NULL,
	outgoing boolean NOT NULL,
	phone_number text NOT NULL,
	CONSTRAINT twilio_voice_errors_uniq_id UNIQUE (id)
);

CREATE INDEX twilio_voice_errors_phone_number_outgoing_occurred_at_idx ON public.twilio_voice_errors USING btree (phone_number, outgoing, occurred_at);
CREATE UNIQUE INDEX twilio_voice_errors_uniq_id ON public.twilio_voice_errors USING btree (id);


CREATE TABLE uik_config (
	config jsonb NOT NULL,
	id uuid NOT NULL,
	primary_token uuid,
	primary_token_hint text,
	secondary_token uuid,
	secondary_token_hint text,
	CONSTRAINT uik_config_id_fkey FOREIGN KEY (id) REFERENCES integration_keys(id) ON DELETE CASCADE,
	CONSTRAINT uik_config_pkey PRIMARY KEY (id),
	CONSTRAINT uik_config_primary_token_key UNIQUE (primary_token),
	CONSTRAINT uik_config_secondary_token_key UNIQUE (secondary_token)
);

CREATE UNIQUE INDEX uik_config_pkey ON public.uik_config USING btree (id);
CREATE UNIQUE INDEX uik_config_primary_token_key ON public.uik_config USING btree (primary_token);
CREATE UNIQUE INDEX uik_config_secondary_token_key ON public.uik_config USING btree (secondary_token);


CREATE TABLE user_calendar_subscriptions (
	config jsonb NOT NULL,
	created_at timestamp with time zone DEFAULT now() NOT NULL,
	disabled boolean DEFAULT false NOT NULL,
	id uuid NOT NULL,
	last_access timestamp with time zone,
	last_update timestamp with time zone DEFAULT now() NOT NULL,
	name text NOT NULL,
	schedule_id uuid NOT NULL,
	user_id uuid NOT NULL,
	CONSTRAINT user_calendar_subscriptions_name_schedule_id_user_id_key UNIQUE (name, schedule_id, user_id),
	CONSTRAINT user_calendar_subscriptions_pkey PRIMARY KEY (id),
	CONSTRAINT user_calendar_subscriptions_schedule_id_fkey FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
	CONSTRAINT user_calendar_subscriptions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX user_calendar_subscriptions_name_schedule_id_user_id_key ON public.user_calendar_subscriptions USING btree (name, schedule_id, user_id);
CREATE UNIQUE INDEX user_calendar_subscriptions_pkey ON public.user_calendar_subscriptions USING btree (id);

CREATE CONSTRAINT TRIGGER trg_enforce_calendar_subscriptions_per_user_limit AFTER INSERT ON public.user_calendar_subscriptions NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_calendar_subscriptions_per_user_limit();


CREATE TABLE user_contact_methods (
	dest jsonb NOT NULL,
	disabled boolean DEFAULT false NOT NULL,
	enable_status_updates boolean DEFAULT false NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	last_test_verify_at timestamp with time zone,
	metadata jsonb,
	name text NOT NULL,
	pending boolean DEFAULT true NOT NULL,
	type enum_user_contact_method_type NOT NULL,
	user_id uuid NOT NULL,
	value text NOT NULL,
	CONSTRAINT user_contact_methods_dest_key UNIQUE (dest),
	CONSTRAINT user_contact_methods_pkey PRIMARY KEY (id),
	CONSTRAINT user_contact_methods_type_value_key UNIQUE (type, value),
	CONSTRAINT user_contact_methods_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_contact_method_users ON public.user_contact_methods USING btree (user_id);
CREATE INDEX idx_valid_contact_methods ON public.user_contact_methods USING btree (id) WHERE (NOT disabled);
CREATE UNIQUE INDEX user_contact_methods_dest_key ON public.user_contact_methods USING btree (dest);
CREATE UNIQUE INDEX user_contact_methods_pkey ON public.user_contact_methods USING btree (id);
CREATE UNIQUE INDEX user_contact_methods_type_value_key ON public.user_contact_methods USING btree (type, value);

CREATE TRIGGER trg_10_cm_set_dest_on_insert BEFORE INSERT ON public.user_contact_methods FOR EACH ROW WHEN ((new.dest IS NULL)) EXECUTE FUNCTION fn_cm_set_dest_on_insert();
CREATE TRIGGER trg_10_cm_set_dest_on_update AFTER UPDATE ON public.user_contact_methods FOR EACH ROW WHEN (((new.dest = old.dest) AND ((new.type <> 'DEST'::enum_user_contact_method_type) AND ((new.value <> old.value) OR (new.type <> old.type))))) EXECUTE FUNCTION fn_cm_set_dest_on_insert();
CREATE TRIGGER trg_10_compat_set_type_val_on_insert BEFORE INSERT ON public.user_contact_methods FOR EACH ROW WHEN ((new.dest IS NOT NULL)) EXECUTE FUNCTION fn_cm_compat_set_type_val_on_insert();
CREATE TRIGGER trg_10_compat_set_type_val_on_update BEFORE UPDATE ON public.user_contact_methods FOR EACH ROW WHEN ((new.dest <> old.dest)) EXECUTE FUNCTION fn_cm_compat_set_type_val_on_insert();
CREATE TRIGGER trg_cm_set_not_pending_on_verify BEFORE UPDATE OF disabled ON public.user_contact_methods FOR EACH ROW WHEN (((NOT new.disabled) AND old.pending)) EXECUTE FUNCTION fn_cm_set_not_pending_on_verify();
CREATE CONSTRAINT TRIGGER trg_enforce_contact_method_limit AFTER INSERT ON public.user_contact_methods NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_contact_method_limit();


CREATE TABLE user_favorites (
	id bigint DEFAULT nextval('user_favorites_id_seq'::regclass) NOT NULL,
	tgt_escalation_policy_id uuid,
	tgt_rotation_id uuid,
	tgt_schedule_id uuid,
	tgt_service_id uuid,
	tgt_user_id uuid,
	user_id uuid NOT NULL,
	CONSTRAINT user_favorites_tgt_escalation_policy_id_fkey FOREIGN KEY (tgt_escalation_policy_id) REFERENCES escalation_policies(id) ON DELETE CASCADE,
	CONSTRAINT user_favorites_tgt_rotation_id_fkey FOREIGN KEY (tgt_rotation_id) REFERENCES rotations(id) ON DELETE CASCADE,
	CONSTRAINT user_favorites_tgt_schedule_id_fkey FOREIGN KEY (tgt_schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
	CONSTRAINT user_favorites_tgt_service_id_fkey FOREIGN KEY (tgt_service_id) REFERENCES services(id) ON DELETE CASCADE,
	CONSTRAINT user_favorites_tgt_user_id_fkey FOREIGN KEY (tgt_user_id) REFERENCES users(id) ON DELETE CASCADE,
	CONSTRAINT user_favorites_uniq_id UNIQUE (id),
	CONSTRAINT user_favorites_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
	CONSTRAINT user_favorites_user_id_tgt_escalation_policy_id_key UNIQUE (user_id, tgt_escalation_policy_id),
	CONSTRAINT user_favorites_user_id_tgt_rotation_id_key UNIQUE (user_id, tgt_rotation_id),
	CONSTRAINT user_favorites_user_id_tgt_schedule_id_key UNIQUE (user_id, tgt_schedule_id),
	CONSTRAINT user_favorites_user_id_tgt_service_id_key UNIQUE (user_id, tgt_service_id),
	CONSTRAINT user_favorites_user_id_tgt_user_id_key UNIQUE (user_id, tgt_user_id)
);

CREATE UNIQUE INDEX user_favorites_uniq_id ON public.user_favorites USING btree (id);
CREATE UNIQUE INDEX user_favorites_user_id_tgt_escalation_policy_id_key ON public.user_favorites USING btree (user_id, tgt_escalation_policy_id);
CREATE UNIQUE INDEX user_favorites_user_id_tgt_rotation_id_key ON public.user_favorites USING btree (user_id, tgt_rotation_id);
CREATE UNIQUE INDEX user_favorites_user_id_tgt_schedule_id_key ON public.user_favorites USING btree (user_id, tgt_schedule_id);
CREATE UNIQUE INDEX user_favorites_user_id_tgt_service_id_key ON public.user_favorites USING btree (user_id, tgt_service_id);
CREATE UNIQUE INDEX user_favorites_user_id_tgt_user_id_key ON public.user_favorites USING btree (user_id, tgt_user_id);


CREATE TABLE user_notification_rules (
	contact_method_id uuid NOT NULL,
	created_at timestamp with time zone DEFAULT now(),
	delay_minutes integer DEFAULT 0 NOT NULL,
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	user_id uuid NOT NULL,
	CONSTRAINT user_notification_rules_contact_method_id_delay_minutes_key UNIQUE (contact_method_id, delay_minutes),
	CONSTRAINT user_notification_rules_contact_method_id_fkey FOREIGN KEY (contact_method_id) REFERENCES user_contact_methods(id) ON DELETE CASCADE,
	CONSTRAINT user_notification_rules_pkey PRIMARY KEY (id),
	CONSTRAINT user_notification_rules_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_notif_rule_creation_time ON public.user_notification_rules USING btree (user_id, created_at);
CREATE INDEX idx_notification_rule_users ON public.user_notification_rules USING btree (user_id);
CREATE UNIQUE INDEX user_notification_rules_contact_method_id_delay_minutes_key ON public.user_notification_rules USING btree (contact_method_id, delay_minutes);
CREATE UNIQUE INDEX user_notification_rules_pkey ON public.user_notification_rules USING btree (id);

CREATE CONSTRAINT TRIGGER trg_enforce_notification_rule_limit AFTER INSERT ON public.user_notification_rules NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_notification_rule_limit();
CREATE TRIGGER trg_notification_rule_same_user BEFORE INSERT OR UPDATE ON public.user_notification_rules FOR EACH ROW EXECUTE FUNCTION fn_notification_rule_same_user();


CREATE TABLE user_overrides (
	add_user_id uuid,
	end_time timestamp with time zone NOT NULL,
	id uuid NOT NULL,
	remove_user_id uuid,
	start_time timestamp with time zone NOT NULL,
	tgt_schedule_id uuid NOT NULL,
	CONSTRAINT user_overrides_add_user_id_fkey FOREIGN KEY (add_user_id) REFERENCES users(id) ON DELETE CASCADE,
	CONSTRAINT user_overrides_check CHECK (end_time > start_time),
	CONSTRAINT user_overrides_check1 CHECK (COALESCE(add_user_id, remove_user_id) IS NOT NULL),
	CONSTRAINT user_overrides_check2 CHECK (add_user_id <> remove_user_id),
	CONSTRAINT user_overrides_pkey PRIMARY KEY (id),
	CONSTRAINT user_overrides_remove_user_id_fkey FOREIGN KEY (remove_user_id) REFERENCES users(id) ON DELETE CASCADE,
	CONSTRAINT user_overrides_tgt_schedule_id_fkey FOREIGN KEY (tgt_schedule_id) REFERENCES schedules(id) ON DELETE CASCADE
);

CREATE INDEX idx_user_overrides_schedule ON public.user_overrides USING btree (tgt_schedule_id, end_time);
CREATE UNIQUE INDEX user_overrides_pkey ON public.user_overrides USING btree (id);

CREATE CONSTRAINT TRIGGER trg_enforce_user_overide_no_conflict AFTER INSERT OR UPDATE ON public.user_overrides NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_user_overide_no_conflict();
CREATE CONSTRAINT TRIGGER trg_enforce_user_override_schedule_limit AFTER INSERT ON public.user_overrides NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION fn_enforce_user_override_schedule_limit();


CREATE TABLE user_slack_data (
	access_token text NOT NULL,
	id uuid NOT NULL,
	CONSTRAINT user_slack_data_id_fkey FOREIGN KEY (id) REFERENCES users(id) ON DELETE CASCADE,
	CONSTRAINT user_slack_data_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX user_slack_data_pkey ON public.user_slack_data USING btree (id);


CREATE TABLE user_verification_codes (
	code integer NOT NULL,
	contact_method_id uuid NOT NULL,
	expires_at timestamp with time zone NOT NULL,
	id uuid NOT NULL,
	sent boolean DEFAULT false NOT NULL,
	CONSTRAINT user_verification_codes_contact_method_id_fkey FOREIGN KEY (contact_method_id) REFERENCES user_contact_methods(id) ON DELETE CASCADE,
	CONSTRAINT user_verification_codes_contact_method_id_key UNIQUE (contact_method_id),
	CONSTRAINT user_verification_codes_pkey PRIMARY KEY (id)
);

CREATE UNIQUE INDEX user_verification_codes_contact_method_id_key ON public.user_verification_codes USING btree (contact_method_id);
CREATE UNIQUE INDEX user_verification_codes_pkey ON public.user_verification_codes USING btree (id);


CREATE TABLE users (
	alert_status_log_contact_method_id uuid,
	avatar_url text DEFAULT ''::text NOT NULL,
	bio text DEFAULT ''::text NOT NULL,
	email text DEFAULT ''::text NOT NULL,
	id uuid NOT NULL,
	name text NOT NULL,
	role enum_user_role DEFAULT 'unknown'::enum_user_role NOT NULL,
	CONSTRAINT goalert_user_pkey PRIMARY KEY (id),
	CONSTRAINT users_alert_status_log_contact_method_id_fkey FOREIGN KEY (alert_status_log_contact_method_id) REFERENCES user_contact_methods(id) ON DELETE SET NULL DEFERRABLE
);

CREATE UNIQUE INDEX goalert_user_pkey ON public.users USING btree (id);
CREATE INDEX idx_search_users_name_eng ON public.users USING gin (to_tsvector('english'::regconfig, replace(lower(name), '.'::text, ' '::text)));
CREATE INDEX idx_user_status_updates ON public.users USING btree (alert_status_log_contact_method_id) WHERE (alert_status_log_contact_method_id IS NOT NULL);

CREATE TRIGGER trg_enforce_status_update_same_user BEFORE INSERT OR UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION fn_enforce_status_update_same_user();


-- Sequences

CREATE SEQUENCE incident_number_seq
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 9223372036854775807
	CACHE 1;

