-- ============================================================
--  Break the Locks — Take Action v2 schema patch
--
--  Run once in phpMyAdmin AFTER you've already run:
--    life_list_schema.sql
--    lock_actions_schema.sql
--
--  What it does:
--    1) Adds `kind` (todo|idea) and `lock_id` columns to life_list_items
--       so we can store "ideas" the user wants to try later without
--       cluttering their daily todos.
--    2) Creates user_lock_reflections — the user's own words on
--       why each lock has held them back, and their commitment to break it.
-- ============================================================

-- ---- 1. Extend life_list_items ----
ALTER TABLE `life_list_items`
    ADD COLUMN `kind`    VARCHAR(10) NOT NULL DEFAULT 'todo' AFTER `source_ref`;

ALTER TABLE `life_list_items`
    ADD COLUMN `lock_id` INT NULL AFTER `kind`;

ALTER TABLE `life_list_items`
    ADD INDEX `idx_user_kind` (`user_id`, `kind`, `archived`);

-- If any of the ALTER statements above error with "Duplicate column name"
-- or "Duplicate key name", that's fine — it just means it was already
-- added on a previous run. Skip it and continue with the next.


-- ---- 2. Reflections table ----
CREATE TABLE IF NOT EXISTS `user_lock_reflections` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`         INT NOT NULL,
  `lock_id`         INT NOT NULL,
  `why_text`        TEXT NULL,
  `commitment_text` TEXT NULL,
  `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_user_lock` (`user_id`, `lock_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;