# B2B Garments Marketplace - Factory Profile Database Schema

This document provides the complete, production-ready database schema definitions for the **Factory Profile** module, supporting both **NoSQL (MongoDB / Mongoose)** and **Relational SQL (PostgreSQL / Drizzle ORM)** environments.

---

## 1. MongoDB (Mongoose Schema)

The following TypeScript code defines the Mongoose Schema, Model, and structural validators for the Factory collection.

```typescript
import mongoose, { Schema, Document } from 'mongoose';

// 1. Define the TypeScript Interface for type-safety
export interface IFactory extends Document {
  userId: mongoose.Types.ObjectId;
  name: string;
  location: string;
  address: string;
  size: 'Small' | 'Medium' | 'Large';
  qcStaff: number;
  rdStaff: number;
  productionLines: number;
  annualOutput: string;
  leadTime: '30 Days' | '45 Days' | '60 Days' | '90 Days';
  type: 'Woven' | 'Knit' | 'Denim' | 'Sweater' | 'Leather' | 'Activewear';
  capacity: string;
  verificationId: string;
  verificationDoc?: string;
  status: 'Pending' | 'Approved' | 'Rejected';
  createdAt: Date;
  updatedAt: Date;
}

// 2. Define the Mongoose Schema with robust validations
const FactorySchema: Schema = new Schema<IFactory>(
  {
    userId: {
      type: Schema.Types.ObjectId,
      ref: 'User',
      required: [true, 'Owner User ID is required'],
      index: true,
    },
    name: {
      type: String,
      required: [true, 'Factory Name is required'],
      trim: true,
      minlength: [3, 'Factory name must be at least 3 characters'],
      maxlength: [100, 'Factory name cannot exceed 100 characters'],
    },
    location: {
      type: String,
      required: [true, 'Location (City/Region) is required'],
      trim: true,
    },
    address: {
      type: String,
      required: [true, 'Full detailed address is required'],
      trim: true,
    },
    size: {
      type: String,
      enum: {
        values: ['Small', 'Medium', 'Large'],
        message: '{VALUE} is not a valid factory size category',
      },
      required: true,
      default: 'Medium',
    },
    qcStaff: {
      type: Number,
      required: [true, 'Total QC Staff is required'],
      min: [0, 'QC staff cannot be negative'],
    },
    rdStaff: {
      type: Number,
      required: [true, 'Total R&D Staff is required'],
      min: [0, 'R&D staff cannot be negative'],
    },
    productionLines: {
      type: Number,
      required: [true, 'Number of production lines is required'],
      min: [1, 'Must have at least 1 production line'],
    },
    annualOutput: {
      type: String,
      required: [true, 'Annual production output is required'],
      trim: true,
    },
    leadTime: {
      type: String,
      enum: ['30 Days', '45 Days', '60 Days', '90 Days'],
      required: true,
      default: '30 Days',
    },
    type: {
      type: String,
      enum: {
        values: ['Woven', 'Knit', 'Denim', 'Sweater', 'Leather', 'Activewear'],
        message: '{VALUE} is not a valid garments factory type',
      },
      required: true,
    },
    capacity: {
      type: String,
      required: [true, 'Production capacity description is required (e.g. 500,000 Pcs/Month)'],
      trim: true,
    },
    verificationId: {
      type: String,
      required: [true, 'Trade License or BGMEA Membership ID is required'],
      unique: true,
      trim: true,
      index: true,
    },
    verificationDoc: {
      type: String,
      trim: true,
      default: '',
    },
    status: {
      type: String,
      enum: ['Pending', 'Approved', 'Rejected'],
      default: 'Pending',
      required: true,
      index: true,
    },
  },
  {
    timestamps: true, // Automatically manages createdAt & updatedAt fields
  }
);

// 3. Indexes for fast geospatial or status searching
FactorySchema.index({ status: 1, type: 1 });
FactorySchema.index({ location: 'text', name: 'text' }); // Text search capabilities

export const FactoryModel = mongoose.model<IFactory>('Factory', FactorySchema);
```

---

