-- MailSyncScheduler previously only ever fetched each account's 20 most-recent messages
-- (no pagination) and hardcoded every imported Gmail/Outlook message's folder as INBOX
-- regardless of its real label — Sent history was never fetched at all, and if it had been,
-- it would have been mislabeled. Fixed in MailSyncScheduler.java by paginating through full
-- provider history and deriving the real folder from Gmail labelIds / Outlook isDraft.
-- This column tracks whether that one-time full-history backfill has completed for an
-- account; the existing `sync_cursor` column is reused to hold the in-progress pagination
-- token while backfill_completed_at is still null.
-- Idempotent (guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/add_mail_account_backfill_completed_at.sql

USE db_abos_v0.1;

SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = 'db_abos_v0.1' AND TABLE_NAME = 'dim_mail_account' AND COLUMN_NAME = 'backfill_completed_at'
);

SET @ddl = IF(@col_exists = 0,
  'ALTER TABLE dim_mail_account ADD COLUMN backfill_completed_at TIMESTAMP NULL AFTER sync_cursor',
  'SELECT 1'
);

PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;