-- Migration 015: Customer salt level reminder
--
-- Per-customer salt level check reminder. Default interval is 90 days
-- (3 months); customers may switch to 30 or 180 days from the PWA.
-- next_due_date resets to (last check OR today) + interval every time the
-- customer taps "Salt checked" or changes their reminder interval.

CREATE TABLE IF NOT EXISTS salt_tracker (
  customer_id INT NOT NULL PRIMARY KEY,
  interval_days INT NOT NULL DEFAULT 90 COMMENT 'Reminder cadence: 30, 90, or 180 days',
  last_checked_date DATE DEFAULT NULL,
  next_due_date DATE DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_salt_tracker_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed a default reminder (due in 90 days) for existing customers
-- so the tracker is usable immediately without any prior check-in.
INSERT INTO salt_tracker (customer_id, interval_days, next_due_date)
SELECT c.customer_id, 90, DATE_ADD(CURDATE(), INTERVAL 90 DAY)
FROM customers c
ON DUPLICATE KEY UPDATE customer_id = salt_tracker.customer_id;