-- 019_dashboard_config.sql
-- Dashboard date/status configuration (docs/2026-07-28-dashboard-redesign.md, Phase 2)
--
-- The dashboard must not hard-code which date means "ECD" or which status means
-- "complete": those are client-defined. A build team may keep a Build Complete
-- date and a Release to Sales date and reasonably want either driving the page.
-- So the client selects, per module, from the fields and statuses they already
-- have. Nothing here changes a field definition — pointing at a field is a
-- dashboard decision and lives in the dashboard's own configuration, so
-- admin_save.php's rule that static fields belong to migrations stays absolute.
--
-- Four settings per module, in two tables because the last one is a set:
--   ecd             (field) when is it due            -> flags
--   completion      (field) when did it finish        -> completed trend
--   complete_status (status) did it finish OK         -> qualifies the trend
--   inactive set    (statuses) is work still happening -> suppresses flags
--
-- Completions and cancellations are deliberately separate: a cancelled record
-- stops being chased without ever counting as a completion.
--
-- Scope is projects/accounts/stocklists. Wayleave is excluded for now because
-- wayleave.agreement_status uses id/description rather than the
-- <module>_status_id/<module>_status_desc shape the other three share; it joins
-- once the "Align wayleave to the other modules" backlog item lands, with no
-- change needed here. Opportunities has no field-meta tables at all, so it has
-- nothing to configure.
--
-- No foreign keys: field_id and status_id point into per-module tables (four
-- possible parents each), and the *_status tables have no primary key to
-- reference. Validation is done on save — the field must exist in that module,
-- be a date type, and be active. Rows are cleaned up by the same save path.

CREATE TABLE IF NOT EXISTS public.dashboard_config (
    module        varchar(50)  NOT NULL,
    role          varchar(30)  NOT NULL,
    field_id      bigint,
    status_id     integer,
    updated_at    timestamptz  NOT NULL DEFAULT now(),
    updated_user  integer,

    CONSTRAINT dashboard_config_pk
        PRIMARY KEY (module, role),

    CONSTRAINT dashboard_config_role_ck
        CHECK (role IN ('ecd', 'completion', 'complete_status')),

    -- Exactly one pointer, and the right one for the role. Without this a
    -- malformed row means nothing and fails silently at read time.
    CONSTRAINT dashboard_config_target_ck
        CHECK (
            (role IN ('ecd', 'completion') AND field_id IS NOT NULL AND status_id IS NULL)
         OR (role = 'complete_status'      AND status_id IS NOT NULL AND field_id IS NULL)
        )
);

COMMENT ON TABLE public.dashboard_config IS
    'Per-module dashboard settings: which date field is the ECD, which is the completion date, and which status means complete. See docs/2026-07-28-dashboard-redesign.md Phase 2.';

CREATE TABLE IF NOT EXISTS public.dashboard_inactive_statuses (
    module        varchar(50) NOT NULL,
    status_id     integer     NOT NULL,
    updated_at    timestamptz NOT NULL DEFAULT now(),
    updated_user  integer,

    CONSTRAINT dashboard_inactive_statuses_pk
        PRIMARY KEY (module, status_id)
);

COMMENT ON TABLE public.dashboard_inactive_statuses IS
    'Statuses meaning "work is no longer happening" — suppresses dashboard flags so cancelled or paused records stop being chased. The configured complete status is always treated as inactive whether or not it is listed here.';
