53 lines
1.8 KiB
MySQL
53 lines
1.8 KiB
MySQL
|
-- ============================================================
|
||
|
|
-- Migration 015: Presenca do agente e reserva de chamados em pausa
|
||
|
|
-- Tabelas:
|
||
|
|
-- agent_presence
|
||
|
|
-- whatsapp_chat_atribuicoes
|
||
|
|
-- ============================================================
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS agent_presence (
|
||
|
|
user_id INTEGER PRIMARY KEY REFERENCES usuarios(id) ON DELETE CASCADE,
|
||
|
|
status VARCHAR(40) NOT NULL DEFAULT 'offline',
|
||
|
|
paused_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
last_seen_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM pg_constraint
|
||
|
|
WHERE conname = 'chk_agent_presence_status'
|
||
|
|
) THEN
|
||
|
|
ALTER TABLE agent_presence
|
||
|
|
ADD CONSTRAINT chk_agent_presence_status
|
||
|
|
CHECK (status IN ('available', 'paused', 'offline'));
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
INSERT INTO agent_presence (user_id, status, last_seen_at, updated_at)
|
||
|
|
SELECT id, 'available', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
|
||
|
|
FROM usuarios
|
||
|
|
ON CONFLICT (user_id) DO NOTHING;
|
||
|
|
|
||
|
|
ALTER TABLE whatsapp_chat_atribuicoes
|
||
|
|
ADD COLUMN IF NOT EXISTS reserved_user_id INTEGER REFERENCES usuarios(id) ON DELETE SET NULL,
|
||
|
|
ADD COLUMN IF NOT EXISTS reserved_at TIMESTAMP WITH TIME ZONE,
|
||
|
|
ADD COLUMN IF NOT EXISTS pause_released_at TIMESTAMP WITH TIME ZONE;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_agent_presence_status
|
||
|
|
ON agent_presence (status);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_agent_presence_paused_at
|
||
|
|
ON agent_presence (paused_at)
|
||
|
|
WHERE status = 'paused';
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_whatsapp_atribuicoes_reserved_user
|
||
|
|
ON whatsapp_chat_atribuicoes (reserved_user_id, status)
|
||
|
|
WHERE reserved_user_id IS NOT NULL;
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_whatsapp_atribuicoes_reserved_queue
|
||
|
|
ON whatsapp_chat_atribuicoes (area_id, status, reserved_user_id)
|
||
|
|
WHERE status = 'queued';
|