-- R549 / Phase 53 — Workflow Contract Schema v2.
-- The pattern Mike named:
--   TRIGGER → LOAD CONTEXT → DECIDE → HITL GATE → FAN-OUT → VERIFY → REFLECT
-- Every workflow is a contract. Once mapped, the runner executes the full
-- cascade automatically. AI proposes; system executes; Mike gates.
--
-- The v1 schema (workflow_definitions, migration 117) is too thin. v2
-- adds explicit columns for: trigger spec, inputs, preconditions,
-- fan-out targets, verification checks, retry policy, reflexion linkage.

ALTER TABLE workflow_definitions ADD COLUMN trigger_spec_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN inputs_required_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN context_to_load_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN preconditions_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN fan_out_targets_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN post_actions_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN verify_checks_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN retry_policy_json TEXT;
ALTER TABLE workflow_definitions ADD COLUMN reflexion_enabled INTEGER NOT NULL DEFAULT 1;
ALTER TABLE workflow_definitions ADD COLUMN contract_version INTEGER NOT NULL DEFAULT 1;

-- workflow_step_log — Per-step execution audit. Reflexion + post-mortem queries
-- run off this. Augments existing workflow_runs (which is per-run, not per-step).
CREATE TABLE IF NOT EXISTS workflow_step_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  workflow_run_id TEXT NOT NULL,
  workflow_type TEXT NOT NULL,
  step_id TEXT NOT NULL,
  step_name TEXT NOT NULL,
  step_index INTEGER NOT NULL,
  status TEXT NOT NULL,         -- 'pending' | 'running' | 'ok' | 'failed' | 'skipped' | 'retrying'
  started_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at TEXT,
  duration_ms INTEGER,
  attempt INTEGER NOT NULL DEFAULT 1,
  input_json TEXT,
  output_json TEXT,
  error TEXT,
  proposed_action_id INTEGER,    -- if this step staged a HITL proposal
  ns_push_id INTEGER,            -- if this step queued a NS write
  d1_rows_affected INTEGER
);
CREATE INDEX IF NOT EXISTS idx_wf_step_log_run ON workflow_step_log(workflow_run_id);
CREATE INDEX IF NOT EXISTS idx_wf_step_log_type ON workflow_step_log(workflow_type);
CREATE INDEX IF NOT EXISTS idx_wf_step_log_status ON workflow_step_log(status);

-- workflow_verify_results — outcome of post-execution verification checks
CREATE TABLE IF NOT EXISTS workflow_verify_results (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  workflow_run_id TEXT NOT NULL,
  workflow_type TEXT NOT NULL,
  check_name TEXT NOT NULL,
  status TEXT NOT NULL,         -- 'pass' | 'fail' | 'pending' | 'timeout'
  expected_json TEXT,
  actual_json TEXT,
  ran_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes TEXT
);
CREATE INDEX IF NOT EXISTS idx_wf_verify_run ON workflow_verify_results(workflow_run_id);

-- Worked example: bid_price_update — Mike's literal example.
-- This is the canonical pattern every other workflow follows.
UPDATE workflow_definitions
SET
  trigger_spec_json = '{"type":"hitl_approval","event":"proposed_actions row with action_type=''price_change'' or ''bid_price_update'' approved"}',
  inputs_required_json = '["customer_id","sku","new_price","effective_date","reasoning_text","bid_id?"]',
  context_to_load_json = '[{"name":"current_pricing","sql":"SELECT * FROM pricing_master WHERE customer_id=? AND item_code=?"},{"name":"bid_line","sql":"SELECT * FROM bid_lines WHERE bid_id=? AND item_code=?","if":"bid_id present"},{"name":"vendor_cost","sql":"SELECT unit_cost, effective_from FROM vendor_costs WHERE item_code=? ORDER BY effective_from DESC LIMIT 1"},{"name":"reflexion","sql":"SELECT * FROM reflexion_log WHERE entity_type=''customer'' AND entity_id=? AND tags LIKE ''%price%''","if":"reflexion_enabled"}]',
  preconditions_json = '[{"check":"vendor_cost_recency","rule":"effective_from > datetime(''now'',''-7 days'')","severity":"warn"},{"check":"new_price_positive","rule":"new_price > 0","severity":"block"},{"check":"margin_floor","rule":"(new_price - cost_basis) / new_price * 100 >= customer_program.margin_floor","severity":"warn"}]',
  fan_out_targets_json = '[{"id":"write_ns","kind":"ns_push","record_type":"itempricing","fields":{"customer":"?customer_id","item":"?sku","price":"?new_price","effective":"?effective_date"},"queue":"NS_PUSH_QUEUE","on_failure":"retry_3_then_alert"},{"id":"write_d1","kind":"d1_write","parallel_with":"write_ns","operations":["UPDATE pricing_master SET case_price=?, cost_basis=?, margin_pct=?, updated_at=now WHERE id=?","INSERT INTO pricing_history (customer_id,item_code,case_price,changed_at,changed_by) VALUES (?,?,?,now,?)"]},{"id":"update_bid_lines","kind":"d1_write","if":"bid_id present","operations":["UPDATE bid_lines SET proposed_price=?, updated_at=now WHERE bid_id=? AND item_code=?"]},{"id":"regen_bid_artifact","kind":"http_call","if":"bid_id present AND artifact_r2_key exists","method":"POST","path":"/api/bids/:bid_id/regenerate","produces":["updated bid PDF","updated price-quotes tab"]},{"id":"invalidate_hub_cache","kind":"kv_invalidate","if":"bid_id IN active_hub_bids","keys":["hub:nycdoe:bid_?bid_id","hub:nycdoe:summary"]},{"id":"spec_sheet_check","kind":"flag","if":"abs(price_change_pct) > 5","action":"INSERT proposed_actions (action_type=spec_review_needed, entity_ref=?sku)"},{"id":"customer_notify","kind":"hitl_email_draft","if":"customer has open quote AND price_increase","tool":"propose_email_to_customer"}]',
  post_actions_json = '[{"id":"log_run","action":"INSERT INTO workflow_runs (workflow_type, status, inputs_json, started_at, finished_at)"},{"id":"reflexion","action":"INSERT INTO reflexion_log (workflow_run_id, entity_type, entity_id, outcome, tags)","if":"reflexion_enabled"},{"id":"customer_profile_touch","action":"UPDATE customer_profiles SET last_pricing_event=now WHERE customer_id=?"}]',
  verify_checks_json = '[{"name":"ns_reflects_new_price","window_minutes":15,"sql_check":"SELECT case_price FROM pricing_customer_prices WHERE customer_id=? AND item_code=? AND last_synced_at > datetime(''now'',''-15 minutes'')","expected":"new_price","on_fail":"raise_anomaly"},{"name":"hub_cache_busted","window_seconds":60,"http_check":"GET /api/hub/nycdoe/bid/:bid_id should return new_price"},{"name":"d1_pricing_master_updated","sql_check":"SELECT case_price FROM pricing_master WHERE customer_id=? AND item_code=?","expected":"new_price"}]',
  retry_policy_json = '{"max_attempts":3,"backoff":"exponential","base_ms":1000,"max_ms":30000,"alert_on_final_failure":true}',
  contract_version = 2,
  risk_level = 3,
  expected_duration_minutes = 5
