CREATE TABLE IF NOT EXISTS community_users (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  display_name VARCHAR(80) NOT NULL,
  university VARCHAR(120) NOT NULL,
  community_name VARCHAR(120) NOT NULL,
  can_create TINYINT(1) NOT NULL DEFAULT 0,
  can_review TINYINT(1) NOT NULL DEFAULT 0,
  is_admin TINYINT(1) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  KEY idx_community_users_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS access_tokens (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  user_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  token_prefix VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  label VARCHAR(80) NOT NULL DEFAULT 'Ana erişim',
  last_used_at DATETIME(3) NULL,
  expires_at DATETIME(3) NULL,
  revoked_at DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_access_tokens_hash (token_hash),
  KEY idx_access_tokens_user (user_id),
  KEY idx_access_tokens_active (revoked_at, expires_at),
  CONSTRAINT fk_access_tokens_user FOREIGN KEY (user_id) REFERENCES community_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS puzzles (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  creator_user_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  title VARCHAR(60) NOT NULL,
  proposed_difficulty ENUM('easy','medium','hard') NOT NULL,
  final_difficulty ENUM('easy','medium','hard') NULL,
  board_size SMALLINT UNSIGNED NOT NULL,
  stars_per_unit TINYINT UNSIGNED NOT NULL DEFAULT 1,
  regions_json LONGTEXT NOT NULL,
  solution_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  puzzle_fingerprint CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status ENUM('reviewing','published','rejected') NOT NULL DEFAULT 'reviewing',
  review_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  playable_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  unplayable_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  decision_reason VARCHAR(40) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  published_at DATETIME(3) NULL,
  rejected_at DATETIME(3) NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_puzzles_fingerprint (puzzle_fingerprint),
  KEY idx_puzzles_status_difficulty (status, final_difficulty, published_at),
  KEY idx_puzzles_creator (creator_user_id, created_at),
  KEY idx_puzzles_review_queue (status, review_count, created_at),
  CONSTRAINT fk_puzzles_creator FOREIGN KEY (creator_user_id) REFERENCES community_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS puzzle_reviews (
  id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  puzzle_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  reviewer_user_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  playability ENUM('playable','unplayable') NOT NULL,
  difficulty_vote ENUM('easy','medium','hard') NULL,
  comment VARCHAR(500) NULL,
  elapsed_seconds INT UNSIGNED NOT NULL DEFAULT 0,
  move_count INT UNSIGNED NOT NULL DEFAULT 0,
  solution_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_puzzle_reviewer (puzzle_id, reviewer_user_id),
  KEY idx_reviews_reviewer (reviewer_user_id, created_at),
  KEY idx_reviews_puzzle_created (puzzle_id, created_at),
  CONSTRAINT fk_reviews_puzzle FOREIGN KEY (puzzle_id) REFERENCES puzzles (id) ON DELETE CASCADE,
  CONSTRAINT fk_reviews_user FOREIGN KEY (reviewer_user_id) REFERENCES community_users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;