-- Gratitude journal: 3 spiritual gratitudes per day, streak tracking
CREATE TABLE IF NOT EXISTS gratitude_entries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    entry_date DATE NOT NULL,
    gratitude_1 TEXT NOT NULL,
    gratitude_2 TEXT,
    gratitude_3 TEXT,
    dimension_hope TINYINT(1) NOT NULL DEFAULT 0,
    dimension_security TINYINT(1) NOT NULL DEFAULT 0,
    dimension_value TINYINT(1) NOT NULL DEFAULT 0,
    dimension_connection TINYINT(1) NOT NULL DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_grat_user_date (user_id, entry_date),
    KEY idx_grat_user (user_id),
    KEY idx_grat_date (entry_date)
);

ALTER TABLE users
    ADD COLUMN gratitude_streak INT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN gratitude_streak_best INT UNSIGNED NOT NULL DEFAULT 0,
    ADD COLUMN gratitude_last_date DATE DEFAULT NULL;