-- Deployment 17: Add week_starting column to both KPI tables
-- Safe to re-run (uses IF NOT EXISTS equivalent via IGNORE)

ALTER TABLE `kpi_scheduling_entries`
  ADD COLUMN IF NOT EXISTS `week_starting` date DEFAULT NULL AFTER `week_ending`;

ALTER TABLE `cesar_kpi_entries`
  ADD COLUMN IF NOT EXISTS `week_starting` date DEFAULT NULL AFTER `week_ending`;

-- Back-fill: set week_starting = week_ending - 6 days for all existing rows
UPDATE `kpi_scheduling_entries` SET `week_starting` = DATE_SUB(`week_ending`, INTERVAL 6 DAY) WHERE `week_starting` IS NULL;
UPDATE `cesar_kpi_entries`      SET `week_starting` = DATE_SUB(`week_ending`, INTERVAL 6 DAY) WHERE `week_starting` IS NULL;
