bmc_hub/migrations/068_conversations_module.sql

-- 068_conversations_module.sql

-- Table for storing transcribed conversations (calls, voice notes)
CREATE TABLE IF NOT EXISTS conversations (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
    ticket_id INTEGER REFERENCES tticket_tickets(id) ON DELETE SET NULL,
    user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    email_message_id INTEGER REFERENCES email_messages(id) ON DELETE SET NULL,
    
    title VARCHAR(255) NOT NULL,
    transcript TEXT,               -- The full transcribed text
    summary TEXT,                  -- AI generated summary (optional)
    
    audio_file_path VARCHAR(500) NOT NULL,
    duration_seconds INTEGER DEFAULT 0,
    
    -- Privacy and Deletion
    is_private BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP,          -- Soft delete
    
    source VARCHAR(50) DEFAULT 'email', -- 'email', 'upload', 'phone_system'
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Index for linkage
CREATE INDEX idx_conversations_customer ON conversations(customer_id);
CREATE INDEX idx_conversations_ticket ON conversations(ticket_id);
CREATE INDEX idx_conversations_user ON conversations(user_id);

-- Full Text Search Index for Danish
ALTER TABLE conversations ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
    to_tsvector('danish', coalesce(title, '') || ' ' || coalesce(transcript, ''))
) STORED;

CREATE INDEX idx_conversations_search ON conversations USING GIN(search_vector);