CREATE TABLE IF NOT EXISTS phonemes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  symbol VARCHAR(20) NOT NULL,
  slug VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  category VARCHAR(80) NOT NULL,
  articulatory_instruction TEXT NOT NULL,
  mouth_image VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stimuli (
  id INT AUTO_INCREMENT PRIMARY KEY,
  phoneme_id INT NOT NULL,
  word VARCHAR(120) NOT NULL,
  position ENUM('inicial','medial','final','encontro') NOT NULL,
  syllable_level VARCHAR(30) DEFAULT NULL,
  phrase TEXT NOT NULL,
  short_text TEXT NOT NULL,
  image_path VARCHAR(255) NOT NULL,
  difficulty TINYINT NOT NULL DEFAULT 1,
  FOREIGN KEY (phoneme_id) REFERENCES phonemes(id) ON DELETE CASCADE,
  INDEX idx_stimuli_phoneme_position (phoneme_id, position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  type VARCHAR(60) NOT NULL,
  description TEXT NOT NULL,
  clinical_goal TEXT NOT NULL,
  instructions TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS session_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  patient_name VARCHAR(120) NOT NULL,
  phoneme_id INT NOT NULL,
  activity_type VARCHAR(60) NOT NULL,
  correct_count INT NOT NULL DEFAULT 0,
  approximate_count INT NOT NULL DEFAULT 0,
  incorrect_count INT NOT NULL DEFAULT 0,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (phoneme_id) REFERENCES phonemes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;