-- Create contract_chat_conversations table for storing conversation threads per contract
CREATE TABLE IF NOT EXISTS contract_chat_conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
    owner_id UUID NOT NULL,
    name VARCHAR(255),
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_contract_chat_conversations_contract ON contract_chat_conversations(contract_id);
CREATE INDEX idx_contract_chat_conversations_owner ON contract_chat_conversations(owner_id);
CREATE INDEX idx_contract_chat_conversations_active ON contract_chat_conversations(contract_id, is_active);

CREATE TRIGGER update_contract_chat_conversations_updated_at
    BEFORE UPDATE ON contract_chat_conversations
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Create contract_chat_messages table for individual messages
CREATE TABLE IF NOT EXISTS contract_chat_messages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    conversation_id UUID NOT NULL REFERENCES contract_chat_conversations(id) ON DELETE CASCADE,
    role VARCHAR(16) NOT NULL CHECK (role IN ('user', 'assistant', 'error')),
    content TEXT NOT NULL,
    tool_calls JSONB,
    pending_questions JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_contract_chat_messages_conversation ON contract_chat_messages(conversation_id);
CREATE INDEX idx_contract_chat_messages_created ON contract_chat_messages(created_at);