-- ============================================================
--  Break the Locks — Life List Schema
--  Run this once in phpMyAdmin (or your MySQL client) on the
--  admin_btlc database before using life_list.php.
-- ============================================================

CREATE TABLE IF NOT EXISTS `life_list_items` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`      INT NOT NULL,
  `title`        VARCHAR(255) NOT NULL,
  `description`  TEXT NULL,

  -- where this item came from. "manual" by default; other sections can
  -- pass things like "alignment", "calling", "purpose", "lock", etc.
  `source`       VARCHAR(40)  NOT NULL DEFAULT 'manual',
  `source_ref`   VARCHAR(255) NULL,

  -- One-off vs. recurring:
  --   recurrence='none' → shows ONLY on item_date
  --   recurrence='daily' → shows every day between start_date..end_date
  `recurrence`   VARCHAR(20)  NOT NULL DEFAULT 'none',
  `item_date`    DATE NULL,
  `start_date`   DATE NULL,
  `end_date`     DATE NULL,

  `archived`     TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`   TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX `idx_user_date`   (`user_id`, `item_date`),
  INDEX `idx_user_recur`  (`user_id`, `recurrence`),
  INDEX `idx_user_active` (`user_id`, `archived`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `life_list_completions` (
  `id`             INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`        INT NOT NULL,
  `item_id`        INT NOT NULL,
  `completed_date` DATE NOT NULL,
  `completed_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY `uk_item_date` (`user_id`, `item_id`, `completed_date`),
  INDEX `idx_user_date` (`user_id`, `completed_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;