schema.sql

-- Position Evaluator — MySQL schema
-- Run: mysql -u root -p < schema.sql

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

USE position_evaluator;

CREATE TABLE IF NOT EXISTS entries (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id        VARCHAR(50)  NOT NULL DEFAULT 'default_user',
  situation_text TEXT         NOT NULL,
  entry_date     DATE         NOT NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  situation_hash CHAR(64) GENERATED ALWAYS AS (SHA2(situation_text, 256)) STORED,
  UNIQUE KEY uq_user_date_hash (user_id, entry_date, situation_hash),
  INDEX idx_user_date (user_id, entry_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS diagnoses (
  id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entry_id          BIGINT UNSIGNED NOT NULL,
  classification    ENUM('zugzwang','fork','zwischenzug','prophylaxis',
                          'gambit','overextension','tempo_loss') NOT NULL,
  confidence_bucket ENUM('low','medium','high') NOT NULL,
  reasoning         VARCHAR(800) NOT NULL,
  model_version     VARCHAR(50)  NOT NULL,
  created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (entry_id) REFERENCES entries(id) ON DELETE CASCADE,
  INDEX idx_classification (classification)
) ENGINE=InnoDB;