-- Phase A: paliad.akten — the central Akte (matter) entity.
--
-- OBSOLETED by migration 018 (data model v2): paliad.akten is dropped and
-- replaced by paliad.projects. The effective shape lives in 018; this file
-- is kept only so a fresh database can replay the migration history.
--
-- Office-scoped visibility columns (per design §2):
--   owning_office       — the office the Akte belongs to
--   collaborators       — uuid[] of users with explicit access (cross-office)
--   firm_wide_visible   — partner-toggled override for firm-wide matters
--
-- Visibility enforcement lives in paliad.can_see_akte() (migration 006)
-- and is applied through the RLS policies (migration 007).

CREATE TABLE paliad.akten (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    aktenzeichen         text NOT NULL,
    title                text NOT NULL,
    akte_type            text,
    court                text,
    court_ref            text,
    status               text NOT NULL DEFAULT 'active'
                         CHECK (status IN ('active', 'pending', 'closed', 'archived')),
    ai_summary           text,

    -- Visibility model
    owning_office        text NOT NULL CHECK (owning_office IN (
                            'munich', 'duesseldorf', 'hamburg',
                            'amsterdam', 'london', 'paris', 'milan'
                         )),
    collaborators        uuid[] NOT NULL DEFAULT '{}',
    firm_wide_visible    boolean NOT NULL DEFAULT false,

    created_by           uuid REFERENCES auth.users(id) ON DELETE SET NULL,
    metadata             jsonb NOT NULL DEFAULT '{}',
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX akten_status_owning_office_idx ON paliad.akten (status, owning_office);
CREATE INDEX akten_owning_office_idx        ON paliad.akten (owning_office);
CREATE INDEX akten_firm_wide_idx            ON paliad.akten (firm_wide_visible) WHERE firm_wide_visible = true;
-- GIN index on collaborators lets the visibility predicate use `ANY(collaborators)` efficiently.
CREATE INDEX akten_collaborators_gin_idx    ON paliad.akten USING GIN (collaborators);
