# Tools and Lambdas for Datafy

# Notes

## Supabase client initialization (Lambda)
- Older versions of `supabase-py` do not accept `ClientOptions(http_client=...)`.
- Use `create_client(url, key)` and, when needed, adjust database timeouts via SQL:
  - `supabase.postgrest.rpc("raw_sql", {"query": "SET statement_timeout = '0';"})`

## Supabase: clearing tables from Lambdas
- The Supabase Python client does **not** expose a `.truncate()` method on `table()` queries.
- To clear a table (e.g. `store_processing_queue`) from a Lambda, either:
  - Use a delete-all pattern with a filter, e.g. `supabase.table('store_processing_queue').delete().neq('id', 0).execute()`; or
  - Create a Postgres function that runs `TRUNCATE` server-side and call it via `supabase.rpc(...)`.


## Combined Weekly Report: cron-only email notifications
- The `CreateCombinedWeeklyReport` Lambda now sends a client email with the public report link **only** when invoked by a cron/EventBridge rule.
- To enable this, configure your scheduled event to include a flag in the payload (for example `{"customer_id": 123, "is_cron": true}`); normal API calls without this flag will generate the report but will not send an email.
- The email content includes the generated file URL in the body so recipients can click through directly to the report in the `Reports` bucket.

## Read Files: duplicate product inserts are benign
- The `insert_distinct_products` RPC can encounter duplicates when reprocessing weeks. We now ignore Postgres duplicate-key errors (code `23505`) for this RPC so the run proceeds to:
  - `batch_update_sjreport_product_id`
  - `update_sjreport_customer_ids`
  - `update_product_customer_ids`
- This prevents failing the entire import when a product already exists.

## Read Files: prevent client-side timeouts on long RPCs
- We increase the Supabase PostgREST HTTP read timeout to 300s during client init for `Read Files` so long operations (e.g., `update_sjreport_customer_ids`) do not hit `httpx.ReadTimeout`.
- We still disable server-side Postgres timeout per invocation with:
  - `supabase.postgrest.rpc("raw_sql", {"query": "SET statement_timeout = '0';"})`

## Read Files: RPC error handling and batch tuning
- Each post-processing RPC is wrapped in its own try/except so a failure won’t abort the entire import. Failures are logged, emailed to admin, and stored on the upload record.
- We call `batch_update_sjreport_product_id` with parameters to tune workload:
  - Loops until empty with `{"p_batch_size": 1000, "p_hours": 1}` (or `p_hours_back` where applicable)
- We also loop `update_sjreport_customer_ids` and `update_product_customer_ids` until empty with:
  - `{"p_batch_size": 1000, "p_hours_back": 1}`

## Fast iterative deploys with SAM (single function)
- `sam deploy` always updates the whole stack. For code-only changes to one Lambda, use `sam sync` to push just that function.

Example (Windows cmd, from repo root):

```bat
REM Build only one function (logical ID)
sam build ReadFiles --cached

REM One-time initial deploy (if the stack does not exist yet)
sam deploy  --guided

REM Fast code-only sync for a single function
sam sync --resource ReadFiles --code --profile datafy

REM Optional: watch for file changes and auto-sync
sam sync --watch --stack-name datafy-new -t template.yaml --resource ReadFiles --code
```

Notes:
- Replace `ReadFiles` with any logical ID from the template (e.g., `CreateTasks`, `ReadPromosKaching`, etc.).
- Use `--code` for handler/library changes. If you change infra (IAM, env vars, events), run a normal `sam deploy`.
- You can also target build to a single function: `sam build <LogicalId> -t <template.yaml>`.

## Shared Python Layer (smaller zips, faster syncs)
- Heavy/common deps are moved into a layer at `lambda/datafy_new/lambda_layer/`.
- The layer is defined in `lambda/datafy_new/template.yaml` as `CommonPythonLayer` and attached to all functions.
- Function-level `requirements.txt` no longer list: `requests`, `supabase`, `pandas`, `openpyxl`, `rapidfuzz`.

Build and publish the layer (one-time or when its requirements change):

```bat
cd lambda\datafy_new
sam build --use-container --clean
sam deploy --profile datafy
```

After the layer exists, you can iterate quickly on a single function:

```bat
sam sync --stack-name datafy-new -t template.yaml --resource-id ReadFiles --code --profile datafy
```

# Dump Remote

## PostGres
pg_dump -h aws-0-eu-central-1.pooler.supabase.com -p 6543 -d postgres -U postgres.natpyffafbqetnbeoctm -F c -b -v -f /tmp/remote_db_backup.dump

## Supabase - Schema
npx supabase db dump -f schema.sql
## Supabase - Data
npx supabase db dump --data-only -f schema_data.sql

# Restore local
## PostGres - Schema
psql -h localhost -p 54322 -U postgres -d postgres -a -f schema.sql 
## PostGres - Data
psql -h localhost -p 54322 -U postgres -d postgres -a -f schema_data.sql


# System importing
## SJ Report (Spinning Jenny Report)
## /file_processing/
### Callsheet / schedule is uploaded once a month and contains details of the reps and is added to table.
datafy_lambda\file_processing\1_parse_callsheet_files.js
### Once a week uploaded by client , this and runs code after the file is uploaded from frontend
datafy_lambda\file_processing\2_parse_sjreport_files.js
datafy_lambda\file_processing\signup_users.js

### After any of these files is uploaded task_parse script is run , to build tasks per store per user
datafy_lambda\file_processing\3_parse_task.js

### Store parse file is executed when uploaded
datafy_lambda\file_processing\parse_store_files.js

### Store products parse
datafy_lambda\file_processing\parse_store_product_files.js