-- ============================================================
--  Break the Locks — 4K Meter Schema
--  Run once in phpMyAdmin on the admin_btlc database before
--  using meter.php.
-- ============================================================

CREATE TABLE IF NOT EXISTS `meter_assessments` (
  `id`               INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`          INT NOT NULL,

  -- 1-10 score for each of the Four Spiritual Desires
  `hope_score`       TINYINT UNSIGNED NOT NULL,
  `hope_notes`       TEXT NULL,
  `security_score`   TINYINT UNSIGNED NOT NULL,
  `security_notes`   TEXT NULL,
  `value_score`      TINYINT UNSIGNED NOT NULL,
  `value_notes`      TEXT NULL,
  `connection_score` TINYINT UNSIGNED NOT NULL,
  `connection_notes` TEXT NULL,

  -- AI-generated insights JSON for this specific assessment (lazy)
  `ai_insights`      TEXT NULL,

  `created_at`       TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX `idx_user_date` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;