47 lines
1.9 KiB
PL/PgSQL
47 lines
1.9 KiB
PL/PgSQL
BEGIN;
|
|
|
|
-- Ensure sev-4 exists in the severity table
|
|
INSERT INTO severity (id, name, description, version, sla, slack_user_ids, created_at, priority)
|
|
VALUES (5, 'Sev-4', 'Customer Issue', 0, 15, '{}', now(), 500);
|
|
|
|
-- Add entries for each team in the team_severity table for sev-4
|
|
INSERT INTO team_severity (team_id, severity_id, sla)
|
|
SELECT t.id, s.id, 15
|
|
FROM team t
|
|
JOIN severity s ON s.id = 5;
|
|
|
|
-- Remove the manager's entry from any other severity lists
|
|
DELETE
|
|
FROM team_user_severity
|
|
USING team_user, team, houston_user
|
|
WHERE team_user_severity.team_user = team_user.id
|
|
AND team_user.team_id = team.id
|
|
AND team_user.user_id = houston_user.id
|
|
AND team.manager_handle = houston_user.slack_user_id
|
|
AND team_user_severity.team_severity IN (SELECT ts.id
|
|
FROM team_severity ts
|
|
JOIN severity s ON ts.severity_id = s.id
|
|
WHERE s.id = 5);
|
|
|
|
-- Add entries in the team_user_severity table for the manager of each team
|
|
WITH manager_entries AS (SELECT t.id AS team_id,
|
|
hu.id AS user_id,
|
|
ts.id AS team_severity_id
|
|
FROM team t
|
|
JOIN
|
|
houston_user hu ON t.manager_handle = hu.slack_user_id
|
|
JOIN
|
|
team_severity ts ON ts.team_id = t.id
|
|
JOIN
|
|
severity s ON ts.severity_id = s.id
|
|
WHERE s.id = 5)
|
|
INSERT
|
|
INTO team_user_severity (team_user, team_severity)
|
|
SELECT tu.id,
|
|
me.team_severity_id
|
|
FROM manager_entries me
|
|
JOIN
|
|
team_user tu ON tu.team_id = me.team_id AND tu.user_id = me.user_id
|
|
ON CONFLICT (team_user, team_severity) DO NOTHING;
|
|
|
|
COMMIT; |