# Schema Design: Unified Product Inventory and Pricing

## Standardized `products` Table

To unify the system, we will harmonize the production and test schemas. The primary key will be `UUID` and we will use the name `inventory` for stock levels.

```sql
-- Standardized Products Table
CREATE TABLE IF NOT EXISTS products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) NOT NULL DEFAULT 'EUR',
  inventory INTEGER NOT NULL DEFAULT 0,
  sku VARCHAR(100) UNIQUE NOT NULL,
  category_id UUID REFERENCES categories(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
```

## Inventory Management (Logic-level)

The `InventoryRepository` will manage the `inventory` field in the `products` table. 

**Core Operations:**
- `getStock(productId: string): Promise<number>`
- `updateStock(productId: string, quantity: number): Promise<void>` (Relative or Absolute)
- `checkAvailability(productId: string, requestedQuantity: number): Promise<boolean>`

## Pricing Management (Logic-level)

The `PricingRepository` will manage the `price` and `currency` fields in the `products` table.

**Core Operations:**
- `getPrice(productId: string): Promise<{price: number, currency: string}>`
- `updatePrice(productId: string, newPrice: number, currency?: string): Promise<void>`

## Future Expansion (Not in Scope but planned for)

The architecture allows for moving these fields to dedicated tables (e.g., `product_inventory`, `product_prices`) without changing the Repository interface or the Server Actions.

**Proposed Future Tables:**
- `product_inventory`: `id`, `product_id`, `warehouse_id`, `quantity`, `reserved_quantity`
- `product_prices`: `id`, `product_id`, `price`, `currency`, `valid_from`, `valid_to`, `is_sale`