-- Add lesson position for sequencing
ALTER TABLE lessons ADD COLUMN position INT NULL;

-- Backfill position from chapter text (first number in chapter), fallback to id order per textbook
UPDATE lessons
SET position = CAST(REGEXP_SUBSTR(chapter, '[0-9]+') AS UNSIGNED)
WHERE chapter IS NOT NULL AND chapter REGEXP '[0-9]';

-- Fallback for any remaining NULL positions
SET @current_textbook := NULL;
SET @row_num := 0;
UPDATE lessons
SET position = (
  CASE
    WHEN @current_textbook = textbook_id THEN (@row_num := @row_num + 1)
    ELSE (@row_num := 1)
  END
)
WHERE position IS NULL
ORDER BY textbook_id, id;
SET @current_textbook := textbook_id;

-- Recurring series tables
CREATE TABLE recurring_series (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  teacher_id INT NOT NULL,
  duration_minutes INT NOT NULL,
  timezone VARCHAR(64) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  prebook_mode VARCHAR(32) NOT NULL DEFAULT 'next_4_weeks',
  auto_renew TINYINT(1) NOT NULL DEFAULT 1,
  textbook_id INT NULL,
  next_lesson_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_recurring_series_student (student_id),
  INDEX idx_recurring_series_teacher (teacher_id),
  INDEX idx_recurring_series_status (status)
);

CREATE TABLE recurring_slots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  series_id INT NOT NULL,
  day_of_week TINYINT NOT NULL,
  local_time VARCHAR(5) NOT NULL,
  INDEX idx_recurring_slots_series (series_id),
  INDEX idx_recurring_slots_day (day_of_week)
);

ALTER TABLE classes ADD COLUMN series_id INT NULL;
CREATE INDEX idx_classes_series ON classes(series_id);
