CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL
);

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 api_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(120) NOT NULL,
  key_prefix VARCHAR(20) NOT NULL,
  key_hash VARCHAR(255) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_used_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_api_keys_prefix_active (key_prefix, is_active)
);

CREATE TABLE IF NOT EXISTS fetch_requests (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  request_id CHAR(32) NOT NULL,
  api_key_id INT NULL,
  url TEXT NULL,
  normalized_url TEXT NULL,
  final_url TEXT NULL,
  success TINYINT(1) NOT NULL DEFAULT 0,
  fetch_method VARCHAR(20) NULL,
  fallback_used TINYINT(1) NOT NULL DEFAULT 0,
  status_code INT NULL,
  page_title VARCHAR(500) NULL,
  selectors_used JSON NULL,
  content_length INT NOT NULL DEFAULT 0,
  model_used VARCHAR(120) NULL,
  prompt_tokens INT NOT NULL DEFAULT 0,
  completion_tokens INT NOT NULL DEFAULT 0,
  total_tokens INT NOT NULL DEFAULT 0,
  cached TINYINT(1) NOT NULL DEFAULT 0,
  request_payload_json JSON NULL,
  response_json JSON NULL,
  error_code VARCHAR(120) NULL,
  error_message TEXT NULL,
  warnings_json JSON NULL,
  duration_ms INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_fetch_requests_created (created_at),
  INDEX idx_fetch_requests_success (success),
  INDEX idx_fetch_requests_method (fetch_method),
  INDEX idx_fetch_requests_cached (cached),
  INDEX idx_fetch_requests_request_id (request_id)
);

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)
);

INSERT INTO admin_users (email, password_hash, is_active)
SELECT 'admin@local', '$2y$12$98UMPrqJNOwImGdIRiNsI.iVKOs350.nMMfA7uP4zsiTPeKJzhHvC', 1
WHERE NOT EXISTS (SELECT 1 FROM admin_users);
