-- Controle operacional de migrations SQL.
-- O runner tools/migrate-sql.php tambem cria estas tabelas automaticamente.

CREATE TABLE IF NOT EXISTS wpjy_schema_migrations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  migration_key VARCHAR(190) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_name VARCHAR(190) NOT NULL,
  checksum CHAR(64) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  batch INT UNSIGNED NULL DEFAULT NULL,
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  last_started_at DATETIME NULL DEFAULT NULL,
  last_finished_at DATETIME NULL DEFAULT NULL,
  duration_ms INT UNSIGNED NULL DEFAULT NULL,
  statement_count INT UNSIGNED NOT NULL DEFAULT 0,
  deploy_run_id BIGINT UNSIGNED NULL DEFAULT NULL,
  repo_full_name VARCHAR(190) NULL DEFAULT NULL,
  commit_sha VARCHAR(64) NULL DEFAULT NULL,
  last_error_message TEXT NULL DEFAULT NULL,
  last_output MEDIUMTEXT NULL DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_schema_migrations_key (migration_key),
  KEY idx_schema_migrations_status (status),
  KEY idx_schema_migrations_batch (batch),
  KEY idx_schema_migrations_checksum (checksum)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wpjy_schema_migration_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  migration_id BIGINT UNSIGNED NULL DEFAULT NULL,
  migration_key VARCHAR(190) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  checksum CHAR(64) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'running',
  batch INT UNSIGNED NULL DEFAULT NULL,
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME NULL DEFAULT NULL,
  duration_ms INT UNSIGNED NULL DEFAULT NULL,
  statement_count INT UNSIGNED NOT NULL DEFAULT 0,
  deploy_run_id BIGINT UNSIGNED NULL DEFAULT NULL,
  repo_full_name VARCHAR(190) NULL DEFAULT NULL,
  commit_sha VARCHAR(64) NULL DEFAULT NULL,
  error_message TEXT NULL DEFAULT NULL,
  output MEDIUMTEXT NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_schema_migration_runs_migration (migration_id),
  KEY idx_schema_migration_runs_key (migration_key),
  KEY idx_schema_migration_runs_status (status),
  KEY idx_schema_migration_runs_batch (batch),
  KEY idx_schema_migration_runs_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;