-- Create parent_children table to link parents with their children
CREATE TABLE IF NOT EXISTS parent_children (
  id INT AUTO_INCREMENT PRIMARY KEY,
  parent_user_id INT NOT NULL,
  child_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (child_id) REFERENCES children(id) ON DELETE CASCADE,
  UNIQUE KEY unique_parent_child (parent_user_id, child_id)
);

-- Add index for better performance
CREATE INDEX idx_parent_user_id ON parent_children(parent_user_id);
CREATE INDEX idx_child_id ON parent_children(child_id);