CREATE TABLE IF NOT EXISTS order_status_history (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(40) NULL,
  to_status VARCHAR(40) NOT NULL,
  changed_by BIGINT UNSIGNED NULL,
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_order_history_order
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_history_user
    FOREIGN KEY (changed_by) REFERENCES users(id),
  INDEX idx_order_history (order_id, changed_at)
);

INSERT INTO order_status_history (order_id, from_status, to_status)
SELECT o.id, NULL, o.status
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM order_status_history h WHERE h.order_id = o.id
);

CREATE TABLE IF NOT EXISTS deleted_external_orders (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  integration VARCHAR(60) NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  order_number VARCHAR(40) NULL,
  deleted_by BIGINT UNSIGNED NULL,
  deleted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_deleted_order_user FOREIGN KEY (deleted_by) REFERENCES users(id),
  UNIQUE KEY uq_deleted_external_order (integration, external_id)
);

CREATE TABLE IF NOT EXISTS document_sequences (
  document_type VARCHAR(40) PRIMARY KEY,
  next_value BIGINT UNSIGNED NOT NULL
);

INSERT INTO document_sequences (document_type, next_value)
SELECT 'order', COALESCE(MAX(id), 0) + 1 FROM orders
ON DUPLICATE KEY UPDATE next_value = GREATEST(next_value, VALUES(next_value));

INSERT INTO document_sequences (document_type, next_value)
SELECT 'invoice', COALESCE(MAX(id), 0) + 1 FROM invoices
ON DUPLICATE KEY UPDATE next_value = GREATEST(next_value, VALUES(next_value));