# Database Schema: NexCore Apparel Management System

This schema is designed to handle a full garment manufacturing lifecycle, from material procurement to final shipping.

## Entity Relationship Summary

```mermaid
erDiagram
    BUYER ||--o{ ORDER : places
    BUYER ||--o{ PAYMENT : makes
    SUPPLIER ||--o{ INVENTORY_ITEM : supplies
    SUPPLIER ||--o{ PURCHASE_ORDER : receives
    PRODUCTION_LINE ||--o{ ORDER : processes
    PRODUCTION_LINE ||--o{ EMPLOYEE : staffed_by
    ORDER ||--o{ ORDER_HISTORY : tracks
```

## Tables and Columns

### 1. Buyers (`buyers`)
| Column | Type | Description |
| :--- | :--- | :--- |
| `id` | BigInt (PK) | Primary Key |
| `name` | String | Company Name (e.g., ZARA) |
| `country` | String | HQ Location |
| `contact_person` | String | Primary Contact |
| `email` | String | Contact Email |
| `phone` | String | Contact Phone |
| `brand` | String | Parent Brand |
| `status` | String | Active/Inactive |

### 2. Suppliers (`suppliers`)
| Column | Type | Description |
| :--- | :--- | :--- |
| `id` | BigInt (PK) | Primary Key |
| `name` | String | Supplier Name |
| `category` | String | Fabric, Accessory, Chemical, etc. |
| `contact_person` | String | Primary Contact |
| `email` | String | Contact Email |
| `lead_time` | Integer | Average delivery days |
| `rating` | Integer | 1-5 Scale |

### 3. Inventory Items (`inventory_items`)
| Column | Type | Description |
| :--- | :--- | :--- |
| `id` | BigInt (PK) | Primary Key |
| `sku` | String (Unique) | Stock Keeping Unit |
| `name` | String | Item Name |
| `category` | String | Grouping |
| `stock` | Decimal | Current quantity |
| `unit` | String | m, spools, pcs, etc. |
| `min_level` | Decimal | Threshold for "Low Stock" alert |
| `supplier_id` | BigInt (FK) | Link to `suppliers` |

### 4. Orders (`orders`)
| Column | Type | Description |
| :--- | :--- | :--- |
| `id` | BigInt (PK) | Primary Key |
| `order_no` | String (Unique) | Internal tracking number |
| `product_name` | String | e.g., Slim Fit Chinos |
| `buyer_id` | BigInt (FK) | Link to `buyers` |
| `qty` | Integer | Batch size |
| `current_stage` | String | Enum: HANDOVER, SEWING, QC, etc. |
| `progress` | Integer | 0-100 percentage |
| `deadline` | Date | Target completion |
| `production_line_id` | BigInt (FK) | Link to `production_lines` |

### 5. Production Lines (`production_lines`)
| Column | Type | Description |
| :--- | :--- | :--- |
| `id` | BigInt (PK) | Primary Key |
| `name` | String | Line Identity |
| `capacity` | Integer | Daily units max |
| `efficiency` | Integer | Performance percentage |
| `actual_output` | Integer | Real-time units produced |

### 6. Employees (`employees`)
| Column | Type | Description |
| :--- | :--- | :--- |
| `id` | BigInt (PK) | Primary Key |
| `emp_id` | String | Staff Identity Code |
| `name` | String | Full Name |
| `skills` | JSON | List of expertise |
| `production_line_id` | BigInt (FK) | Assigned Line |
| `avatar_url` | String | Profile Image path |

### 7. Payments & Audit
*   **Payments**: `invoice_no`, `amount`, `due_date`, `status` (Pending/Overdue/Paid).
*   **Audit Logs**: `user_name`, `action`, `details`, `timestamp`.

---

## SQL DDL (MySQL Compatible)

```sql
CREATE TABLE buyers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(255),
    contact_person VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(255),
    brand VARCHAR(255),
    status VARCHAR(50) DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE suppliers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(255),
    contact_person VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(255),
    lead_time INT DEFAULT 0,
    rating INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE production_lines (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    capacity INT DEFAULT 0,
    efficiency INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'Active',
    current_order_name VARCHAR(255),
    actual_output INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE inventory_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(255),
    stock DECIMAL(15,2) DEFAULT 0.00,
    unit VARCHAR(50),
    status VARCHAR(50) DEFAULT 'Active',
    supplier_id BIGINT,
    min_level DECIMAL(15,2) DEFAULT 0.00,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(255) UNIQUE NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    buyer_id BIGINT NOT NULL,
    qty INT DEFAULT 0,
    current_stage VARCHAR(100) DEFAULT 'HANDOVER',
    progress INT DEFAULT 0,
    deadline DATE,
    production_line_id BIGINT,
    completed_at TIMESTAMP NULL,
    FOREIGN KEY (buyer_id) REFERENCES buyers(id),
    FOREIGN KEY (production_line_id) REFERENCES production_lines(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```
