-- =============================================================================
-- Migration: 045_create_profile_auto_schedule_log.sql
-- Description: Creates a deduplication table that tracks which dates
--              auto-scheduled message generation has already run for each
--              profile, preventing double-firing on the same calendar day.
--
-- Composite PK (profile_id, generated_date) guarantees at most one log row
-- per profile per date; INSERT will simply fail (or be ignored with
-- INSERT IGNORE) if the scheduler fires more than once on the same day.
-- =============================================================================

CREATE TABLE IF NOT EXISTS profile_auto_schedule_log (
  profile_id     VARCHAR(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  generated_date DATE        NOT NULL,
  created_at     DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (profile_id, generated_date),

  CONSTRAINT fk_pasl_profile
    FOREIGN KEY (profile_id)
    REFERENCES profiles(id)
    ON DELETE CASCADE
);
