## OMNI Database Design Document
**Purpose:**
The purpose of this Database Design Document is to provide a comprehensive description of the data requirements, data models, and database implementation details for the OmniSync. This document serves as a blueprint for the design and development of the database, ensuring that it meets the functional and non-functional requirements of the system.

**Data Requirements:**
This section outlines the tables required to support the business processes and functionalities of the OmniSync. Each table is described in terms of its purpose, attributes/columns, and relationships with other entities.

**1. Addresses**
- **Description**: Stores address information associated with customers or other entities (SH and SP , Ship to Party and Sold to Party) within the system.
- **Attributes**:
Here are the attributes for the "Addresses" entity/table in a table format:

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| address_id | uuid | Unique identifier for the address (primary key). |
| name | string | Name or label associated with the address. |
| first_name | string | First name of the recipient. |
| last_name | string | Last name of the recipient. |
| address1 | text | First line of the street address. |
| address2 | text | Second line of the street address (nullable). |
| city | string | City of the address. |
| state | string | State or province of the address. |
| zip_code | string | ZIP or postal code of the address. |
| phone | string | Phone number associated with the address (nullable). |
| customer_id | uuid | Identifier of the customer associated with this address (foreign key, nullable). |
| country | string | Country of the address. |
| sap_id | string | SAP ID associated with the address, if applicable (nullable). |
| sap_ref | string | SAP reference, if applicable (nullable). |
| kunn2 | string | KUNN2 value, if applicable (nullable). |
| partner_fun | string | Partner function, if applicable (nullable). |
| is_active | boolean | Indicates if the address is currently active or inactive (default: true). |
| created_at | timestamp | Timestamp when the address record was created. |
| updated_at | timestamp | Timestamp when the address record was last updated. |

This table format provides a clear overview of the attributes, their data types, and descriptions, making it easier to review and understand the structure of the "Addresses" entity/table.

**Notes**:
- The `address_id` column is the primary key for the table, ensuring unique identification of each address record.
- The `customer_id` column is a nullable foreign key referencing the `customers` table, allowing addresses to be associated with customers or used for other purposes (e.g., supplier addresses).
- The `sap_id`, `sap_ref`, `kunn2`, and `partner_fun` columns are included to accommodate integration with SAP systems, if necessary.
- The `is_active` column allows for tracking active and inactive addresses, facilitating data maintenance and historical tracking.
- The `created_at` and `updated_at` columns provide audit trails for record creation and modification times.

**2. Batches**
- **Description**: Represents batches of data with associated customer and batch type. It is mainly used to store the background job details into the database.
- **Attributes**:
Here are the attributes for the "Batches" entity/table in a table format:

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the batch (primary key). |
| customer_id | uuid | Identifier of the customer associated with this batch (foreign key referencing the `id` column in the `customers` table). |
| batch_type | integer | Type of the batch (`0` for `export_product_file`, `1` for `sync`). |
| status | integer | Status of the batch (`0` for `pending`, `1` for `running`, `2` for `success`, `3` for `failed`). |
| data | jsonb | JSON data associated with the batch. |
| remarks | text | Remarks about the batch. |
| file | string | File associated with the batch. |
| created_at | timestamp | Timestamp when the batch record was created. |
| updated_at | timestamp | Timestamp when the batch record was last updated. |

**Batch Types**:
- `0` for `export_product_file`
- `1` for `sync`

**Batch Statuses**:
- `0` for `pending`
- `1` for `running`
- `2` for `success`
- `3` for `failed`

**Relationship**:
- The `customer_id` column serves as a foreign key referencing the `id` column in the `customers` table.
- This establishes a one-to-many relationship between `customers` and `batches`, where a customer can have multiple batches, and each batch belongs to a single customer.

**3. Carts**
- **Description**: Stores information about shopping carts associated with customers.
- **Attributes**:
Here are the attributes for the "Batches" entity/table in a table format:

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the cart (primary key). |
| order_type | string | Type of the order. |
| fd_region_id | uuid | Identifier of the region associated with this cart (foreign key). |
| shipping_address_id | uuid | Identifier of the shipping address associated with this cart (foreign key). |
| customer_id | uuid | Identifier of the customer associated with this cart (foreign key referencing the `id` column in the `customers` table). |
| fd_origin_id | uuid | Identifier of the origin associated with this cart (foreign key referencing the `id` column in the `fd_origins` table). |
| created_at | timestamp | Timestamp when the cart record was created. |
| updated_at | timestamp | Timestamp when the cart record was last updated. |

