# B2B Garments Marketplace - Unified Database Setup Guide

This guide details the **Unified Database Architecture (`unified_database.sql`)**, which consolidates both the Admin Section and User/Merchant Section schemas into a single relational database with cross-table foreign key constraints, indexes, and sample seed data.

---

## 1. Unified Database Overview

- **Database Name**: `b2b_marketplace`
- **SQL Script**: `/unified_database.sql`
- **Central PHP Config**: `/config/db.php`
- **Module Configs**: `/config/db_admin.php` and `/config/db_user.php`

### Consolidated Table Architecture & Relationships:

| Table Name | Description | Relationships & Foreign Keys |
| :--- | :--- | :--- |
| `users` | Buyer, Supplier, and User accounts | Primary authentication store |
| `admin_users` | Platform Administrators, Managers & Staff | Primary admin store |
| `factories` | Registered garments manufacturing facilities | `fk_factories_user` -> `users(id)` |
| `products` | Factory showcase products & garments | `fk_products_factory` -> `factories(id)` |
| `sourcing_orders` | Buyer RFQ sourcing jobs & work orders | `fk_sourcing_orders_user` -> `users(id)` |
| `order_bids` | Supplier quotations & proposals on RFQs | `fk_order_bids_order` -> `sourcing_orders(id)`<br>`fk_order_bids_supplier` -> `users(id)` |
| `capacity_posts` | Available production capacity slots | `fk_capacity_user` -> `users(id)`<br>`fk_capacity_factory` -> `factories(id)` |
| `capacity_quotes` | Buyer bookings on empty capacity slots | `fk_quotes_post` -> `capacity_posts(id)`<br>`fk_quotes_buyer` -> `users(id)` |
| `factory_approvals` | Factory compliance & verification queue | `fk_approvals_factory` -> `factories(id)`<br>`fk_approvals_owner` -> `users(id)`<br>`fk_approvals_admin` -> `admin_users(id)` |
| `order_analysis_audits` | Financial audits & tracking records | `fk_audits_buyer` -> `users(id)`<br>`fk_audits_supplier` -> `users(id)`<br>`fk_audits_admin` -> `admin_users(id)` |
| `system_audit_logs` | Administrative security & activity logs | `fk_logs_admin` -> `admin_users(id)` |
| `admin_notifications` | Alerts and notifications for Admin team | `fk_notif_admin` -> `admin_users(id)` |
| `master_categories_departments` | Categories, departments & designations | Self-contained lookup table |
| `user_messages` | Direct buyer-to-supplier messaging | `fk_msg_sender` -> `users(id)`<br>`fk_msg_receiver` -> `users(id)` |
| `user_notifications` | Alerts and notifications for Users | `fk_user_notif` -> `users(id)` |

---

## 2. How to Run & Import in XAMPP / phpMyAdmin / MySQL

### Option A: Via phpMyAdmin (Recommended for XAMPP)
1. Start **Apache** and **MySQL** in your XAMPP Control Panel.
2. Open your browser and navigate to: `http://localhost/phpmyadmin`
3. Click on the **Import** tab at the top.
4. Click **Choose File** and select `unified_database.sql` from your project folder.
5. Click **Import** (or **Go**).
6. The database `b2b_marketplace` will be created with all 15 relational tables and sample seed records.

### Option B: Via MySQL Command Line
Run the following single command in your terminal / command prompt:
```bash
mysql -u root -p < unified_database.sql
```
*(If your root user has no password, simply press Enter when prompted).*

### Option C: Via MySQL Workbench or DBeaver
1. Connect to your local MySQL instance (`localhost:3306`).
2. Open `unified_database.sql` in the SQL Query Editor.
3. Click **Execute All / Run** (⚡).

---

## 3. Database Connection Configuration

Both Admin and User/Merchant modules now connect to the same central database credentials:

### Environment Variables (`.env`):
```env
DB_HOST=localhost
DB_PORT=3306
DB_NAME=b2b_marketplace
DB_USER=root
DB_PASS=
```

### PHP PDO Connection Example:
```php
require_once __DIR__ . '/config/db.php';
$pdo = Database::getConnection();

// Query any table across the entire system
$stmt = $pdo->query("SELECT * FROM sourcing_orders WHERE is_closed = 0");
$orders = $stmt->fetchAll();
```
