-- ClientFlow v4.5 Operational Core
-- Communications, contextual tasks and unified timeline.
-- Safe to run more than once.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS communications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_system TEXT NOT NULL DEFAULT 'email',
    source_message_id TEXT,
    thread_id TEXT,
    conversation_id TEXT,
    contact_id TEXT,
    direction TEXT NOT NULL DEFAULT 'inbound',
    sender_name TEXT,
    sender_email TEXT,
    recipient TEXT,
    subject TEXT,
    body TEXT,
    classification TEXT,
    confidence NUMERIC(4,3),
    status TEXT NOT NULL DEFAULT 'new',
    customer_id UUID,
    opportunity_id UUID,
    task_id UUID,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS ux_communications_source_message
ON communications(source_system, source_message_id)
WHERE source_message_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_communications_created ON communications(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_communications_status ON communications(status);
CREATE INDEX IF NOT EXISTS idx_communications_classification ON communications(classification);
CREATE INDEX IF NOT EXISTS idx_communications_sender_email ON communications(sender_email);
CREATE INDEX IF NOT EXISTS idx_communications_customer ON communications(customer_id);
CREATE INDEX IF NOT EXISTS idx_communications_opportunity ON communications(opportunity_id);

ALTER TABLE tasks ADD COLUMN IF NOT EXISTS communication_id UUID;
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS document_id UUID;
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS shipment_id UUID;
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS outbox_id UUID;
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS priority TEXT NOT NULL DEFAULT 'normal';
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS assigned_to TEXT;

CREATE INDEX IF NOT EXISTS idx_tasks_communication ON tasks(communication_id);
CREATE INDEX IF NOT EXISTS idx_tasks_document ON tasks(document_id);
CREATE INDEX IF NOT EXISTS idx_tasks_shipment ON tasks(shipment_id);
CREATE INDEX IF NOT EXISTS idx_tasks_outbox ON tasks(outbox_id);
CREATE INDEX IF NOT EXISTS idx_tasks_priority ON tasks(priority);
CREATE INDEX IF NOT EXISTS idx_tasks_due_status ON tasks(status, due_at);

CREATE TABLE IF NOT EXISTS timeline_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    opportunity_id UUID,
    customer_id UUID,
    event_type TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    source TEXT NOT NULL DEFAULT 'clientflow',
    related_type TEXT,
    related_id TEXT,
    payload JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_by TEXT NOT NULL DEFAULT 'system',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_timeline_opportunity ON timeline_events(opportunity_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_timeline_customer ON timeline_events(customer_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_timeline_related ON timeline_events(related_type, related_id);