CREATE TABLE users
(
    id                  BIGSERIAL PRIMARY KEY,
    credit              BIGINT      DEFAULT 0           NOT NULL CHECK ( credit >= 0 ),
    last_checkin_day    DATE,
    checkin_streak      INT         DEFAULT 0           NOT NULL CHECK ( checkin_streak >= 0 )
);

CREATE TABLE credit_log
(
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT                    NOT NULL REFERENCES users (id),
    amount      INT                       NOT NULL,
    type        TEXT                      NOT NULL,
    description TEXT,
    created_at  timestamptz DEFAULT NOW() NOT NULL
);

-- name: CheckinUser :one
WITH target AS (
    SELECT id, last_checkin_day, checkin_streak
    FROM users
    WHERE users.id = $1
    FOR UPDATE
),
tz AS (
    SELECT (NOW() AT TIME ZONE 'Asia/Shanghai')::date AS today
),
abc (new_streak, id, applied) AS (
    SELECT id,
           (last_checkin_day IS NULL OR last_checkin_day <> (SELECT today FROM tz)) AS applied,
           CASE
               WHEN last_checkin_day = (SELECT today FROM tz) - INTERVAL '1 day' THEN checkin_streak + 1
               ELSE 1
               END AS new_streak
    FROM target
),
reward AS (
    SELECT id,
           applied,
           new_streak,
           CASE
               WHEN applied THEN $2 + ($2 * LEAST(GREATEST(new_streak - 1, 0), 5) / 5)
               ELSE 0
               END AS total_reward
    FROM abc 
),
updated AS (
    UPDATE users u
        SET last_checkin_day = CASE WHEN r.applied THEN (SELECT today FROM tz) ELSE u.last_checkin_day END,
            checkin_streak   = CASE WHEN r.applied THEN r.new_streak ELSE u.checkin_streak END,
            credit           = u.credit + r.total_reward
        FROM reward r
        WHERE u.id = r.id
        RETURNING r.applied, r.new_streak, r.total_reward, u.id, u.credit, u.last_checkin_day, u.checkin_streak
),
log AS (
    INSERT INTO credit_log (user_id, amount, type, description)
        SELECT $1, total_reward, 'checkin', $3
        FROM updated
        WHERE applied
        RETURNING id
)
SELECT applied::INT AS applied,
       new_streak,
       total_reward,
       id, credit, last_checkin_day, checkin_streak
FROM updated;