-- Intelligence Cache Table for Performance Optimization
-- This table enables advanced caching for faster intelligence responses
-- OPTIONAL: System works perfectly without this table

CREATE TABLE IF NOT EXISTS intelligence_cache (
  cache_key VARCHAR(255) PRIMARY KEY,
  cache_value LONGTEXT,
  expires_at DATETIME,
  organization_id INT,
  cache_type ENUM('realtime', 'analytical', 'predictions', 'patterns', 'strategic') DEFAULT 'analytical',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  INDEX idx_org_type_expires (organization_id, cache_type, expires_at),
  INDEX idx_expires (expires_at),
  INDEX idx_org_cache_type (organization_id, cache_type),
  
  FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

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