-- Phase A: child tables of paliad.akten.
-- All inherit visibility from their parent Akte via RLS policies in migration 007.
--
-- OBSOLETED by migration 018 (data model v2): these tables are renamed/rewired
-- to hang off paliad.projects (parteien→parties, etc.) and later renamed to
-- English in migration 020. The effective shape lives in 018+020; this file
-- is kept only so a fresh database can replay the migration history.

-- ============================================================================
-- parteien (parties to an Akte)
-- ============================================================================
CREATE TABLE paliad.parteien (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    akte_id         uuid NOT NULL REFERENCES paliad.akten(id) ON DELETE CASCADE,
    name            text NOT NULL,
    role            text,
    representative  text,
    contact_info    jsonb NOT NULL DEFAULT '{}',
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX parteien_akte_idx ON paliad.parteien (akte_id);

-- ============================================================================
-- fristen (deadlines)
-- ============================================================================
CREATE TABLE paliad.fristen (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    akte_id              uuid NOT NULL REFERENCES paliad.akten(id) ON DELETE CASCADE,
    title                text NOT NULL,
    description          text,
    due_date             date NOT NULL,
    original_due_date    date,
    warning_date         date,
    source               text NOT NULL DEFAULT 'manual',
    rule_id              uuid REFERENCES paliad.deadline_rules(id) ON DELETE SET NULL,
    status               text NOT NULL DEFAULT 'pending'
                         CHECK (status IN ('pending', 'completed', 'cancelled', 'waived')),
    completed_at         timestamptz,
    caldav_uid           text,
    caldav_etag          text,
    notes                text,
    created_by           uuid REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX fristen_akte_idx             ON paliad.fristen (akte_id);
CREATE INDEX fristen_status_due_date_idx  ON paliad.fristen (status, due_date);
CREATE INDEX fristen_due_date_idx         ON paliad.fristen (due_date);

-- ============================================================================
-- termine (appointments)
-- ============================================================================
CREATE TABLE paliad.termine (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    akte_id              uuid REFERENCES paliad.akten(id) ON DELETE CASCADE,
    title                text NOT NULL,
    description          text,
    start_at             timestamptz NOT NULL,
    end_at               timestamptz,
    location             text,
    termin_type          text CHECK (termin_type IS NULL OR termin_type IN (
                            'hearing', 'meeting', 'consultation', 'deadline_hearing'
                         )),
    caldav_uid           text,
    caldav_etag          text,
    created_by           uuid REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX termine_akte_idx      ON paliad.termine (akte_id);
CREATE INDEX termine_start_at_idx  ON paliad.termine (start_at);

-- ============================================================================
-- dokumente (documents)
-- ============================================================================
CREATE TABLE paliad.dokumente (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    akte_id         uuid NOT NULL REFERENCES paliad.akten(id) ON DELETE CASCADE,
    title           text NOT NULL,
    doc_type        text,
    file_path       text,
    file_size       bigint,
    mime_type       text,
    ai_extracted    jsonb,
    uploaded_by     uuid REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX dokumente_akte_idx ON paliad.dokumente (akte_id);

-- ============================================================================
-- akten_events (audit trail)
-- ============================================================================
CREATE TABLE paliad.akten_events (
    id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    akte_id         uuid NOT NULL REFERENCES paliad.akten(id) ON DELETE CASCADE,
    event_type      text,
    title           text NOT NULL,
    description     text,
    event_date      timestamptz,
    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_events_akte_created_idx ON paliad.akten_events (akte_id, created_at DESC);

-- ============================================================================
-- notizen (polymorphic notes — exactly one parent per row)
-- ============================================================================
CREATE TABLE paliad.notizen (
    id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    akte_id           uuid REFERENCES paliad.akten(id) ON DELETE CASCADE,
    frist_id          uuid REFERENCES paliad.fristen(id) ON DELETE CASCADE,
    termin_id         uuid REFERENCES paliad.termine(id) ON DELETE CASCADE,
    akten_event_id    uuid REFERENCES paliad.akten_events(id) ON DELETE CASCADE,
    content           text NOT NULL,
    created_by        uuid REFERENCES auth.users(id) ON DELETE SET NULL,
    created_at        timestamptz NOT NULL DEFAULT now(),
    updated_at        timestamptz NOT NULL DEFAULT now(),

    -- Exactly one parent FK must be set.
    CONSTRAINT notizen_exactly_one_parent CHECK (
        (CASE WHEN akte_id        IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN frist_id       IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN termin_id      IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN akten_event_id IS NOT NULL THEN 1 ELSE 0 END) = 1
    )
);
-- Partial indexes per parent type so `WHERE akte_id = $1` etc. is fast.
CREATE INDEX notizen_akte_idx        ON paliad.notizen (akte_id)        WHERE akte_id        IS NOT NULL;
CREATE INDEX notizen_frist_idx       ON paliad.notizen (frist_id)       WHERE frist_id       IS NOT NULL;
CREATE INDEX notizen_termin_idx      ON paliad.notizen (termin_id)      WHERE termin_id      IS NOT NULL;
CREATE INDEX notizen_akten_event_idx ON paliad.notizen (akten_event_id) WHERE akten_event_id IS NOT NULL;
