-- ============================================================================
-- Proactive Intelligence Engine — FC-ERP
-- Adds:
--   ai_recommendations : cross-module "missed / stuck / late step" alerts that
--                        the intelligence engine detects and the AI Briefing
--                        screen + bell badge surface proactively.
--   auto_pilot_runs    : three new counters for AI recommendations/briefing.
-- Apply once:  mysql -u root fcqc_database < migration_ai_proactive.sql
-- ============================================================================

CREATE TABLE IF NOT EXISTS ai_recommendations (
  id bigint unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT '1',
  rec_no varchar(40) NOT NULL,
  gap_type varchar(40) NOT NULL,
  severity enum('critical','warning','info') NOT NULL DEFAULT 'info',
  score int unsigned NOT NULL DEFAULT '0',
  confidence enum('high','medium','low') NOT NULL DEFAULT 'medium',
  source enum('rule','llm') NOT NULL DEFAULT 'rule',
  title varchar(255) NOT NULL,
  message text,
  ref_type varchar(50) DEFAULT NULL,
  ref_id int unsigned DEFAULT NULL,
  ref_no varchar(100) DEFAULT NULL,
  action_route varchar(255) DEFAULT NULL,
  details json DEFAULT NULL,
  status enum('open','acknowledged','resolved','dismissed') NOT NULL DEFAULT 'open',
  first_seen_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at timestamp NULL DEFAULT NULL,
  resolved_by int unsigned DEFAULT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ai_rec_no (company_id, rec_no),
  KEY idx_air_status_sev (company_id, status, severity, last_seen_at),
  KEY idx_air_gap_ref (company_id, gap_type, ref_type, ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extend the Auto-Pilot run log with AI recommendation counters.
ALTER TABLE auto_pilot_runs
  ADD COLUMN ai_recs_created int unsigned NOT NULL DEFAULT '0' AFTER alerts_resolved,
  ADD COLUMN ai_recs_resolved int unsigned NOT NULL DEFAULT '0' AFTER ai_recs_created,
  ADD COLUMN ai_briefing text COLLATE utf8mb4_unicode_ci AFTER ai_recs_resolved;