## 2. Relational SQL Schema (PostgreSQL)

The following scripts represent optimized PostgreSQL tables complete with constraints, check enums, indices, and the corresponding TypeScript Drizzle ORM definition.

### Raw PostgreSQL DDL Script

```sql
-- Create custom type enums for PostgreSQL
CREATE TYPE factory_size_enum AS ENUM ('Small', 'Medium', 'Large');
CREATE TYPE factory_lead_time_enum AS ENUM ('30 Days', '45 Days', '60 Days', '90 Days');
CREATE TYPE factory_type_enum AS ENUM ('Woven', 'Knit', 'Denim', 'Sweater', 'Leather', 'Activewear');
CREATE TYPE factory_status_enum AS ENUM ('Pending', 'Approved', 'Rejected');

-- Create Factories table
CREATE TABLE factories (
    id VARCHAR(50) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    name VARCHAR(150) NOT NULL,
    location VARCHAR(100) NOT NULL,
    address TEXT NOT NULL,
    size factory_size_enum DEFAULT 'Medium' NOT NULL,
    qc_staff INTEGER DEFAULT 0 NOT NULL CHECK (qc_staff >= 0),
    rd_staff INTEGER DEFAULT 0 NOT NULL CHECK (rd_staff >= 0),
    production_lines INTEGER DEFAULT 1 NOT NULL CHECK (production_lines >= 1),
    annual_output VARCHAR(100) NOT NULL,
    lead_time factory_lead_time_enum DEFAULT '30 Days' NOT NULL,
    type factory_type_enum NOT NULL,
    capacity VARCHAR(100) NOT NULL,
    verification_id VARCHAR(100) UNIQUE NOT NULL,
    verification_doc VARCHAR(255) DEFAULT '',
    status factory_status_enum DEFAULT 'Pending' NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    
    -- Foreign Key Constraint referencing the platform Users table
    CONSTRAINT fk_factory_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Optimize queries with indexes on high-frequency search vectors
CREATE INDEX idx_factories_user_id ON factories(user_id);
CREATE INDEX idx_factories_status ON factories(status);
CREATE INDEX idx_factories_type ON factories(type);
CREATE INDEX idx_factories_verification_id ON factories(verification_id);
```

### TypeScript Drizzle ORM Schema

```typescript
import { pgTable, varchar, integer, text, timestamp, pgEnum } from 'drizzle-orm/pg-core';
import { users } from './users'; // Assuming users table exists

// Define PostgreSQL Custom Enums
export const sizeEnum = pgEnum('size', ['Small', 'Medium', 'Large']);
export const leadTimeEnum = pgEnum('lead_time', ['30 Days', '45 Days', '60 Days', '90 Days']);
export const typeEnum = pgEnum('type', ['Woven', 'Knit', 'Denim', 'Sweater', 'Leather', 'Activewear']);
export const statusEnum = pgEnum('status', ['Pending', 'Approved', 'Rejected']);

export const factories = pgTable('factories', {
  id: varchar('id', { length: 50 }).primaryKey(),
  userId: varchar('user_id', { length: 50 })
    .notNull()
    .references(() => users.id, { onDelete: 'cascade' }),
  name: varchar('name', { length: 150 }).notNull(),
  location: varchar('location', { length: 100 }).notNull(),
  address: text('address').notNull(),
  size: sizeEnum('size').default('Medium').notNull(),
  qcStaff: integer('qc_staff').default(0).notNull(),
  rdStaff: integer('rd_staff').default(0).notNull(),
  productionLines: integer('production_lines').default(1).notNull(),
  annualOutput: varchar('annual_output', { length: 100 }).notNull(),
  leadTime: leadTimeEnum('lead_time').default('30 Days').notNull(),
  type: typeEnum('type').notNull(),
  capacity: varchar('capacity', { length: 100 }).notNull(),
  verificationId: varchar('verification_id', { length: 100 }).unique().notNull(),
  verificationDoc: varchar('verification_doc', { length: 255 }).default(''),
  status: statusEnum('status').default('Pending').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});
```
