CREATE TABLE IF NOT EXISTS children (
  id INT AUTO_INCREMENT PRIMARY KEY,
  line_user_id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  grade_class VARCHAR(50) NULL,
  last_active_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_children_line_user_id (line_user_id),
  INDEX idx_children_last_active_at (last_active_at)
);

CREATE TABLE IF NOT EXISTS assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slot_id INT NOT NULL,
  child_id INT NOT NULL,
  parent_name VARCHAR(255) NOT NULL,
  brought_items VARCHAR(255) NULL,
  message TEXT NULL,
  remind_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_assignments_slot_id FOREIGN KEY (slot_id) REFERENCES slots(id) ON DELETE CASCADE,
  CONSTRAINT fk_assignments_child_id FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  UNIQUE KEY uq_assignments_slot_child (slot_id, child_id),
  INDEX idx_assignments_slot_id (slot_id),
  INDEX idx_assignments_child_id (child_id)
);