-- 033_professional_summary.sql
-- Job-agnostic professional summary (tech-stack speciality + department).
-- Latest denorm on each intake row; per-run snapshot on ats_results.
-- ats_results.rescan_run_id is the 1:N link from an On-Hold ReScan run.
-- Applied at startup by alembic_setup.run_manual_sql().

ALTER TABLE app.inbox_messages
    ADD COLUMN IF NOT EXISTS professional_summary TEXT;

ALTER TABLE app.form_data
    ADD COLUMN IF NOT EXISTS professional_summary TEXT;

ALTER TABLE app.manual_upload_candidate
    ADD COLUMN IF NOT EXISTS professional_summary TEXT;

ALTER TABLE app.users
    ADD COLUMN IF NOT EXISTS professional_summary TEXT;

ALTER TABLE app.candidates
    ADD COLUMN IF NOT EXISTS professional_summary TEXT;

ALTER TABLE app.ats_results
    ADD COLUMN IF NOT EXISTS professional_summary TEXT,
    ADD COLUMN IF NOT EXISTS rescan_run_id UUID REFERENCES app.inbox_rescan_runs (id);

ALTER TABLE app.inbox_rescan_runs
    ADD COLUMN IF NOT EXISTS summaries JSONB NOT NULL DEFAULT '[]'::jsonb;

CREATE INDEX IF NOT EXISTS ix_ats_results_rescan_run_id
    ON app.ats_results (rescan_run_id);