-- Migration: 188_sag_buzzwords
-- Description: Separate buzzwords module for SAG with free-text keywords and case links

BEGIN;

CREATE TABLE IF NOT EXISTS buzzwords (
    id SERIAL PRIMARY KEY,
    word VARCHAR(120) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS sag_buzzwords (
    id SERIAL PRIMARY KEY,
    sag_id INTEGER NOT NULL REFERENCES sag_sager(id) ON DELETE CASCADE,
    buzzword_id INTEGER NOT NULL REFERENCES buzzwords(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_buzzwords_word_active
    ON buzzwords (word)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_buzzwords_word_active
    ON buzzwords (word)
    WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_sag_buzzwords_active
    ON sag_buzzwords (sag_id, buzzword_id)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_sag_buzzwords_sag_id_active
    ON sag_buzzwords (sag_id)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_sag_buzzwords_buzzword_id_active
    ON sag_buzzwords (buzzword_id)
    WHERE deleted_at IS NULL;

COMMIT;