-- ChatGPT automation hub (PHP + MySQL)
-- Import via phpMyAdmin or: mysql -u USER -p DBNAME < schema.sql

SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS results (
  job_id VARCHAR(128) NOT NULL,
  machine_id VARCHAR(64) NOT NULL DEFAULT '',
  status VARCHAR(32) NOT NULL DEFAULT '',
  topic MEDIUMTEXT NULL,
  category VARCHAR(128) NOT NULL DEFAULT '',
  workflow_id VARCHAR(128) NOT NULL DEFAULT '',
  step2 LONGTEXT NULL,
  step3 LONGTEXT NULL,
  step4_prompt MEDIUMTEXT NULL,
  final_text LONGTEXT NULL,
  images_json MEDIUMTEXT NULL,
  error_message TEXT NULL,
  finished_at VARCHAR(40) NOT NULL DEFAULT '',
  updated_at VARCHAR(40) NOT NULL DEFAULT '',
  payload_json LONGTEXT NULL,
  PRIMARY KEY (job_id),
  KEY idx_results_status_finished (status, finished_at),
  KEY idx_results_machine (machine_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notes (
  job_id VARCHAR(128) NOT NULL,
  note MEDIUMTEXT NULL,
  updated_at VARCHAR(40) NOT NULL DEFAULT '',
  updated_by VARCHAR(64) NOT NULL DEFAULT '',
  PRIMARY KEY (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;