-- +goose Up

-- CONTINUOUS VETTING, AND THE BREAK IN ACCESS CLOCK THAT DECIDES WHO IS ACTUALLY PLACEABLE.
--
-- THE MODEL WAS TEN YEARS OUT OF DATE. This product stores clearance_expires_at and reinvestigation_due_at,
-- which describe the world before 1 October 2021: a Top Secret reinvestigated every five years, a Secret every
-- ten. That world is gone. DCSA enrolled DoD clearance holders in CONTINUOUS VETTING under Trusted Workforce
-- 2.0, which REPLACED periodic reinvestigation with ongoing monitoring of financial, criminal and public
-- records. A person in continuous vetting has no reinvestigation date to count down to.
--
-- WHAT ACTUALLY DECIDES PLACEABILITY IS THE BREAK IN ACCESS, and this product could not answer it at all.
-- Separation from a sponsor pulls a person out of that sponsor's continuous vetting enrollment, and the clock
-- that starts at separation is the one that matters:
--
--   under 24 months, and a favourable adjudication closed within the previous 5 years
--        a new employer can usually REINSTATE without a full reinvestigation
--   over 24 months, or an adjudication older than 5 years
--        a new investigation is likely, which is months of lead time and a different staffing proposition
--
-- A TS/SCI candidate three months out of access and one twenty five months out are not the same asset, and
-- missionMATCH showed them identically. For a product whose entire question is "who can support this mission
-- today", that is the single most important fact it was not storing.
--
-- IT CONNECTS TO THE REDEPLOYMENT WORK DIRECTLY. A contractor rolling off is the event that STARTS this
-- clock. The alert that says somebody finishes in thirty days and the clock that says they become hard to
-- place twenty four months later are the same story told at two points in time.
--
-- NOTHING IS INFERRED AND NOTHING IS BACKFILLED. Every column arrives NULL. A candidate whose access history
-- nobody has entered reports "not known" rather than a guess, because a guessed reinstatement posture is a
-- staffing decision made on fiction. The dates come from a person who knows them, or from nowhere.

-- ONE STATEMENT PER CHANGE. sqlc parses these migration files as the schema, and it refuses an ALTER TABLE
-- that mixes ADD COLUMN with a table level CONSTRAINT in the same statement. Splitting them costs nothing and
-- keeps the generated models buildable, which matters because a stale dbgen.Candidate is exactly how the
-- merged_into_id column broke candidate search earlier in this build.

-- Currently inside somebody's continuous vetting enrollment, which in practice means currently sponsored and
-- in access. NULL means nobody has said, which is different from false.
ALTER TABLE candidates ADD COLUMN cv_enrolled boolean;
-- When continuous vetting enrollment began. Useful evidence, never used to compute the window.
ALTER TABLE candidates ADD COLUMN cv_enrolled_on date;
-- THE DATE ACCESS ENDED, which is what the 24 month window is measured from. NULL while somebody is in
-- access, and NULL also when nobody has told us, which is why cv_enrolled is a separate column: the two
-- absences mean different things and one boolean cannot carry both.
ALTER TABLE candidates ADD COLUMN access_ended_on date;
-- The date the favourable adjudication closed. The simple reinstatement path additionally requires this to be
-- within five years, so an in window break with a stale adjudication is still an investigation.
ALTER TABLE candidates ADD COLUMN last_adjudicated_on date;

ALTER TABLE candidates ADD CONSTRAINT ck_candidates_access_dates CHECK (
    (last_adjudicated_on IS NULL OR last_adjudicated_on <= CURRENT_DATE + 1)
    AND (cv_enrolled_on IS NULL OR cv_enrolled_on <= CURRENT_DATE + 1)
);

-- A person cannot be in continuous vetting enrollment and simultaneously carry a date on which their access
-- ended. Enrollment follows sponsorship, and separation is what ends both.
ALTER TABLE candidates ADD CONSTRAINT ck_candidates_cv_not_separated CHECK (
    NOT (cv_enrolled IS TRUE AND access_ended_on IS NOT NULL)
);

-- The index the search filter and the alert arm need: find everybody whose break in access is approaching or
-- past the 24 month line. Partial, because a candidate still in access has no clock running and is the
-- majority of any healthy bench.
CREATE INDEX IF NOT EXISTS idx_candidates_access_ended
    ON candidates (organization_id, access_ended_on)
    WHERE access_ended_on IS NOT NULL AND deleted_at IS NULL;

-- +goose Down
DROP INDEX IF EXISTS idx_candidates_access_ended;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_cv_not_separated;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_access_dates;
ALTER TABLE candidates DROP COLUMN IF EXISTS last_adjudicated_on;
ALTER TABLE candidates DROP COLUMN IF EXISTS access_ended_on;
ALTER TABLE candidates DROP COLUMN IF EXISTS cv_enrolled_on;
ALTER TABLE candidates DROP COLUMN IF EXISTS cv_enrolled;