CREATE TABLE IF NOT EXISTS contents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  level ENUM('section','subcategory') NOT NULL,
  section_label VARCHAR(50) NULL,
  parent_id BIGINT UNSIGNED NULL,
  page_marker VARCHAR(50) NULL,
  code_hint VARCHAR(50) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_parent (parent_id),
  INDEX idx_level (level),
  CONSTRAINT fk_contents_parent FOREIGN KEY (parent_id) REFERENCES contents(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE medical_codes
  ADD COLUMN content_id BIGINT UNSIGNED NULL AFTER source_file,
  ADD INDEX idx_content_id (content_id),
  ADD CONSTRAINT fk_medical_codes_content FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE SET NULL;
