ALTER TYPE app.applicant_status ADD VALUE IF NOT EXISTS 'Failed';

ALTER TABLE app.candidate
    ADD COLUMN IF NOT EXISTS status_changed_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    ADD COLUMN IF NOT EXISTS lifecycle_notifications jsonb DEFAULT '{}'::jsonb NOT NULL,
    ADD COLUMN IF NOT EXISTS analysis_last_run_at timestamp with time zone,
    ADD COLUMN IF NOT EXISTS analysis_input_fingerprint text,
    ADD COLUMN IF NOT EXISTS invalidated_at timestamp with time zone,
    ADD COLUMN IF NOT EXISTS reupload_requested_at timestamp with time zone,
    ADD COLUMN IF NOT EXISTS reuploaded_at timestamp with time zone,
    ADD COLUMN IF NOT EXISTS failed_purged_at timestamp with time zone;

UPDATE app.candidate
SET status_changed_at = CASE
    WHEN status = 'Initial'::app.applicant_status THEN COALESCE(created_at, last_updated_at, CURRENT_TIMESTAMP)
    WHEN status = 'Pending'::app.applicant_status THEN COALESCE(submitted_at, last_updated_at, created_at, CURRENT_TIMESTAMP)
    ELSE COALESCE(last_updated_at, submitted_at, created_at, CURRENT_TIMESTAMP)
END;

CREATE INDEX IF NOT EXISTS idx_candidate_status_changed_at ON app.candidate (status, status_changed_at);
CREATE INDEX IF NOT EXISTS idx_candidate_reupload_flags ON app.candidate (invalidated_at, reuploaded_at);

CREATE OR REPLACE FUNCTION app.handle_candidate_status_lifecycle()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status THEN
        NEW.status_changed_at = CURRENT_TIMESTAMP;

        IF NEW.status = 'Invalid'::app.applicant_status THEN
            NEW.invalidated_at = CURRENT_TIMESTAMP;
            NEW.reuploaded_at = NULL;
        ELSIF OLD.status = 'Invalid'::app.applicant_status
           AND NEW.status = 'Initial'::app.applicant_status THEN
            NEW.reupload_requested_at = CURRENT_TIMESTAMP;
        END IF;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS candidate_status_lifecycle_trigger ON app.candidate;
CREATE TRIGGER candidate_status_lifecycle_trigger
BEFORE UPDATE OF status ON app.candidate
FOR EACH ROW
EXECUTE FUNCTION app.handle_candidate_status_lifecycle();

CREATE OR REPLACE FUNCTION app.mark_candidate_reuploaded_after_invalid()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.uploaded IS TRUE
       AND (OLD.uploaded IS DISTINCT FROM TRUE) THEN
        UPDATE app.candidate
        SET reuploaded_at = CURRENT_TIMESTAMP
        WHERE candidate_id = NEW.candidate_id
          AND invalidated_at IS NOT NULL
          AND status = 'Initial'::app.applicant_status
          AND (reuploaded_at IS NULL OR reuploaded_at < invalidated_at);
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS candidate_document_reupload_marker_trigger ON app.candidate_document;
CREATE TRIGGER candidate_document_reupload_marker_trigger
AFTER UPDATE OF uploaded ON app.candidate_document
FOR EACH ROW
EXECUTE FUNCTION app.mark_candidate_reuploaded_after_invalid();