

CREATE TABLE IF NOT EXISTS app_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT NULL,
  updated_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS fetch_cache (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  cache_key CHAR(64) NOT NULL UNIQUE,
  response_json JSON NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  INDEX idx_fetch_cache_expires (expires_at)
);

CREATE TABLE IF NOT EXISTS schema_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  template_name VARCHAR(150) NOT NULL,
  instruction_text TEXT NOT NULL,
  schema_json JSON NOT NULL,
  defaults_json JSON NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  INDEX idx_schema_templates_active (is_active)
);