**Relationships**:
- The `customer_id` column serves as a foreign key referencing the `id` column in the `customers` table. This establishes a one-to-one relationship between `customers` and `carts`, where a customer has one cart, and a cart belongs to one customer.
- The `fd_origin_id` column serves as a foreign key referencing the `id` column in the `fd_origins` table. This establishes a one-to-many relationship between `fd_origins` and `carts`, where an origin can have many carts.

**4. Comments**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the comment (primary key). |
| content | text | Content of the comment. |
| order_id | uuid | Identifier of the order associated with this comment (foreign key referencing the `id` column in the `orders` table). |
| customer_id | uuid | Identifier of the customer who made the comment (foreign key referencing the `id` column in the `customers` table). |
| created_at | timestamp | Timestamp when the comment record was created. |
| updated_at | timestamp | Timestamp when the comment record was last updated. |

**Relationships**:
- The `customer_id` column serves as a foreign key referencing the `id` column in the `customers` table. This establishes a one-to-many relationship between `customers` and `comments`, where a customer can have many comments, and each comment belongs to a single customer.
- The `order_id` column serves as a foreign key referencing the `id` column in the `orders` table. This establishes a one-to-many relationship between `orders` and `comments`, where an order can have many comments, and each comment belongs to a single order.

**5. Customers**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the customer (primary key). |
| email | string | Email address of the customer. |
| encrypted_password | string | Encrypted password of the customer. |
| reset_password_token | string | Token used for resetting the password. |
| reset_password_sent_at | datetime | Timestamp when the reset password token was sent. |
| remember_created_at | datetime | Timestamp for remembering the sign-in. |
| sign_in_count | integer | Number of sign-ins. |
| current_sign_in_at | datetime | Timestamp of the current sign-in. |
| last_sign_in_at | datetime | Timestamp of the last sign-in. |
| current_sign_in_ip | inet | IP address of the current sign-in. |
| last_sign_in_ip | inet | IP address of the last sign-in. |
| role_id | uuid | Identifier of the role associated with this customer (foreign key referencing the `id` column in the `roles` table). |
| authentication_token | string | Token used for authentication. |
| region | integer | Region associated with the customer. |
| fd_region_id | uuid | Identifier of the region associated with this customer (foreign key referencing the `id` column in the `fd_regions` table). |
| status | integer | Status of the customer. |
| sap_id | string | SAP ID associated with the customer. |
| name | string | Name of the customer. |
| phone | string | Phone number of the customer. |
| fd_origin_id | uuid | Identifier of the origin associated with this customer (foreign key referencing the `id` column in the `fd_origins` table). |
| region_1 | string | Region information. |
| inco1 | string | Inco1 information. |
| zterm | string | Zterm information. |
| zterm_txt | string | Zterm text. |
| country | string | Country of the customer. |
| inco1_txt | string | Inco1 text. |
| kdgrp | string | KDGRP value. |
| kdgrp_txt | string | KDGRP text. |
| permissions | string[] | Permissions associated with the customer. |
| payment_term | string | Payment term associated with the customer. |
| created_at | timestamp | Timestamp when the customer record was created. |
| updated_at | timestamp | Timestamp when the customer record was last updated. |

**Relationships**:
- The `role_id` column serves as a foreign key referencing the `id` column in the `roles` table. This establishes a one-to-many relationship between `roles` and `customers`, where a role can have many customers, and each customer belongs to a single role.
- The `fd_origin_id` column serves as a foreign key referencing the `id` column in the `fd_origins` table. This establishes a one-to-one relationship between `customers` and `fd_origins`, where a customer has one origin, and an origin belongs to one customer.
- The `fd_region_id` column serves as a foreign key referencing the `id` column in the `fd_regions` table. This establishes a one-to-one relationship between `customers` and `fd_regions`, where a customer has one region, and a region belongs to one customer.

**6. FdOrigins**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the origin (primary key). |
| name | string | Name of the origin. |
| status | integer | Status of the origin. |
| customer_id | uuid | Identifier of the customer associated with this origin (foreign key referencing the `id` column in the `customers` table). |
| created_at | timestamp | Timestamp when the origin record was created. |
| updated_at | timestamp | Timestamp when the origin record was last updated. |

**Relationship**:
- The `customer_id` column serves as a foreign key referencing the `id` column in the `customers` table. This establishes a one-to-one relationship between `customers` and `fd_origins`, where a customer has one origin, and an origin belongs to one customer.

**7. FdRegions**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the region (primary key). |
| name | string | Name of the region. |
| status | integer | Status of the region. |
| created_at | timestamp | Timestamp when the region record was created. |
| updated_at | timestamp | Timestamp when the region record was last updated. |

