-- ============================================================
-- CUTTALO AI NEURAL NETWORK - DATABASE SCHEMA
-- Sistema di intelligenza artificiale auto-apprendente
-- ============================================================

-- Tabella versioni modello
CREATE TABLE IF NOT EXISTS wp_ai_model_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  model_name VARCHAR(64) NOT NULL,           -- 'conversion', 'churn', 'response_timing', 'customer_value'
  version VARCHAR(32) NOT NULL,
  trained_at DATETIME NOT NULL,
  samples_count INT NOT NULL,

  -- Metriche performance
  accuracy DECIMAL(5,4),
  precision_score DECIMAL(5,4),
  recall_score DECIMAL(5,4),
  f1_score DECIMAL(5,4),
  auc_roc DECIMAL(5,4),
  mse DECIMAL(10,6),                         -- Per modelli regression

  -- Configurazione modello
  model_config JSON,                         -- Architettura, iperparametri
  feature_config JSON,                       -- Features utilizzate

  -- Pesi modello (serializzati)
  weights_blob LONGBLOB,

  -- Feature importance
  feature_importance JSON,

  is_active TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_model_name (model_name),
  INDEX idx_active (is_active),
  INDEX idx_trained (trained_at)
);

-- Tabella samples di training
CREATE TABLE IF NOT EXISTS wp_ai_training_samples (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  model_name VARCHAR(64) NOT NULL,

  -- Riferimenti entita
  entity_type VARCHAR(32) NOT NULL,          -- 'order', 'customer', 'conversation', 'upload'
  entity_id BIGINT UNSIGNED NOT NULL,

  -- Features estratte (JSON)
  features JSON NOT NULL,

  -- Label/target
  label VARCHAR(64),                         -- Per classification
  target_value DECIMAL(10,4),                -- Per regression

  -- Metadata
  extracted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  used_in_training TINYINT(1) DEFAULT 0,
  training_version VARCHAR(32),

  INDEX idx_model (model_name),
  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_extracted (extracted_at),
  INDEX idx_used (used_in_training)
);

-- Tabella predizioni in tempo reale
CREATE TABLE IF NOT EXISTS wp_ai_predictions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  model_name VARCHAR(64) NOT NULL,
  model_version VARCHAR(32) NOT NULL,

  -- Riferimenti
  entity_type VARCHAR(32) NOT NULL,
  entity_id BIGINT UNSIGNED NOT NULL,

  -- Predizione
  prediction VARCHAR(64),                    -- Classe predetta
  probability DECIMAL(5,4),                  -- Probabilita principale
  probabilities JSON,                        -- Tutte le probabilita
  confidence DECIMAL(5,4),

  -- Features usate
  features_snapshot JSON,

  -- Feedback (per apprendimento)
  actual_outcome VARCHAR(64),
  outcome_recorded_at DATETIME,
  was_correct TINYINT(1),

  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_model (model_name),
  INDEX idx_entity (entity_type, entity_id),
  INDEX idx_created (created_at),
  INDEX idx_outcome (actual_outcome)
);

-- Tabella customer insights aggregati
CREATE TABLE IF NOT EXISTS wp_ai_customer_insights (
  customer_id BIGINT UNSIGNED PRIMARY KEY,

  -- Score aggregati
  lifetime_value_score DECIMAL(5,2),         -- 0-100
  churn_risk_score DECIMAL(5,2),             -- 0-100
  conversion_propensity DECIMAL(5,2),        -- 0-100
  engagement_score DECIMAL(5,2),             -- 0-100

  -- Segmentazione
  segment VARCHAR(32),                       -- 'vip', 'regular', 'at_risk', 'new', 'dormant'
  segment_confidence DECIMAL(5,4),

  -- Preferenze apprese
  preferred_materials JSON,                  -- Materiali preferiti
  preferred_services JSON,                   -- Servizi preferiti (taglio, incisione, stencil)
  price_sensitivity VARCHAR(16),             -- 'low', 'medium', 'high'
  response_preference JSON,                  -- Orari/giorni preferiti

  -- Comportamento
  avg_order_value DECIMAL(10,2),
  order_frequency_days DECIMAL(6,2),
  last_activity_days INT,
  total_uploads INT,
  total_conversations INT,

  -- Predizioni
  next_order_probability DECIMAL(5,4),
  expected_next_order_value DECIMAL(10,2),
  recommended_actions JSON,                  -- Azioni suggerite

  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_segment (segment),
  INDEX idx_ltv (lifetime_value_score),
  INDEX idx_churn (churn_risk_score)
);

-- Tabella order insights
CREATE TABLE IF NOT EXISTS wp_ai_order_insights (
  order_id BIGINT UNSIGNED PRIMARY KEY,

  -- Probabilita conversione
  conversion_probability DECIMAL(5,4),
  conversion_factors JSON,

  -- Rischio abbandono
  abandonment_risk DECIMAL(5,4),
  abandonment_factors JSON,

  -- Timing risposta ottimale
  optimal_response_hour TINYINT,
  response_urgency VARCHAR(16),              -- 'immediate', 'today', 'tomorrow', 'week'

  -- Sentiment conversazione
  conversation_sentiment DECIMAL(3,2),       -- -1 to +1
  sentiment_trend VARCHAR(16),               -- 'improving', 'stable', 'declining'

  -- Valore stimato
  estimated_final_value DECIMAL(10,2),
  upsell_opportunities JSON,

  -- Complessita ordine
  complexity_score DECIMAL(5,2),             -- 0-100
  estimated_processing_hours DECIMAL(6,2),

  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_conversion (conversion_probability),
  INDEX idx_abandonment (abandonment_risk)
);

