CREATE TABLE IF NOT EXISTS cases (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_number VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50),
    status VARCHAR(50) NOT NULL,
    opened_at TIMESTAMPTZ DEFAULT NOW(),
    closed_at TIMESTAMPTZ,
    description TEXT,
    owner_id UUID REFERENCES users(id) ON DELETE SET NULL,
    allowed_viewers UUID[] DEFAULT '{}',
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS legal_requests (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type VARCHAR(50) NOT NULL,
    title VARCHAR(255) NOT NULL,
    requester_name VARCHAR(255),
    requester_email VARCHAR(255),
    status VARCHAR(50) NOT NULL,
    opened_at TIMESTAMPTZ DEFAULT NOW(),
    due_date DATE,
    assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
    legal_basis TEXT,
    scope JSONB,
    linked_case_id UUID REFERENCES cases(id) ON DELETE SET NULL,
    sla_breached BOOLEAN DEFAULT FALSE,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS legal_request_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    request_id UUID REFERENCES legal_requests(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id) ON DELETE SET NULL,
    added_at TIMESTAMPTZ DEFAULT NOW(),
    added_by UUID REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS case_custodians (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    department VARCHAR(255),
    role VARCHAR(255),
    added_at TIMESTAMPTZ DEFAULT NOW(),
    notified_at TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS collections (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    scope JSONB,
    status VARCHAR(50),
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    items_collected INT DEFAULT 0,
    dedup_enabled BOOLEAN DEFAULT TRUE,
    threading_enabled BOOLEAN DEFAULT TRUE,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS collection_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    collection_id UUID REFERENCES collections(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id) ON DELETE SET NULL,
    thread_id UUID,
    is_duplicate BOOLEAN DEFAULT FALSE,
    duplicate_of UUID REFERENCES collection_items(id) ON DELETE SET NULL,
    collected_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS holds (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    reason TEXT NOT NULL,
    scope JSONB,
    status VARCHAR(50),
    applied_at TIMESTAMPTZ DEFAULT NOW(),
    released_at TIMESTAMPTZ,
    released_by UUID REFERENCES users(id) ON DELETE SET NULL,
    override_reason TEXT,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS hold_documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hold_id UUID REFERENCES holds(id) ON DELETE CASCADE,
    document_id UUID REFERENCES documents(id) ON DELETE SET NULL,
    added_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS hold_notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hold_id UUID REFERENCES holds(id) ON DELETE CASCADE,
    custodian_id UUID REFERENCES case_custodians(id) ON DELETE CASCADE,
    sent_at TIMESTAMPTZ,
    acknowledged_at TIMESTAMPTZ,
    notification_type VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS review_sets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    reviewers UUID[] DEFAULT '{}',
    status VARCHAR(50),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS review_set_items (
    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 SET NULL,
    review_status VARCHAR(50),
    reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at TIMESTAMPTZ,
    tags TEXT[],
    notes TEXT,
    added_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS legal_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    case_id UUID REFERENCES cases(id) ON DELETE CASCADE,
    report_type VARCHAR(50),
    generated_at TIMESTAMPTZ DEFAULT NOW(),
    generated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    file_path VARCHAR(500),
    file_size BIGINT,
    format VARCHAR(20)
);

CREATE INDEX IF NOT EXISTS idx_legal_requests_status ON legal_requests(status);
CREATE INDEX IF NOT EXISTS idx_legal_requests_due_date ON legal_requests(due_date);
CREATE INDEX IF NOT EXISTS idx_cases_status ON cases(status);
CREATE INDEX IF NOT EXISTS idx_cases_owner ON cases(owner_id);
CREATE INDEX IF NOT EXISTS idx_collections_case ON collections(case_id);
CREATE INDEX IF NOT EXISTS idx_holds_case ON holds(case_id);
CREATE INDEX IF NOT EXISTS idx_review_sets_case ON review_sets(case_id);
