-- +goose Up

-- GOALS, WHICH TURN EVERY NUMBER PHASE 1 REPORTS INTO ATTAINMENT.
--
-- Phase 1 gave a manager four figures and the window before them. That answers "is this better or worse
-- than last month" and cannot answer "is this enough", which is the question the business actually asks.
-- Four placements is a good month or a bad one depending entirely on what was committed to, and the
-- product had nowhere to record what was committed to.
--
-- SCOPE MATCHES THE MANAGER VIEW EXACTLY, organization, team or user, because a goal that cannot be set
-- at the level the figures are read at is a goal nobody can check. The same resolver serves both, so a
-- target set on a desk is compared against that desk's number and never against a different population.
--
-- A PERIOD IS A START AND A LENGTH, not a month name. The manager view already compares a window against
-- the window before it and lets the length be chosen, so a goal has to speak the same way or the two
-- screens disagree about what a period is. Thirty days starting the first is a month; ninety is a
-- quarter; nothing here needs a calendar library to say so.
--
-- TARGETS ARE EXACT DECIMAL. Everything countable in this product is, and a target compared against a
-- decimal actual using a float would produce attainment that drifts at the boundary, which is the one
-- place anybody looks closely.

CREATE TABLE recruiting_goals (
    organization_id uuid NOT NULL REFERENCES organizations(id),
    id              uuid NOT NULL DEFAULT gen_random_uuid(),

    -- WHOSE GOAL. subject_id is the team or user; NULL means the whole organization, which is why it
    -- cannot simply be NOT NULL and why the CHECK below ties the two columns together.
    scope           text NOT NULL
                      CHECK (scope IN ('organization', 'team', 'user')),
    subject_id      uuid,

    -- WHAT IS BEING COMMITTED TO. Deliberately the same four figures the manager view reports, so every
    -- goal has a number already on screen to be judged against. A metric with no matching figure would be
    -- a target nobody could ever see progress on.
    metric          text NOT NULL
                      CHECK (metric IN ('placements', 'submissions', 'fill_rate', 'time_to_fill_days')),

    period_start    date NOT NULL,
    period_days     integer NOT NULL DEFAULT 30
                      CHECK (period_days BETWEEN 1 AND 366),

    target          numeric(12,2) NOT NULL
                      CHECK (target >= 0),

    note            text NOT NULL DEFAULT '',
    created_by      uuid,
    created_at      timestamptz NOT NULL DEFAULT now(),
    updated_at      timestamptz NOT NULL DEFAULT now(),

    PRIMARY KEY (organization_id, id),

    -- An organization goal names nobody and a team or user goal must name somebody. Without this a row
    -- claiming to be a team target with no team would be silently compared against the whole company.
    CONSTRAINT ck_goal_subject_matches_scope CHECK (
        (scope = 'organization' AND subject_id IS NULL)
        OR (scope IN ('team', 'user') AND subject_id IS NOT NULL)
    )
);

-- ONE TARGET PER SUBJECT PER METRIC PER PERIOD. Two rows for the same thing is not a richer goal, it is a
-- question about which number is real, and a manager should never have to answer that. coalesce lets the
-- organization row take part in the same unique index despite its NULL subject.
CREATE UNIQUE INDEX IF NOT EXISTS uq_recruiting_goal_subject_period
    ON recruiting_goals (organization_id, scope, coalesce(subject_id, '00000000-0000-0000-0000-000000000000'::uuid), metric, period_start);

-- The read the manager view makes: this organization's goals covering today.
CREATE INDEX IF NOT EXISTS idx_recruiting_goals_period
    ON recruiting_goals (organization_id, period_start DESC);

ALTER TABLE recruiting_goals ENABLE ROW LEVEL SECURITY;
ALTER TABLE recruiting_goals FORCE ROW LEVEL SECURITY;

CREATE POLICY recruiting_goals_tenant ON recruiting_goals
    USING (organization_id = current_setting('app.current_org_id', true)::uuid)
    WITH CHECK (organization_id = current_setting('app.current_org_id', true)::uuid);

-- Creating a table grants nothing, and forgetting this is the defect 0102 and 0103 each shipped once.
GRANT SELECT, INSERT, UPDATE, DELETE ON recruiting_goals TO missionmatch_app;

SELECT mm_mirror_staging_role_privileges();

-- +goose Down
DROP POLICY IF EXISTS recruiting_goals_tenant ON recruiting_goals;
DROP INDEX IF EXISTS idx_recruiting_goals_period;
DROP INDEX IF EXISTS uq_recruiting_goal_subject_period;
DROP TABLE IF EXISTS recruiting_goals;