-- Tabella upload insights
CREATE TABLE IF NOT EXISTS wp_ai_upload_insights (
  upload_id BIGINT UNSIGNED PRIMARY KEY,
  upload_type VARCHAR(32) NOT NULL,          -- 'laser', 'stencil', 'stamp'

  -- Qualita file
  file_quality_score DECIMAL(5,2),           -- 0-100
  complexity_score DECIMAL(5,2),

  -- Predizioni prezzo
  predicted_price DECIMAL(10,2),
  price_confidence DECIMAL(5,4),

  -- Probabilita acquisto
  purchase_probability DECIMAL(5,4),
  purchase_factors JSON,

  -- Analisi design
  design_category VARCHAR(64),               -- Categoria design rilevata
  similar_designs JSON,                      -- ID design simili

  -- Suggerimenti
  material_recommendations JSON,
  optimization_suggestions JSON,

  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_type (upload_type),
  INDEX idx_purchase (purchase_probability)
);

-- Tabella pattern appresi
CREATE TABLE IF NOT EXISTS wp_ai_learned_patterns (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pattern_type VARCHAR(64) NOT NULL,         -- 'conversion', 'timing', 'pricing', 'material'
  pattern_name VARCHAR(128) NOT NULL,

  -- Condizioni pattern (JSON)
  conditions JSON NOT NULL,

  -- Statistiche
  occurrences INT DEFAULT 0,
  positive_outcomes INT DEFAULT 0,
  negative_outcomes INT DEFAULT 0,

  -- Impatto calcolato
  impact_score DECIMAL(6,4),                 -- Impatto sul target
  confidence DECIMAL(5,4),

  -- Esempio
  example_entities JSON,

  discovered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_validated DATETIME,
  is_active TINYINT(1) DEFAULT 1,

  INDEX idx_type (pattern_type),
  INDEX idx_impact (impact_score),
  INDEX idx_active (is_active)
);

-- Tabella training jobs
CREATE TABLE IF NOT EXISTS wp_ai_training_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  model_name VARCHAR(64) NOT NULL,

  -- Stato
  status VARCHAR(32) NOT NULL,               -- 'queued', 'running', 'completed', 'failed'

  -- Configurazione
  config JSON,

  -- Timing
  started_at DATETIME,
  completed_at DATETIME,
  duration_seconds INT,

  -- Risultati
  samples_processed INT,
  result_version VARCHAR(32),
  error_message TEXT,

  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_model (model_name),
  INDEX idx_status (status),
  INDEX idx_created (created_at)
);

-- Tabella feature definitions
CREATE TABLE IF NOT EXISTS wp_ai_feature_definitions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  feature_name VARCHAR(128) NOT NULL UNIQUE,
  feature_group VARCHAR(64) NOT NULL,        -- 'customer', 'order', 'conversation', 'upload', 'temporal'

  -- Tipo e calcolo
  data_type VARCHAR(32) NOT NULL,            -- 'numeric', 'categorical', 'boolean', 'text'
  calculation_method TEXT,                   -- SQL o descrizione calcolo

  -- Normalizzazione
  normalization VARCHAR(32),                 -- 'minmax', 'zscore', 'log', 'none'
  min_value DECIMAL(20,6),
  max_value DECIMAL(20,6),
  mean_value DECIMAL(20,6),
  std_value DECIMAL(20,6),

  -- Metadata
  description TEXT,
  is_active TINYINT(1) DEFAULT 1,

  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_group (feature_group),
  INDEX idx_active (is_active)
);

-- Tabella metriche real-time
CREATE TABLE IF NOT EXISTS wp_ai_realtime_metrics (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  metric_name VARCHAR(64) NOT NULL,
  metric_value DECIMAL(20,6) NOT NULL,
  metric_metadata JSON,

  recorded_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_metric (metric_name),
  INDEX idx_recorded (recorded_at)
);

-- Tabella A/B test AI
CREATE TABLE IF NOT EXISTS wp_ai_experiments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  experiment_name VARCHAR(128) NOT NULL,

  -- Configurazione
  model_a_version VARCHAR(32),
  model_b_version VARCHAR(32),
  traffic_split DECIMAL(3,2) DEFAULT 0.50,

  -- Stato
  status VARCHAR(32) NOT NULL,               -- 'active', 'paused', 'completed'
  started_at DATETIME,
  ended_at DATETIME,

  -- Risultati
  samples_a INT DEFAULT 0,
  samples_b INT DEFAULT 0,
  conversions_a INT DEFAULT 0,
  conversions_b INT DEFAULT 0,
  winner VARCHAR(1),                         -- 'A', 'B', or NULL
  statistical_significance DECIMAL(5,4),

  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_status (status)
);
