CREATE DATABASE IF NOT EXISTS confivoice
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE confivoice;

CREATE TABLE IF NOT EXISTS prediction_results (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  student_name VARCHAR(255) NOT NULL,
  student_gender VARCHAR(20),
  predicted_label VARCHAR(20),
  description VARCHAR(255),
  confidence DOUBLE NOT NULL DEFAULT 0,
  probability_pd DOUBLE NOT NULL DEFAULT 0,
  probability_tpd DOUBLE NOT NULL DEFAULT 0,
  is_valid_audio TINYINT(1) NOT NULL DEFAULT 1,
  error_message TEXT,
  audio_duration DOUBLE NOT NULL DEFAULT 0,
  volume_score DOUBLE NOT NULL DEFAULT 0,
  intonation_score DOUBLE NOT NULL DEFAULT 0,
  pause_score DOUBLE NOT NULL DEFAULT 0,
  speech_activity_ratio DOUBLE NOT NULL DEFAULT 0,
  silence_ratio DOUBLE NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(255) NOT NULL,
  username VARCHAR(120) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);