-- Sādhana streak: one row per user per day on which they chanted.
--
-- A "day complete" (what the streak + the growing-plant animation are
-- built on) is the conjunction of two daily disciplines:
--   1. chanting today's target rounds   → recorded here (chant_done)
--   2. posting at least one quote today  → derived live from the posts
--      table (a post whose created_at falls on the same calendar day)
-- so this table only needs to capture the chanting half.
--
-- log_date is a DATE (not a timestamp) so the streak rolls over at UTC
-- midnight without any timezone math, matching how the rest of the app
-- buckets "today". chant_done is stored explicitly — decided at save time
-- as rounds_completed >= the user's target THEN — so later edits to a
-- user's daily_chanting_rounds never retroactively rewrite history.
CREATE TABLE IF NOT EXISTS sadhna_logs (
    id               UUID         PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id          UUID         NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    log_date         DATE         NOT NULL,
    rounds_completed INTEGER      NOT NULL DEFAULT 0,
    chant_done       BOOLEAN      NOT NULL DEFAULT FALSE,
    created_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at       TIMESTAMPTZ  NOT NULL DEFAULT now(),
    UNIQUE (user_id, log_date),
    CHECK (rounds_completed >= 0 AND rounds_completed <= 1008)
);

-- The streak/growth computation walks a user's recent days newest-first;
-- this serves both that scan and the "today" lookup.
CREATE INDEX IF NOT EXISTS sadhna_logs_user_date_idx
    ON sadhna_logs (user_id, log_date DESC);