Repository: JaniceHernandez/Product-Management-System
Author: JaniceHernandez
# S3-T01 — Reports API Functions
*Sprint 3 Developer Guide — Hope, Inc. Product Management System*
---
## 1. Task Overview
**Task ID:** S3-T01
**Task Name:** Reports API Functions
**Assigned To:** Janice Hernandez (M1 — Project Lead)
**PR Title:** feat/reports-api
**Deadline:** April 30, 2026
**Dependency:**
- #50 (`current_product_price` view), S3-T06 (`top_selling_products` view).
### Goal
Create `src/services/reportService.js` — the data-access layer for the two reports in the application. `getProductReport()` queries the `current_product_price` view to produce the REP_001 product listing with current price. `getTopSellingProducts()` queries the `top_selling_products` view to produce the REP_002 ranked list. Also create `src/services/userService.js` for the Admin Module: `getAllUsers()`, `activateUser()`, and `deactivateUser()` — used by `UserManagementPage` (S3-T04). All write operations use `makeStamp()` for audit consistency.
### User Stories Covered
- **US-18** — View the product listing report (REP_001) — `getProductReport()` powers this.
- **US-19** — SUPERADMIN views the top-selling report (REP_002) — `getTopSellingProducts()` powers this.
- **US-20** — Export product data — the service returns structured data that the report page converts to CSV.
- **US-21** — SUPERADMIN views all registered users — `getAllUsers()` powers the Admin page.
- **US-22** — SUPERADMIN activates newly registered users — `activateUser()`.
- **US-23** — SUPERADMIN deactivates user accounts — `deactivateUser()`.
- **US-24** — ADMIN views users in the system — `getAllUsers()` also used by ADMIN.
- **US-25** — System prevents ADMIN from modifying SUPERADMIN accounts — RLS from S3-T07 enforces; the service passes through whatever RLS permits.
- **US-29** — Stamp recorded on every write — `activateUser()` and `deactivateUser()` use `makeStamp()`.
### Expected Output
- `src/services/reportService.js` — `getProductReport()` and `getTopSellingProducts()`.
- `src/services/userService.js` — `getAllUsers()`, `activateUser()`, `deactivateUser()`.
- PR `feat/reports-api` reviewed, approved, and merged into `dev`.
---
## 2. Prerequisites
- **S2-T10 merged** — `current_product_price` view deployed and GRANT SELECT applied.
- **S3-T06 merged** — `top_selling_products` view deployed and GRANT SELECT applied.
- **S3-T07 merged** — Admin Module RLS policies protecting SUPERADMIN rows are active.
- **S2-T01 merged** — `src/utils/stampHelper.js` exists with `makeStamp()`.
- **S1-T18 merged** — `AuthContext` provides `currentUser.userid`.
---
## 3. Step-by-Step Development Guide
---
### Step 1 — Pull Latest `dev` and Create Feature Branch
```bash
git checkout dev
git pull origin dev
git checkout -b feat/reports-api
```
---
### Step 2 — Understand the Two Report Queries
**REP_001 — Product Report:**
Queries the `current_product_price` view (deployed in S2-T10). Returns all ACTIVE products with their latest unit price. Used for: a sortable/filterable product listing with prices, and a CSV export.
**REP_002 — Top Selling Products:**
Queries the `top_selling_products` view (deployed in S3-T06). Returns up to 10 ACTIVE products ranked by total quantity sold across all `salesDetail` transactions. Only accessible to users with `REP_002 = 1` (SUPERADMIN only per rights matrix).
**Admin Module — User Management:**
Queries `public.user` for all users visible to the current actor. SUPERADMIN and ADMIN can see all users. Activate and Deactivate operations update `record_status`. The RLS policy from S3-T07 prevents ADMIN from touching SUPERADMIN rows at the DB level — the service does not need to check this; it passes the operation through and the RLS either permits or rejects it.
---
### Step 3 — Create `src/services/reportService.js`
```js
// src/services/reportService.js
// Data-access layer for the Reports module.
// REP_001: getProductReport() — product listing with current price
// REP_002: getTopSellingProducts() — top 10 products by quantity sold
// Components import from here — never call supabase.from('view_name') directly.
import { supabase } from '../lib/supabaseClient';
// ── getProductReport ───────────────────────────────────────────
// US-18: Product listing report (REP_001).
// US-20: Returns structured data used for CSV export.
//
// Queries the current_product_price view (S2-T10) which returns:
// prodcode, description, unit, unitprice, effdate, record_status
// All rows are ACTIVE (view filters INACTIVE products).
//
// @param {object} options - Optional query modifiers
// @param {string} options.search - Filter by prodcode or description (client-side)
// @param {string} options.sortField - Column to sort by (default: 'prodcode')
// @param {string} options.sortDirection - 'asc' or 'desc' (default: 'asc')
// @returns {{ data: Array, error: object|null }}
export async function getProductReport({ search = '', sortField = 'prodcode', sortDirection = 'asc' } = {}) {
const { data, error } = await supabase
.from('current_product_price')
.select('prodcode, description, unit, unitprice, effdate')
.order(sortField, { ascending: sortDirection === 'asc' });
if (error) {
console.error('getProductReport error:', error.message);
return { data: [], error };
}
// Apply search filter in JS (view does not support ILIKE without PostgREST params)
const filtered = search
? (data ?? []).filter(row =>
row.prodcode.toLowerCase().includes(search.toLowerCase()) ||
row.description.toLowerCase().includes(search.toLowerCase())
)
: (data ?? []);
return { data: filtered, error: null };
}
// ── getTopSellingProducts ──────────────────────────────────────
// US-19: Top-selling products report (REP_002) — SUPERADMIN only.
//
// Queries the top_selling_products view (S3-T06) which returns:
// prodcode, description, unit, totalqty
// View is pre-sorted DESC by totalqty and limited to 10 rows.
// No additional filtering needed — the view handles everything.
//
// @returns {{ data: Array, error: object|null }}
export async function getTopSellingProducts() {
const { data, error } = await supabase
.from('top_selling_products')
.select('prodcode, description, unit, totalqty');
if (error) {
console.error('getTopSellingProducts error:', error.message);
return { data: [], error };
}
return { data: data ?? [], error: null };
}
```
---
### Step 4 — Create `src/services/userService.js`
```js
// src/services/userService.js
// Data-access layer for the Admin Module (User Management page).
// getAllUsers() — fetch all user rows for the management table
// activateUser() — set record_status = 'ACTIVE'
// deactivateUser() — set record_status = 'INACTIVE' (soft deactivation)
//
// RLS from S3-T07 enforces SUPERADMIN protection at the DB level:
// - ADMIN cannot update SUPERADMIN rows
// - SUPERADMIN has full access
// The service passes operations through without additional role checks.
import { supabase } from '../lib/supabaseClient';
import { makeStamp } from '../utils/stampHelper';
// ── getAllUsers ────────────────────────────────────────────────
// US-21, US-24: SUPERADMIN and ADMIN view all registered users.
//
// Returns all rows from public.user ordered by user_type then username.
// The RLS SELECT policy on public.user (USING true from migration 009)
// allows all authenticated users to read user rows.
// Stamp column included — visibility gated in the UI by user_type.
//
// @returns {{ data: Array, error: object|null }}
export async function getAllUsers() {
const { data, error } = await supabase
.from('user')
.select('userid, username, firstname, lastname, user_type, record_status, stamp')
.order('user_type')
.order('username');
if (error) {
console.error('getAllUsers error:', error.message);
return { data: [], error };
}
return { data: data ?? [], error: null };
}
// ── activateUser ───────────────────────────────────────────────
// US-22: SUPERADMIN activates a newly registered user.
//
// Sets record_status = 'ACTIVE'. After activation, the user can sign in.
// ADMIN attempting to activate a SUPERADMIN account will be blocked by
// the admin_update_record_status RLS policy (S3-T07) — the error is
// returned to the caller for display.
//
// @param {string} userId - The userid of the account to activate
// @param {string} actorId - currentUser.userid of the person performing the action
// @returns {{ error: object|null }}
export async function activateUser(userId, actorId) {
const stamp = makeStamp('ACTIVATED', actorId);
const { error } = await supabase
.from('user')
.update({ record_status: 'ACTIVE', stamp })
.eq('userid', userId);
if (error) {
console.error('activateUser error:', error.message);
return { error };
}
return { error: null };
}
// ── deactivateUser ─────────────────────────────────────────────
// US-23: SUPERADMIN deactivates a user account.
// US-25: System prevents ADMIN from deactivating SUPERADMIN accounts.
//
// Sets record_status = 'INACTIVE'. The deactivated user is immediately
// blocked by the login guard (AuthCallbackPage checks record_status).
//
// Note: This is a SOFT operation — record_status = 'INACTIVE', not DELETE.
// Per the project rule: no hard deletes. User rows are never removed.
//
// @param {string} userId - The userid of the account to deactivate
// @param {string} actorId - currentUser.userid of the person performing the action
// @returns {{ error: object|null }}
export async function deactivateUser(userId, actorId) {
const stamp = makeStamp('DEACTIVATED', actorId);
const { error } = await supabase
.from('user')
.update({ record_status: 'INACTIVE', stamp })
.eq('userid', userId);
if (error) {
console.error('deactivateUser error:', error.message);
return { error };
}
return { error: null };
}
```
---
### Step 5 — Smoke Test in Browser Console
Start the dev server and sign in as SUPERADMIN. Open DevTools → Console:
```js
// Test REP_001 — product report
const { getProductReport } = await import('/src/services/reportService.js');
const rep001 = await getProductReport();
console.log('REP_001 rows:', rep001.data?.length, rep001.error);
// Expected: rows returned (same as current_product_price view count)
// Test REP_002 — top selling
const { getTopSellingProducts } = await import('/src/services/reportService.js');
const rep002 = await getTopSellingProducts();
console.log('REP_002 rows:', rep002.data?.length, '(should be ≤ 10)', rep002.error);
console.log('REP_002 sorted:', rep002.data?.map(r => `${r.prodcode}: ${r.totalqty}`));
// Expected: ≤ 10 rows, sorted by totalqty DESC
// Test getAllUsers
const { getAllUsers } = await import('/src/services/userService.js');
const users = await getAllUsers();
console.log('Users:', users.data?.length, users.error);
// Expected: all user rows returned
```
---
### Step 6 — Commit and Push
```bash
git add src/services/reportService.js
git add src/services/userService.js
git commit -m "Add report service functions (REP_001, REP_002) and user management service"
git push -u origin feat/reports-api
```
---
### Step 7 — Open the Pull Request
- **Source branch:** `feat/reports-api`
- **Target branch:** `dev`
- **Title:** `feat/reports-api — REP_001 + REP_002 Supabase query functions`
**Description:**
```
## What changed
- src/services/reportService.js
getProductReport({ search, sortField, sortDirection })
Queries current_product_price view (S2-T10)
Returns: prodcode, description, unit, unitprice, effdate
Client-side search filter on prodcode + description
Server-side ORDER BY via Supabase .order()
getTopSellingProducts()
Queries top_selling_products view (S3-T06)
Returns: prodcode, description, unit, totalqty
View pre-sorted DESC, limited to 10
- src/services/userService.js
getAllUsers() — all users ordered by user_type, username
activateUser(userId, actorId) — record_status = ACTIVE + stamp
deactivateUser(userId, actorId) — record_status = INACTIVE + stamp
SUPERADMIN protection enforced by RLS (S3-T07) not by service
## How to test
1. Browser console: getProductReport() → rows returned (≤ current_product_price count)
2. Browser console: getTopSellingProducts() → ≤ 10 rows, sorted by totalqty DESC
3. Browser console: getAllUsers() → all user rows
4. getProductReport({ search: 'AK' }) → filtered rows only
```
Assign M2 (Marlan) as reviewer — `ProductReportPage` and `TopSellingPage` (S3-T03) import from these services.
---
### Step 8 — Respond to Review and Merge
```bash
git checkout dev
git pull origin dev
git branch -d feat/reports-api
```
Notify M2 — `reportService.js` and `userService.js` are ready for S3-T03 and S3-T04.
---
## 4. Potential Issues and Recommended Solutions
- **`getProductReport()` returns empty array.** The `current_product_price` view (S2-T10) may not be deployed or the GRANT SELECT was not applied. Confirm in Supabase Dashboard → Database → Views. Also confirm the authenticated role has SELECT.
- **`getTopSellingProducts()` returns empty.** The `top_selling_products` view (S3-T06) must be merged before this service works. Check the view exists in Supabase. Also verify `salesDetail` has data: `SELECT COUNT(*) FROM public.salesDetail;`
- **`getAllUsers()` returns empty even though users exist.** The SELECT policy on `public.user` uses `USING(true)` from migration 009. If this was dropped, re-add it. Also confirm the Supabase table name is `user` (not `users`).
- **`activateUser` or `deactivateUser` fails with RLS error for ADMIN targeting a SUPERADMIN.** This is the expected and correct behaviour — the `admin_update_record_status` policy (S3-T07) blocks it. The service returns `{ error }` which the UI layer (S3-T04) displays as a toast or inline message.
- **`getProductReport()` sort doesn't work on `unitprice`.** The `current_product_price` view column is `unitprice`. Supabase `.order('unitprice')` on a view works the same as a table — ensure the column name exactly matches.
---
## 5. Definition of Done (DoD)
S3-T01 is complete when ALL of the following are true:
- `src/services/reportService.js` exports `getProductReport` and `getTopSellingProducts`.
- `src/services/userService.js` exports `getAllUsers`, `activateUser`, and `deactivateUser`.
- `getProductReport()` returns rows from the `current_product_price` view with correct columns.
- `getProductReport({ search: 'AK' })` returns only matching rows.
- `getTopSellingProducts()` returns ≤ 10 rows ordered by `totalqty` DESC.
- `getAllUsers()` returns all `public.user` rows.
- `activateUser()` and `deactivateUser()` include `makeStamp()` in the UPDATE payload.
- No `.delete()` call exists in either service file.
- PR `feat/reports-api` raised against `dev`, approved by at least one member, and merged.
- Feature branch deleted from GitHub after merge.