CREATE TABLE IF NOT EXISTS entities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    entity_type VARCHAR(50) NOT NULL,
    text_hash VARCHAR(64) NOT NULL,
    confidence FLOAT,
    start_offset INT,
    end_offset INT,
    source VARCHAR(50),
    extracted_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS relations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    entity_1_id UUID REFERENCES entities(id) ON DELETE CASCADE,
    entity_2_id UUID REFERENCES entities(id) ON DELETE CASCADE,
    relation_type VARCHAR(50),
    confidence FLOAT,
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    evidence_text_hash VARCHAR(64)
);

CREATE TABLE IF NOT EXISTS communications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    thread_id UUID,
    sender_hash VARCHAR(64),
    recipients_hash TEXT[],
    cc_hash TEXT[],
    subject_hash VARCHAR(64),
    sent_at TIMESTAMPTZ,
    message_type VARCHAR(50),
    in_reply_to UUID REFERENCES communications(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS timeline_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    event_type VARCHAR(50),
    event_timestamp TIMESTAMPTZ NOT NULL,
    actors TEXT[],
    description TEXT,
    extracted_from VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS review_notes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    review_set_id UUID REFERENCES review_sets(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id) ON DELETE CASCADE,
    note_type VARCHAR(50),
    content TEXT NOT NULL,
    author_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS review_activity (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    review_set_id UUID REFERENCES review_sets(id) ON DELETE CASCADE,
    action_type VARCHAR(50),
    actor_id UUID REFERENCES users(id) ON DELETE SET NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    details JSONB
);

CREATE TABLE IF NOT EXISTS exports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    review_set_id UUID REFERENCES review_sets(id) ON DELETE CASCADE,
    export_type VARCHAR(50),
    status VARCHAR(50),
    format VARCHAR(50),
    include_annotations BOOLEAN DEFAULT TRUE,
    include_metadata BOOLEAN DEFAULT TRUE,
    redact_pii BOOLEAN DEFAULT FALSE,
    watermark TEXT,
    file_path VARCHAR(500),
    file_size BIGINT,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_entities_doc_type ON entities(document_id, entity_type);
CREATE INDEX IF NOT EXISTS idx_relations_entities ON relations(entity_1_id, entity_2_id);
CREATE INDEX IF NOT EXISTS idx_comms_thread ON communications(thread_id, sent_at);
CREATE INDEX IF NOT EXISTS idx_timeline_case_ts ON timeline_events(case_id, event_timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_review_notes_doc ON review_notes(document_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_review_activity_set ON review_activity(review_set_id, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_exports_set_status ON exports(review_set_id, status);

CREATE MATERIALIZED VIEW IF NOT EXISTS mv_entity_graph AS
SELECT
    e1.entity_type as entity_1_type,
    e1.text_hash as entity_1_hash,
    r.relation_type,
    e2.entity_type as entity_2_type,
    e2.text_hash as entity_2_hash,
    COUNT(DISTINCT r.document_id) as frequency,
    ARRAY_AGG(DISTINCT r.document_id) as document_ids
FROM relations r
JOIN entities e1 ON r.entity_1_id = e1.id
JOIN entities e2 ON r.entity_2_id = e2.id
GROUP BY e1.entity_type, e1.text_hash, r.relation_type, e2.entity_type, e2.text_hash;

CREATE INDEX IF NOT EXISTS idx_mv_entity_graph ON mv_entity_graph(entity_1_hash, entity_2_hash);