WHERE workflow_type = 'draft_quote';  -- closest existing match — will rename below

-- Add a NEW workflow_type for bid_price_update (the canonical worked example)
INSERT OR IGNORE INTO workflow_definitions (
  workflow_type, display_name, description, trigger_type, risk_level,
  steps_json, cascade_tables_json, hitl_gates_json, expected_duration_minutes, owner,
  trigger_spec_json, inputs_required_json, context_to_load_json,
  preconditions_json, fan_out_targets_json, post_actions_json, verify_checks_json,
  retry_policy_json, contract_version
) VALUES (
  'bid_price_update',
  'Bid price update (canonical)',
  'When Mike approves a new bid line price: cascade to NS pricing record, pricing_master, pricing_history, bid_lines, bid artifact regen, hub cache, spec_sheet review flag, customer notification.',
  'hitl_approval', 3,
  '[{"id":"write_ns","name":"NS pricing update"},{"id":"write_d1","name":"D1 pricing_master + history"},{"id":"update_bid_lines","name":"bid_lines update (if bid_id)"},{"id":"regen_bid_artifact","name":"Regenerate bid PDF (if applicable)"},{"id":"invalidate_hub_cache","name":"Invalidate hub UI cache"},{"id":"spec_sheet_check","name":"Flag spec review if >5% change"},{"id":"customer_notify","name":"Customer email draft (if open quote)"}]',
  '["pricing_master","pricing_history","bid_lines","ns_pending_pushes","proposed_actions"]',
  '[0]',
  5, 'mike',
  '{"type":"hitl_approval","event":"proposed_actions approved with action_type=bid_price_update"}',
  '["customer_id","sku","new_price","effective_date","reasoning_text","bid_id?"]',
  '[{"name":"current_pricing","sql":"SELECT * FROM pricing_master WHERE customer_id=? AND item_code=?"},{"name":"bid_line","if":"bid_id","sql":"SELECT * FROM bid_lines WHERE bid_id=? AND item_code=?"},{"name":"vendor_cost","sql":"SELECT unit_cost, effective_from FROM vendor_costs WHERE item_code=? ORDER BY effective_from DESC LIMIT 1"}]',
  '[{"check":"vendor_cost_recency","rule":"effective_from > datetime(''now'',''-7 days'')","severity":"warn"},{"check":"new_price_positive","rule":"new_price > 0","severity":"block"},{"check":"margin_floor","rule":"computed_margin_pct >= customer_program.margin_floor","severity":"warn"}]',
  '[{"id":"write_ns","kind":"ns_push","record_type":"itempricing","queue":"NS_PUSH_QUEUE","on_failure":"retry_3_then_alert"},{"id":"write_d1","kind":"d1_write","parallel_with":"write_ns"},{"id":"update_bid_lines","kind":"d1_write","if":"bid_id present"},{"id":"regen_bid_artifact","kind":"http_call","if":"bid_id AND artifact_r2_key"},{"id":"invalidate_hub_cache","kind":"kv_invalidate","if":"bid_id IN active_hub_bids"},{"id":"spec_sheet_check","kind":"flag","if":"abs(price_change_pct) > 5"},{"id":"customer_notify","kind":"hitl_email_draft","if":"customer_has_open_quote AND price_increase"}]',
  '[{"id":"log_run","action":"INSERT workflow_runs"},{"id":"reflexion","action":"INSERT reflexion_log"},{"id":"customer_profile_touch","action":"UPDATE customer_profiles.last_pricing_event"}]',
  '[{"name":"ns_reflects_new_price","window_minutes":15,"on_fail":"raise_anomaly"},{"name":"hub_cache_busted","window_seconds":60},{"name":"d1_pricing_master_updated","window_seconds":5}]',
  '{"max_attempts":3,"backoff":"exponential","base_ms":1000,"max_ms":30000,"alert_on_final_failure":true}',
  2
);

INSERT OR IGNORE INTO migration_history (migration_id, description) VALUES
  ('118_workflow_contracts_v2', 'R549 — workflow contract v2 schema + bid_price_update canonical worked example');
