CREATE TABLE IF NOT EXISTS fm_migrations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  filename VARCHAR(255) NOT NULL,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY fm_migrations_filename_uq (filename)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fm_field_policies (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client VARCHAR(64) NOT NULL,
  field_name VARCHAR(64) NOT NULL,
  policy ENUM('auto_pass_through','approval_required','locked_override') NOT 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 fm_field_policies_client_field_uq (client, field_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fm_sync_runs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client VARCHAR(64) NOT NULL,
  trigger_type ENUM('manual','cron','web') NOT NULL,
  trigger_user_id INT UNSIGNED DEFAULT NULL,
  status ENUM('running','completed','failed','skipped') NOT NULL DEFAULT 'running',
  started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  finished_at DATETIME DEFAULT NULL,
  source_variants_count INT UNSIGNED NOT NULL DEFAULT 0,
  mapped_variants_count INT UNSIGNED NOT NULL DEFAULT 0,
  auto_applied_changes_count INT UNSIGNED NOT NULL DEFAULT 0,
  pending_changes_count INT UNSIGNED NOT NULL DEFAULT 0,
  skipped_variants_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT DEFAULT NULL,
  summary_json LONGTEXT DEFAULT NULL,
  PRIMARY KEY (id),
  KEY fm_sync_runs_client_started_ix (client, started_at),
  KEY fm_sync_runs_status_ix (status),
  CONSTRAINT fm_sync_runs_trigger_user_fk FOREIGN KEY (trigger_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fm_variant_states (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client VARCHAR(64) NOT NULL,
  variant_id VARCHAR(64) NOT NULL,
  product_id VARCHAR(64) NOT NULL,
  product_title VARCHAR(255) NOT NULL,
  variant_title VARCHAR(255) NOT NULL,
  source_payload_json LONGTEXT NOT NULL,
  source_feed_json LONGTEXT NOT NULL,
  enriched_feed_json LONGTEXT NOT NULL,
  last_sync_run_id BIGINT UNSIGNED NOT 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 fm_variant_states_client_variant_uq (client, variant_id),
  KEY fm_variant_states_client_product_ix (client, product_id),
  KEY fm_variant_states_last_sync_ix (last_sync_run_id),
  CONSTRAINT fm_variant_states_sync_run_fk FOREIGN KEY (last_sync_run_id) REFERENCES fm_sync_runs(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fm_field_changes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  sync_run_id BIGINT UNSIGNED NOT NULL,
  client VARCHAR(64) NOT NULL,
  variant_id VARCHAR(64) NOT NULL,
  product_id VARCHAR(64) NOT NULL,
  field_name VARCHAR(64) NOT NULL,
  old_value_text MEDIUMTEXT,
  new_value_text MEDIUMTEXT,
  policy ENUM('auto_pass_through','approval_required','locked_override') NOT NULL,
  status ENUM('auto_applied','pending','approved','rejected','superseded') NOT NULL,
  decided_by_user_id INT UNSIGNED DEFAULT NULL,
  decided_at DATETIME DEFAULT NULL,
  decision_note TEXT 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),
  KEY fm_field_changes_client_status_ix (client, status),
  KEY fm_field_changes_product_pending_ix (client, product_id, status),
  KEY fm_field_changes_variant_field_pending_ix (client, variant_id, field_name, status),
  KEY fm_field_changes_sync_ix (sync_run_id),
  CONSTRAINT fm_field_changes_sync_fk FOREIGN KEY (sync_run_id) REFERENCES fm_sync_runs(id) ON DELETE CASCADE,
  CONSTRAINT fm_field_changes_user_fk FOREIGN KEY (decided_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fm_audit_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  client VARCHAR(64) NOT NULL,
  user_id INT UNSIGNED DEFAULT NULL,
  action VARCHAR(64) NOT NULL,
  entity_type VARCHAR(64) NOT NULL,
  entity_key VARCHAR(128) NOT NULL,
  field_name VARCHAR(64) DEFAULT NULL,
  old_value_text MEDIUMTEXT,
  new_value_text MEDIUMTEXT,
  details_json LONGTEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY fm_audit_log_client_created_ix (client, created_at),
  KEY fm_audit_log_action_ix (action),
  CONSTRAINT fm_audit_log_user_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;