CREATE TABLE IF NOT EXISTS reminders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
  title VARCHAR(190) NOT NULL,
  reminder_type ENUM('recurring','one_time') NOT NULL DEFAULT 'one_time',
  category VARCHAR(40) NOT NULL DEFAULT 'internal',
  event_date DATE NOT NULL,
  event_time TIME NULL,
  repeat_type ENUM('none','monthly') NOT NULL DEFAULT 'none',
  monthly_day TINYINT UNSIGNED NULL,
  reminder_offsets_json VARCHAR(255) NOT NULL DEFAULT '[0]',
  note TEXT NULL,
  status ENUM('active','done','skipped') NOT NULL DEFAULT 'active',
  priority ENUM('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  nominal_amount DECIMAL(15,2) NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_reminders_scope (tenant_id, location_id, status, event_date),
  KEY idx_reminders_category (tenant_id, location_id, category, reminder_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reminder_occurrence_states (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  reminder_id BIGINT UNSIGNED NOT NULL,
  occurrence_date DATE NOT NULL,
  status ENUM('done','skipped') NOT NULL,
  acted_by BIGINT UNSIGNED NULL,
  acted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_reminder_occurrence (reminder_id, occurrence_date),
  KEY idx_reminder_occurrence_status (occurrence_date, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;