-- Phase 2 Vendor Intelligence demo data. Seeds a small roster of staffing vendors and subcontractors for the
-- demo org and a spread of vendor_submissions linking ALREADY EVALUATED candidate and requirement pairs to
-- those vendors with varied lifecycle statuses, so the vendor_performance_scorecard and the k suppressed
-- vendor_quality_distribution have a live scenario. It selects real evaluated pairs dynamically, so it works on
-- both the clean demo seed and the restored live pool. Idempotent via fixed vendor ids and ON CONFLICT. All
-- data is synthetic Demo data. Run as the migrator role after the candidate evaluations exist.

\set org 11111111-1111-1111-1111-111111111111

BEGIN;

-- Four vendors: two staffing firms, one subcontractor, one prime, with fixed ids so a rerun reasserts them.
INSERT INTO staffing_vendors (organization_id, id, name, vendor_type) VALUES
  (:'org', 'd0000000-0000-0000-0000-000000000001', 'Apex Cleared Talent',      'staffing_firm'),
  (:'org', 'd0000000-0000-0000-0000-000000000002', 'Summit Federal Staffing',  'staffing_firm'),
  (:'org', 'd0000000-0000-0000-0000-000000000003', 'Trident Subcontracting',   'subcontractor'),
  (:'org', 'd0000000-0000-0000-0000-000000000004', 'Meridian Prime',           'prime')
ON CONFLICT (organization_id, id) DO NOTHING;

-- Submissions: take the first 160 evaluated candidate and requirement pairs, assign each to one of the four
-- vendors round robin, give each a lifecycle status round robin, and set submitted_at a few days after the
-- requirement was created so the time to submit is a clean positive interval. Round robin over a single pair
-- means each pair goes to exactly one vendor, so the composite primary key never collides. Forty submissions
-- per vendor clears the ten evaluated candidate quality floor so the scorecard quality publishes.
INSERT INTO vendor_submissions (organization_id, vendor_id, candidate_id, requirement_id, status, submitted_at)
SELECT
  :'org'::uuid,
  (ARRAY[
     'd0000000-0000-0000-0000-000000000001',
     'd0000000-0000-0000-0000-000000000002',
     'd0000000-0000-0000-0000-000000000003',
     'd0000000-0000-0000-0000-000000000004'
   ])[1 + (rn % 4)]::uuid,
  candidate_id,
  requirement_id,
  -- status keys on rn/4 (integer division) so it is independent of the vendor (which keys on rn mod 4); each
  -- vendor therefore accrues a rotation of all four statuses across its submissions, giving varied fill rates.
  (ARRAY['placed', 'interviewed', 'submitted', 'rejected'])[1 + ((rn / 4) % 4)],
  created_at + ((1 + (rn % 12)) || ' days')::interval
FROM (
  SELECT ce.candidate_id, ce.requirement_id, r.created_at,
         row_number() OVER (ORDER BY ce.candidate_id, ce.requirement_id) - 1 AS rn
  FROM candidate_evaluations ce
  JOIN requirements r ON r.organization_id = ce.organization_id AND r.id = ce.requirement_id
  WHERE ce.organization_id = :'org'
  ORDER BY ce.candidate_id, ce.requirement_id
  LIMIT 160
) x
ON CONFLICT (organization_id, vendor_id, candidate_id, requirement_id) DO NOTHING;

COMMIT;