**8. Free Demo Inquiries**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the inquiry (primary key). |
| name | string | Name of the person making the inquiry. |
| work_email | string | Work email of the person making the inquiry. |
| phone_no | string | Phone number of the person making the inquiry. |
| company_name | string | Name of the company. |
| message | text | Message associated with the inquiry. |
| created_at | timestamp | Timestamp when the inquiry record was created. |
| updated_at | timestamp | Timestamp when the inquiry record was last updated. |

**9. Images**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the image (primary key). |
| file | string | Filename of the image. |
| created_at | timestamp | Timestamp when the image record was created. |
| updated_at | timestamp | Timestamp when the image record was last updated. |

**10. Images Products**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the association (primary key). |
| product_id | uuid | Identifier of the product (foreign key referencing the `id` column in the `products` table). |
| image_id | uuid | Identifier of the image (foreign key referencing the `id` column in the `images` table). |
| created_at | timestamp | Timestamp when the association record was created. |
| updated_at | timestamp | Timestamp when the association record was last updated. |

**Relationships**:
- The `product_id` column serves as a foreign key referencing the `id` column in the `products` table. This establishes a many-to-many relationship between `products` and `images`, where a product can have many images, and an image can belong to many products.
- The `image_id` column serves as a foreign key referencing the `id` column in the `images` table. This establishes a many-to-many relationship between `images` and `products`, where an image can belong to many products, and a product can have many images.

**11. Line Items**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the line item (primary key). |
| quantity | integer | Quantity of the product. |
| product_id | uuid | Identifier of the associated product (foreign key referencing the `id` column in the `products` table). |
| order_id | uuid | Identifier of the associated order (foreign key referencing the `id` column in the `orders` table). |
| cart_id | uuid | Identifier of the associated cart (foreign key referencing the `id` column in the `carts` table). |
| posex | string | Position index of the line item. |
| sales_nav | json | JSON data for sales navigation. |
| unit_price | decimal | Price per unit of the product. |
| rim | string | Rim specification of the product. |
| li | string | Load index of the product. |
| sr | string | Speed rating of the product. |
| lr | string | Load range of the product. |
| size | string | Size of the product. |
| product_sku | string | SKU (Stock Keeping Unit) of the product. |
| created_at | timestamp | Timestamp when the line item record was created. |
| updated_at | timestamp | Timestamp when the line item record was last updated. |

**Relationships**:
- The `product_id` column serves as a foreign key referencing the `id` column in the `products` table. This establishes a one-to-many relationship between `products` and `line_items`, where a product can have many line items, and a line item belongs to one product.
- The `order_id` column serves as a foreign key referencing the `id` column in the `orders` table. This establishes a one-to-many relationship between `orders` and `line_items`, where an order can have many line items, and a line item belongs to one order.
- The `cart_id` column serves as a foreign key referencing the `id` column in the `carts` table. This establishes a one-to-many relationship between `carts` and `line_items`, where a cart can have many line items, and a line item belongs to one cart.

**12. Marketings**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the marketing document (primary key). |
| file | string | Filename of the marketing document. |
| doc_type | integer | Type of the document. |
| fd_origin_id | uuid | Identifier of the associated origin (foreign key referencing the `id` column in the `fd_origins` table). |
| category | integer | Category of the marketing document. |
| fd_region_id | uuid | Identifier of the associated region (foreign key referencing the `id` column in the `fd_regions` table). |
| kdgrp | string | Group key. |
| created_at | timestamp | Timestamp when the marketing record was created. |
| updated_at | timestamp | Timestamp when the marketing record was last updated. |

**Relationships**:
- The `fd_origin_id` column serves as a foreign key referencing the `id` column in the `fd_origins` table. This establishes a one-to-many relationship between `fd_origins` and `marketings`, where an origin can have many marketing documents, and a marketing document belongs to one origin.
- The `fd_region_id` column serves as a foreign key referencing the `id` column in the `fd_regions` table. This establishes a one-to-many relationship between `fd_regions` and `marketings`, where a region can have many marketing documents, and a marketing document belongs to one region.

**13. Oauth Clients**
- **Description**: Stores OAuth clients for authentication.

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the OAuth client (primary key). |
| access_token | string | Access token for authentication. |
| provider | string | Provider of the OAuth client. |
| created_at | timestamp | Timestamp when the OAuth client record was created. |
| updated_at | timestamp | Timestamp when the OAuth client record was last updated. |

**14. Omni Notifications**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the notification (primary key). |
| message | text | Content of the notification message. |
| customer_id | uuid | Identifier of the associated customer (foreign key referencing the `id` column in the `customers` table). |
| status | boolean | Status of the notification. |
| created_at | timestamp | Timestamp when the notification record was created. |
| updated_at | timestamp | Timestamp when the notification record was last updated. |

