-- =============================================
-- Migration: Atomic Usage Quota Check + Increment
--
-- Menggantikan pola read-check-write yang ada di setiap API route.
-- Fungsi ini dieksekusi dalam satu transaksi DB sehingga tidak ada
-- race condition antara concurrent requests.
--
-- Returns:
--   'ok'           → request diizinkan, usage sudah di-increment
--   'quota_exceeded' → usage_count sudah >= usage_limit
--   'reset'        → quota direset (bulan baru), request diizinkan
-- =============================================

CREATE OR REPLACE FUNCTION check_and_increment_usage(
  p_user_id UUID,
  p_amount  INTEGER DEFAULT 1
)
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  v_usage_count   INTEGER;
  v_usage_limit   INTEGER;
  v_usage_reset_at TIMESTAMPTZ;
  v_now           TIMESTAMPTZ := NOW();
BEGIN
  -- Lock baris agar concurrent request tidak race
  SELECT usage_count, usage_limit, usage_reset_at
    INTO v_usage_count, v_usage_limit, v_usage_reset_at
    FROM profiles
   WHERE id = p_user_id
     FOR UPDATE;

  IF NOT FOUND THEN
    RETURN 'not_found';
  END IF;

  -- Reset jika bulan sudah berganti
  IF v_now > v_usage_reset_at THEN
    UPDATE profiles
       SET usage_count    = p_amount,
           usage_reset_at = v_now + INTERVAL '30 days'
     WHERE id = p_user_id;
    RETURN 'reset';
  END IF;

  -- Cek kuota
  IF v_usage_count >= v_usage_limit THEN
    RETURN 'quota_exceeded';
  END IF;

  -- Increment atomic
  UPDATE profiles
     SET usage_count = usage_count + p_amount
   WHERE id = p_user_id;

  RETURN 'ok';
END;
$$;

COMMENT ON FUNCTION check_and_increment_usage IS
  'Atomic check-and-increment untuk usage quota. '
  'Gunakan ini di setiap API route generate/storyboard/ugc sebagai pengganti '
  'pola read-check-write yang rawan race condition.';
