-- ============================================================
-- FashCognitive Apparel MES - Phase 5: AI / Intelligence Layer
-- Persists AI analyses (line balancing, bottlenecks, predictive
-- quality, MPO release readiness, LLM insights) for history,
-- audit and trend tracking.
-- Applies to live DB; production_schema.sql is kept in sync.
-- ============================================================

CREATE TABLE IF NOT EXISTS production_ai_analysis (
  id int unsigned NOT NULL AUTO_INCREMENT,
  company_id int unsigned NOT NULL DEFAULT 1,
  ref_type enum('order','line','mpo','unit','global') DEFAULT 'global' COMMENT 'What the analysis is attached to',
  ref_id int unsigned DEFAULT NULL,
  analysis_type varchar(50) NOT NULL COMMENT 'line_balancing | bottleneck | predictive_quality | mpo_release | ai_insights',
  risk_score decimal(5,2) DEFAULT NULL COMMENT '0-100 score',
  severity enum('low','medium','high','critical') DEFAULT 'low',
  summary varchar(500) DEFAULT NULL,
  recommendation text,
  detail json DEFAULT NULL,
  ai_insights text,
  ai_provider varchar(30) DEFAULT NULL,
  ai_model varchar(80) DEFAULT NULL,
  analyzed_by int unsigned DEFAULT NULL COMMENT 'users.id',
  status enum('pending','completed','failed') NOT NULL DEFAULT 'completed',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY company_id (company_id),
  KEY analysis_type (analysis_type),
  KEY ref (ref_type, ref_id),
  KEY created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
