-- 1) Ampliar tasks con recurrencia diaria
ALTER TABLE tasks
  ADD COLUMN is_recurring TINYINT(1) NOT NULL DEFAULT 0 AFTER is_general,
  ADD COLUMN recur_freq ENUM('daily') NULL DEFAULT NULL AFTER is_recurring,
  ADD COLUMN start_date DATE NULL AFTER recur_freq,
  ADD COLUMN end_date   DATE NULL AFTER start_date;

-- Índices útiles
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='tasks' AND index_name='idx_active_recurring' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_active_recurring ON tasks (is_active, is_recurring, start_date, end_date)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 2) task_completions: registrar la ocurrencia por día para periódicas
ALTER TABLE task_completions
  ADD COLUMN occurrence_date DATE NULL AFTER period_id;

-- Índices/uniques para periódicas por persona y día
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='task_completions' AND index_name='uk_task_person_date' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE UNIQUE INDEX uk_task_person_date ON task_completions (task_id, person_id, occurrence_date)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='task_completions' AND index_name='idx_occurrence_date' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_occurrence_date ON task_completions (occurrence_date)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- El esquema inicial podía tener un UNIQUE (task_id, period_id) para “generales”.
-- Si existe con nombre uk_general_once, lo quitamos porque bloquearía las diarias dentro del mismo periodo.
SET @ix := (SELECT index_name FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='task_completions' AND index_name='uk_general_once' LIMIT 1);
SET @sql := IF(@ix IS NOT NULL, 'ALTER TABLE task_completions DROP INDEX uk_general_once', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 3) Ganador de tareas generales por DÍA (nuevo registro)
CREATE TABLE IF NOT EXISTS task_general_wins_daily (
  task_id INT NOT NULL,
  occurrence_date DATE NOT NULL,
  person_id INT NOT NULL,
  period_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_task_date (task_id, occurrence_date),
  INDEX idx_period (period_id),
  FOREIGN KEY (task_id)  REFERENCES tasks(id)    ON DELETE CASCADE,
  FOREIGN KEY (person_id) REFERENCES people(id)  ON DELETE CASCADE,
  FOREIGN KEY (period_id) REFERENCES periods(id) ON DELETE CASCADE
);
