-- R537 / Phase 46 — Risk-tier proposed_actions.
-- Doc 2 #5.7: not all actions are equal. L1 = note/tag; L5 = destructive bulk.
-- Adding risk_level + back-filling existing rows from action_type.

ALTER TABLE proposed_actions ADD COLUMN risk_level INTEGER;

-- Backfill mapping: action_type → default risk_level
UPDATE proposed_actions SET risk_level = 1 WHERE action_type IN ('note', 'tag', 'classification');
UPDATE proposed_actions SET risk_level = 2 WHERE action_type IN ('ns_field_update', 'spec_update', 'create_customer_program', 'other');
UPDATE proposed_actions SET risk_level = 3 WHERE action_type IN ('price_change', 'bid_status_update', 'program_change', 'propose_bid_status', 'propose_quote', 'quote_draft', 'vendor_failover', 'bulk_cost_basis', 'propose_margin_target_pricing', 'collection_action');
UPDATE proposed_actions SET risk_level = 4 WHERE action_type IN ('propose_create_customer', 'propose_create_vendor', 'propose_create_item', 'soft_delete');
UPDATE proposed_actions SET risk_level = 5 WHERE action_type IN ('bulk_delete', 'destructive_bulk', 'mass_price_change');

-- Default catch-all for anything we missed
UPDATE proposed_actions SET risk_level = 2 WHERE risk_level IS NULL;

CREATE INDEX IF NOT EXISTS idx_proposed_actions_risk ON proposed_actions(risk_level);

INSERT OR IGNORE INTO migration_history (migration_id, description) VALUES
  ('113_risk_tiers_proposed_actions', 'R537 — risk_level column + 5-tier backfill');
