-- Migration 010: Add integer attempt counter for blockchain sealing.
--
-- Replaces the fragile regex-based attempt count derived from blockchain_error text.
-- blockchain_attempt_count: how many times sealing has been tried (0 = never attempted).
--
ALTER TABLE customers
  ADD COLUMN IF NOT EXISTS blockchain_attempt_count INTEGER NOT NULL DEFAULT 0;

-- Back-fill count from existing error text for orders already attempted.
UPDATE customers
  SET blockchain_attempt_count = (
    LENGTH(COALESCE(blockchain_error, '')) -
    LENGTH(REPLACE(COALESCE(blockchain_error, ''), 'attempt', ''))
  ) / 7
  WHERE blockchain_error IS NOT NULL AND blockchain_tx IS NULL;

