# Analysis: Current Inventory, Pricing, and Script Systems

## Current State

### Database Schema Inconsistencies
- **Mixed ID Types:** `SERIAL` (Integer) is used in `setup-complete-schema.ts` and `setup-db.ts`, while `UUID` is used in `tests/setup/database.ts`.
- **Mixed Field Names:** `stock` vs `inventory`.
- **Missing Definitions:** Advanced tables (`manufacturers`, `suppliers`, `tax_classes`, `brands`, `product_groups`) are referenced in `seed-data.ts` but are not defined in any of the primary setup scripts.
- **Fragmented SQL:** Core features (Chat, Blackboard, Notifications) are defined in separate `.sql` files, leading to manual execution or fragmented setup logic.

### Script Folder Fragmentation
The `scripts/` folder contains 14 files, many of which are redundant or temporary:
- **Setup Scripts:** `setup-db.ts`, `setup-complete-schema.ts`, `docker-setup.ts` all attempt to initialize the database with different logic.
- **Incremental SQL:** `add-system-logs-table.ts`, `setup-blackboard.sql`, etc., are one-off migrations that should be part of the baseline schema.
- **Shell Scripts:** `setup-chat.sh` is redundant if logic is in TS.

## Proposed Unification and Refactoring Strategy

### 1. Consolidate Schema Setup
- Create a single, definitive `scripts/setup-database.ts`.
- This script will contain the **Baseline Schema** (Unified across Prod and Test).
- **Baseline Schema Features:**
    - Use `UUID` for all primary keys (`gen_random_uuid()`).
    - Standardize on `inventory` instead of `stock`.
    - Include all tables: `users`, `customers`, `customer_addresses`, `manufacturers`, `suppliers`, `tax_classes`, `eu_responsible_persons`, `brands`, `categories`, `product_groups`, `products`, `orders`, `order_items`, `team_blackboard`, `team_presence`, `team_chat_messages`, `entity_mentions`, `edit_locks`, `notifications`, `system_logs`.

### 2. Refactor Seeding
- Update `scripts/seed-data.ts` to align with the new UUID-based baseline schema.
- Ensure seeding covers all unified fields for inventory and pricing.

### 3. Streamline Orchestration
- Update `scripts/docker-setup.ts` to orchestrate the containers and call the new `setup-database.ts`.
- Reduce the main scripts to:
    1. `scripts/setup-database.ts` (Schema & Baseline)
    2. `scripts/seed-data.ts` (Optional test data)
    3. `scripts/docker-setup.ts` (One-click environment setup)

### 4. Cleanup
- Delete all redundant `.ts`, `.sql`, and `.sh` files in the `scripts/` folder.
- Harmonize `tests/setup/database.ts` to import from the same schema definition if possible, or at least mirror the Baseline Schema exactly.