CREATE DATABASE IF NOT EXISTS telc_certificates;
USE telc_certificates;

CREATE TABLE IF NOT EXISTS certificates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  public_id VARCHAR(64) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  subtitle VARCHAR(255) NOT NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  date_of_birth DATE NOT NULL,
  place_of_birth VARCHAR(120) NOT NULL,
  points_leseverstehen INT NOT NULL,
  points_sprachbausteine INT NOT NULL,
  points_hoerverstehen INT NOT NULL,
  points_schriftlicher_ausdruck INT NOT NULL,
  points_kontaktaufnahme INT NOT NULL,
  points_gespraech_thema INT NOT NULL,
  points_gemeinsame_aufgabe INT NOT NULL,
  total_points INT NOT NULL,
  grade VARCHAR(80) NOT NULL,
  exam_date DATE NOT NULL,
  participant_number VARCHAR(80) NOT NULL,
  issue_date DATE NOT NULL,
  exam_center VARCHAR(180) NOT NULL,
  certificate_url TEXT NOT NULL,
  qr_code_data MEDIUMTEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);