**Relationship**:
- The `customer_id` column serves as a foreign key referencing the `id` column in the `customers` table. This establishes a one-to-many relationship between `customers` and `omni_notifications`, where a customer can have many notifications, and a notification belongs to one customer.

**15. Onboarding Clients**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the onboarding client (primary key). |
| legal_business_name | string | The legal name of the client's business. |
| address | text | The address of the client's business. |
| city | string | The city where the client's business is located. |
| state | string | The state where the client's business is located. |
| zipcode | string | The ZIP code of the client's business location. |
| email | string | The email address of the client. |
| phone | string | The phone number of the client. |
| contact_person | string | The name of the contact person at the client's business. |
| business_type | string | The type of business the client operates. |
| company_registration_no | string | The registration number of the client's company. |
| vat_number | string | The VAT (Value Added Tax) number of the client's business. |
| years_in_business | integer | The number of years the client's business has been in operation. |
| no_of_employees | integer | The number of employees at the client's business. |
| principals | text | Details about the principals or key personnel in the client's business. |
| banking_information | text | Information about the client's banking arrangements. |
| sales_turnover | integer | The annual sales turnover of the client's business. |
| trade_references | text | References from other businesses the client has traded with. |
| nature_of_business | text | Description of the nature of the client's business. |
| no_of_warehouses | integer | The number of warehouses owned or used by the client. |
| capacity | integer | The capacity of the client's warehouses. |
| no_of_retail_stores | integer | The number of retail stores owned by the client. |
| no_of_locations | integer | The total number of locations the client operates in. |
| facility_ownership | string | Indicates whether the client owns or leases its facilities. |
| facility_sq_ft | integer | The total square footage of the client's facilities. |
| products_interested | string[] | List of products the client is interested in. |
| projected_no_of_containers_pm | integer | Projected number of containers per month. |
| requested_credit_limit | integer | The credit limit requested by the client. |
| credit_term | string | The credit terms agreed upon with the client. |
| financials_obtained | boolean | Indicates whether financial documents have been obtained from the client. |
| other_information | text | Any additional information provided by the client. |
| signed_by | string | The name of the person who signed the onboarding documents. |
| client_name | string | The name of the client. |
| designation | string | The designation of the person signing the documents. |
| date_signed | datetime | The date when the documents were signed. |
| conducted_by | string | The name of the person who conducted the onboarding process. |
| sales_manager_name | string | The name of the sales manager overseeing the client. |
| date_conducted | datetime | The date when the onboarding process was conducted. |
| created_at | timestamp | Timestamp when the onboarding client record was created. |
| updated_at | timestamp | Timestamp when the onboarding client record was last updated. |

**16. Orders**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the order (primary key). |
| name | string | Name associated with the order. |
| email | string | Email associated with the order. |
| address | text | Address associated with the order. |
| billing_address_id | uuid | Identifier of the associated billing address (foreign key referencing the `id` column in the `addresses` table where `address_type` is 'billing'). |
| shipping_address_id | uuid | Identifier of the associated shipping address (foreign key referencing the `id` column in the `addresses` table where `address_type` is 'shipping'). |
| customer_id | uuid | Identifier of the associated customer (foreign key referencing the `id` column in the `customers` table). |
| status | integer | Status of the order (default: 0). |
| created_at | datetime | Timestamp when the order was created. |
| updated_at | datetime | Timestamp when the order was last updated. |
| order_type | integer | Type of the order. |
| po_number | string | Purchase order number associated with the order. |
| order_number | string | Unique order number. |
| fd_region_id | uuid | Identifier of the associated region (foreign key referencing the `id` column in the `fd_regions` table). |
| quotation_number | string | Quotation number associated with the order. |
| quotation_msg | integer | Quotation message associated with the order. |
| sales_nav | json | JSON data for sales navigation. |
| proforma_status | boolean | Status of the proforma associated with the order. |
| fd_origin_id | uuid | Identifier of the associated origin (foreign key referencing the `id` column in the `fd_origins` table). |
| requested_shipping_date | datetime | Requested shipping date for the order. |
| created_at | timestamp | Timestamp when the order record was created. |
| updated_at | timestamp | Timestamp when the order record was last updated. |

