-- ============================================================================
-- Auto-Pilot — Warehouse scheduled automation engine
-- Adds: auto_pilot_runs run-log table for the cron-style scheduler endpoint.
-- Idempotent: safe to run multiple times.
-- ============================================================================

CREATE TABLE IF NOT EXISTS auto_pilot_runs (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT '1',
  triggered_by enum('cron','manual','cli') NOT NULL DEFAULT 'manual',
  waves_created int unsigned NOT NULL DEFAULT '0',
  replenish_created int unsigned NOT NULL DEFAULT '0',
  alerts_created int unsigned NOT NULL DEFAULT '0',
  alerts_updated int unsigned NOT NULL DEFAULT '0',
  alerts_resolved int unsigned NOT NULL DEFAULT '0',
  error_msg text COLLATE utf8mb4_unicode_ci,
  duration_ms int unsigned NOT NULL DEFAULT '0',
  started_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_apr_company (company_id, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Convert warehouse_alerts.alert_type to VARCHAR so adding new alert types in the
-- engine never requires a schema migration. (Previously an ENUM: a run that inserted a
-- type not yet in the enum aborted with "Data truncated for column 'alert_type'".)
ALTER TABLE warehouse_alerts
  MODIFY COLUMN alert_type varchar(40) NOT NULL DEFAULT 'system';
