USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS ai_settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  is_secret TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS ai_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  task_type VARCHAR(120) NOT NULL,
  related_type VARCHAR(120) NULL,
  related_id BIGINT UNSIGNED NULL,
  provider VARCHAR(80) NULL,
  model VARCHAR(120) NULL,
  request_payload JSON NULL,
  response_payload JSON NULL,
  status ENUM('demo','success','error') NOT NULL DEFAULT 'demo',
  error_message TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY ai_logs_task_type_index (task_type),
  KEY ai_logs_related_index (related_type, related_id),
  KEY ai_logs_created_by_index (created_by),
  CONSTRAINT ai_logs_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

INSERT INTO ai_settings (setting_key, setting_value, is_secret) VALUES
('provider', 'openai', 0),
('model', 'gpt-4.1-mini', 0),
('api_key', '', 1),
('enabled', '0', 0)
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value), is_secret = VALUES(is_secret);