-- PROJ: custom-fields
-- Doku-only (siehe README.md in diesem Verzeichnis) — die tatsächliche
-- Ausführung passiert idempotent über internal/storage/custom_fields.go
-- initCustomFieldsSchema(), aufgerufen aus (*Store).initSchema().
--
-- Benutzerdefinierte Felder (Custom Fields): tenant-skopierte Feld-
-- Definitionen, Zuordnung pro Dokumenttyp (required/visible/sort_order) und
-- die eigentlichen Werte pro Dokument. Regeln: enum-Wert liegt in value_text,
-- monetary in value_number (NUMERIC(14,2) semantisch).

CREATE TABLE IF NOT EXISTS custom_field_defs (
    id            BIGSERIAL PRIMARY KEY,
    tenant_id     BIGINT NOT NULL,
    name          TEXT NOT NULL,
    label         TEXT NOT NULL,
    field_type    TEXT NOT NULL CHECK (field_type IN ('text','number','date','boolean','enum','monetary')),
    enum_options  JSONB,
    currency      TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(tenant_id, name)
);

CREATE TABLE IF NOT EXISTS document_type_fields (
    doc_type_id   BIGINT NOT NULL REFERENCES document_types(id) ON DELETE CASCADE,
    field_id      BIGINT NOT NULL REFERENCES custom_field_defs(id) ON DELETE CASCADE,
    required      BOOLEAN NOT NULL DEFAULT false,
    visible       BOOLEAN NOT NULL DEFAULT true,
    sort_order    INT NOT NULL DEFAULT 0,
    PRIMARY KEY (doc_type_id, field_id)
);

CREATE TABLE IF NOT EXISTS document_field_values (
    document_id   BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    field_id      BIGINT NOT NULL REFERENCES custom_field_defs(id) ON DELETE CASCADE,
    tenant_id     BIGINT NOT NULL,
    value_text    TEXT,
    value_number  NUMERIC,
    value_date    DATE,
    value_bool    BOOLEAN,
    PRIMARY KEY (document_id, field_id)
);
CREATE INDEX IF NOT EXISTS idx_dfv_tenant_field ON document_field_values(tenant_id, field_id);
CREATE INDEX IF NOT EXISTS idx_dfv_field_text ON document_field_values(field_id, value_text);
CREATE INDEX IF NOT EXISTS idx_dfv_field_number ON document_field_values(field_id, value_number);