-- CAS module tables (safe add-on; does not modify existing Monografía/IA flows)

CREATE TABLE IF NOT EXISTS cas_experiences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  academic_year_id INT NOT NULL,
  student_user_id INT NOT NULL,
  strand ENUM('C','A','S') NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  start_date DATE NULL,
  end_date DATE NULL,
  hours DECIMAL(6,2) NOT NULL DEFAULT 0,
  status ENUM('draft','in_review','approved','returned') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (academic_year_id) REFERENCES academic_years(id),
  FOREIGN KEY (student_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cas_reflections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  experience_id INT NOT NULL,
  student_user_id INT NOT NULL,
  reflection_text TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (experience_id) REFERENCES cas_experiences(id) ON DELETE CASCADE,
  FOREIGN KEY (student_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cas_files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  experience_id INT NOT NULL,
  uploaded_by_user_id INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NULL,
  file_size BIGINT NULL,
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (experience_id) REFERENCES cas_experiences(id) ON DELETE CASCADE,
  FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cas_reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  experience_id INT NOT NULL,
  reviewer_user_id INT NOT NULL,
  decision ENUM('approved','returned') NOT NULL,
  comments TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (experience_id) REFERENCES cas_experiences(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewer_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;