**Relationships**:
- The `billing_address_id` column serves as a foreign key referencing the `id` column in the `addresses` table where `address_type` is 'billing'. This establishes a one-to-one relationship between `orders` and `addresses` (billing), where an order has one billing address, and a billing address belongs to one order.
- The `shipping_address_id` column serves as a foreign key referencing the `id` column in the `addresses` table where `address_type` is 'shipping'. This establishes a one-to-one relationship between `orders` and `addresses` (shipping), where an order has one shipping address, and a shipping address belongs to one order.
- The `customer_id` column serves as a foreign key referencing the `id` column in the `customers` table. This establishes a one-to-many relationship between `customers` and `orders`, where a customer can have many orders, and an order belongs to one customer.
- The `fd_origin_id` column serves as a foreign key referencing the `id` column in the `fd_origins` table. This establishes a one-to-many relationship between `fd_origins` and `orders`, where an origin can have many orders, and an order belongs to one origin.
- The `fd_region_id` column serves as a foreign key referencing the `id` column in the `fd_regions` table. This establishes a one-to-many relationship between `fd_regions` and `orders`, where a region can have many orders, and an order belongs to one region.

**17. Products**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the product (primary key). |
| name | string | Name of the product. |
| price | decimal | Price of the product. |
| customer_id | uuid | Identifier of the associated customer (foreign key referencing the `id` column in the `customers` table). |
| created_at | datetime | Timestamp when the product was created. |
| updated_at | datetime | Timestamp when the product was last updated. |
| sku | string | Stock Keeping Unit (SKU) of the product. |
| brand | string | Brand of the product. |
| rim | string | Rim specification of the product. |
| li | string | Load index of the product. |
| sr | string | Speed rating of the product. |
| lr | string | Load range of the product. |
| size | string | Size of the product. |
| mark | string | Mark of the product. |
| pattern | string | Pattern of the product. |
| season | string | Season of the product. |
| unit_price | decimal | Price per unit of the product. |
| fe | string | FE of the product. |
| wg | string | WG of the product. |
| ernc | string | ERNC of the product. |
| ern | string | ERN of the product. |
| wr | string | WR of the product. |
| application | string | Application of the product. |
| ean | string | EAN of the product. |
| sw | string | SW of the product. |
| quantity | integer | Quantity of the product (default: 0). |
| qpc | integer | QPC of the product (default: 0). |
| posex | string | Position index of the product. |
| currency | string | Currency of the product (default: "USD"). |
| mtr_group | string | MTR group of the product. |
| plant | string | Plant of the product. |
| plant_name | string | Name of the plant associated with the product. |
| construction | string | Construction of the product. |
| market_segment | string | Market segment of the product. |
| treadwear_wm | string | Treadwear WM of the product. |
| ratio | string | Ratio of the product. |
| rh_warranty | string | RH warranty of the product. |
| s_guarantee | string | S guarantee of the product. |
| certifications | string | Certifications of the product. |
| pending_qty | string | Pending quantity of the product. |
| prod_status | string | Product status. |
| z_vendor | string | Vendor of the product. |
| meins | string | MEINS of the product. |
| width | string | Width of the product. |
| sidewall | string | Sidewall of the product. |
| plantregion | string | Plant region of the product. |
| z_size2 | string | Size 2 of the product. |
| z_origin | string | Origin of the product. |
| z_factory | string | Factory of the product. |
| z_volume | string | Volume of the product. |
| z_volunit | string | Volume unit of the product. |

**Relationship**:
- The `customer_id` column serves as a foreign key referencing the `id` column in the `customers` table. This establishes a one-to-many relationship between `customers` and `products`, where a customer can have many products, and a product belongs to one customer.

**18. Users**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the user (primary key). |
| name | string | Name of the user. |
| email | string | Email address of the user. |
| phone | string | Phone number of the user. |
| company | string | Company associated with the user. |
| address | text | Address of the user. |
| message | text | Additional message or notes. |
| created_at | datetime | Timestamp when the user was created. |
| updated_at | datetime | Timestamp when the user was last updated. |
| status | integer | Status of the user (default: 0). |
| sap_id | string | SAP ID associated with the user. |
| role_id | uuid | Identifier of the associated role (foreign key referencing the `id` column in the `roles` table). |
| permissions | string[] | Permissions assigned to the user (default: []). |

**Relationship**:
- The `role_id` column serves as a foreign key referencing the `id` column in the `roles` table. This establishes a one-to-many relationship between `roles` and `users`, where a role can have many users, and a user belongs to one role.

**19. Roles**

| Attribute Name | Data Type | Description |
|----------------|------------|--------------|
| id | uuid | Unique identifier for the role (primary key). |
| name | string | Name of the role. |
| created_at | datetime | Timestamp when the role was created. |
| updated_at | datetime | Timestamp when the role was last updated. |

**ERD Diagram**