-- Prediction Accuracy Tracking for ML Learning
-- This table enables machine learning accuracy tracking for future improvements
-- OPTIONAL: System works perfectly without this table

CREATE TABLE IF NOT EXISTS prediction_accuracy (
  id INT AUTO_INCREMENT PRIMARY KEY,
  organization_id INT NOT NULL,
  prediction_type VARCHAR(100) NOT NULL,
  prediction_key VARCHAR(255),
  predicted_value TEXT,
  actual_value TEXT,
  accuracy_score DECIMAL(5,4) DEFAULT NULL,
  confidence_score DECIMAL(5,4) DEFAULT NULL,
  prediction_date DATETIME,
  outcome_date DATETIME,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_org_type (organization_id, prediction_type),
  INDEX idx_org_date (organization_id, prediction_date),
  INDEX idx_accuracy (accuracy_score),
  INDEX idx_prediction_key (prediction_key),
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

-- Verify table was created
SELECT 'prediction_accuracy table created successfully' as Status;
DESCRIBE prediction_accuracy;