-- ============================================================
--  Break the Locks — Take Action v5 Schema Patch
--  Run once in phpMyAdmin on the admin_btlc database.
--
--  Adds AI-source tracking on the shared suggestion library so
--  AI-generated suggestions accumulate into a growing pool,
--  plus a per-user/per-lock flag that records when we already
--  auto-generated personalized suggestions for that combo so
--  we don't re-call OpenAI on every page load.
-- ============================================================

-- 1) Extend lock_action_suggestions
ALTER TABLE `lock_action_suggestions`
  ADD COLUMN `source` ENUM('seed','ai') NOT NULL DEFAULT 'seed' AFTER `active`,
  ADD COLUMN `created_by_user_id` INT NULL AFTER `source`,
  ADD INDEX `idx_lock_source` (`lock_id`, `source`);

-- 2) Per-user/per-lock AI generation flag
CREATE TABLE IF NOT EXISTS `user_lock_ai_state` (
  `user_id`      INT NOT NULL,
  `lock_id`      INT NOT NULL,
  `generated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_input_signature` VARCHAR(64) NULL,
    -- hash of habits+triggers we last generated against, so we can
    -- detect when the user has added new habits and offer a regenerate
  PRIMARY KEY (`user_id`, `lock_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;