CREATE TABLE IF NOT EXISTS order_intelligence (
  order_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  certificate_type VARCHAR(180) NULL,
  certificate_category VARCHAR(40) NULL,
  modality VARCHAR(120) NULL,
  free_request TINYINT(1) NOT NULL DEFAULT 0,
  has_apostille TINYINT(1) NOT NULL DEFAULT 0,
  has_attachment TINYINT(1) NOT NULL DEFAULT 0,
  delivery_type VARCHAR(40) NULL,
  has_book TINYINT(1) NOT NULL DEFAULT 0,
  has_page TINYINT(1) NOT NULL DEFAULT 0,
  has_term TINYINT(1) NOT NULL DEFAULT 0,
  registered_name VARCHAR(255) NULL,
  requester_name VARCHAR(255) NULL,
  book VARCHAR(80) NULL,
  page VARCHAR(80) NULL,
  term VARCHAR(80) NULL,
  pending_flags TEXT NULL,
  pending_count INT NOT NULL DEFAULT 0,
  source_hash CHAR(40) NULL,
  processed_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_oi_category (certificate_category),
  INDEX idx_oi_delivery (delivery_type),
  INDEX idx_oi_apostille (has_apostille),
  INDEX idx_oi_attachment (has_attachment),
  INDEX idx_oi_pending (pending_count),
  INDEX idx_oi_modality (modality),
  CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS report_cache (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cache_key VARCHAR(190) NOT NULL UNIQUE,
  payload LONGTEXT NOT NULL,
  expires_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_cache_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS intelligence_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  status VARCHAR(40) NOT NULL DEFAULT 'done',
  processed INT NOT NULL DEFAULT 0,
  message TEXT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_ij_status (status),
  INDEX idx_ij_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO order_intelligence (order_id, processed_at)
SELECT id, NULL FROM orders;

-- Índices auxiliares. Se algum índice já existir, ignore o aviso/erro de duplicidade e siga em frente.
-- Alguns MySQL/MariaDB antigos não suportam ADD INDEX IF NOT EXISTS; por isso mantemos a migration principal sem depender disso.
