-- Daily checkin feature schema

CREATE TABLE IF NOT EXISTS daily_checkins (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    checkin_date DATE NOT NULL,
    day_in_streak INT NOT NULL DEFAULT 1,
    current_streak_start_date DATE NOT NULL,
    points_earned INT NOT NULL DEFAULT 0,
    reward_claimed BOOLEAN DEFAULT FALSE,
    claimed_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_daily_checkins_user_date (user_id, checkin_date),
    KEY idx_daily_checkins_user_streak (user_id, current_streak_start_date),
    KEY idx_daily_checkins_user_id (user_id),
    CONSTRAINT fk_daily_checkins_user FOREIGN KEY (user_id)
        REFERENCES subscribers(sId) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS daily_checkin_config (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feature_enabled BOOLEAN DEFAULT FALSE,
    points_per_checkin INT NOT NULL DEFAULT 10,
    reward_points_at_day INT NOT NULL DEFAULT 7,
    streak_break_days INT NOT NULL DEFAULT 1,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS daily_checkin_rewards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    checkin_id BIGINT UNSIGNED NOT NULL,
    points INT NOT NULL,
    reward_type ENUM('points', 'airtime', 'data') DEFAULT 'points',
    status ENUM('earned', 'used', 'pending') DEFAULT 'earned',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    used_at DATETIME NULL,
    KEY idx_daily_checkin_rewards_user (user_id),
    KEY idx_daily_checkin_rewards_status (status),
    CONSTRAINT fk_daily_checkin_rewards_user FOREIGN KEY (user_id)
        REFERENCES subscribers(sId) ON DELETE CASCADE,
    CONSTRAINT fk_daily_checkin_rewards_checkin FOREIGN KEY (checkin_id)
        REFERENCES daily_checkins(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default config
INSERT INTO daily_checkin_config (feature_enabled, points_per_checkin, reward_points_at_day, streak_break_days)
VALUES (TRUE, 10, 7, 1)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id);
