Plataforma SaaS que ofrezca APIs y módulos integrados para reportes dinámicos y gestión de usuarios con control de acceso basado en roles y auditoría automática.

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.