Firestore Database Schema

firestore/
├── users/{userId}
│   └── addresses/{addressId}
├── products/{productId}
│   └── history/{historyId}
├── categories/{categoryId}
├── brands/{brandId}
├── orders/{orderId}
├── remissions/{remissionId}
├── warranties/{warrantyId}
├── warranty_inventory/{itemId}
├── suppliers/{supplierId}
├── purchases/{purchaseId}
├── carts/{cartId}
├── expenses/{expenseId}
├── expenses_trash/{trashId}
├── accounts/{accountId}
├── payables/{payableId}
├── scheduled_transfers/{transferId}
├── chats/{chatId}
│   └── messages/{messageId}
├── config/{configDoc}
└── merchant_feed_cache (singleton)

{
  id: string;                    // Auto-generated document ID
  name: string;                  // Product name
  description: string;           // HTML description
  category: string;              // Category name
  subcategory?: string;          // Optional subcategory
  brand: string;                 // Brand name
  
  // Pricing
  price: number;                 // Current price in COP
  originalPrice?: number;        // Original price (if on sale)
  promoEndsAt?: Timestamp;       // Promotion end date
  
  // Inventory
  stock: number;                 // Available quantity
  sku?: string;                  // SKU/EAN code
  
  // Product Type
  isSimple: boolean;             // true for simple products
  combinations?: [];             // Empty for simple products
  
  // Media
  mainImage: string;             // Primary image URL
  images: string[];              // Additional images
  colorImages?: {};              // Empty for simple products
  
  // Status & Metadata
  status: 'active' | 'draft';    // Visibility status
  createdAt: Timestamp;
  updatedAt: Timestamp;
  last_updated: Timestamp;       // Sync timestamp
  
  // Marketing
  isNewLaunch?: boolean;         // Badge: New
  isHeroPromo?: boolean;         // Featured promotion
}

{
  // ... all fields from simple product
  
  isSimple: false;               // false for variant products
  
  combinations: [
    {
      color: string;             // e.g., "Negro", "Blanco"
      capacity: string;          // e.g., "64GB", "128GB"
      price: number;             // Variant-specific price
      stock: number;             // Variant-specific stock
      sku?: string;              // Variant-specific SKU/EAN
    }
  ],
  
  colorImages: {
    "Negro": ["url1", "url2"],   // Images per color
    "Blanco": ["url3", "url4"]
  }
}

// Order item with variant
{
  id: "iphone15pro",
  name: "iPhone 15 Pro",
  price: 4500000,
  quantity: 1,
  color: "Titanio Negro",
  capacity: "256GB",
  mainImage: "https://..."
}

// Stock deduction logic finds matching combination
const idx = combinations.findIndex(c => 
  c.color === "Titanio Negro" && c.capacity === "256GB"
);
combinations[idx].stock -= 1;

{
  id: string;                    // Auto-generated
  
  // Source
  source: 'TIENDA_WEB' | 'ADMIN_PANEL';
  
  // Customer Info
  userId: string;                // Firebase Auth UID or 'GUEST_MP'
  userEmail: string;
  userName: string;
  phone: string;
  clientDoc: string;             // ID number (cedula)
  
  // Shipping
  shippingData: {
    address: string;
    city: string;
    department: string;
    phone?: string;
    notes?: string;
  };
  
  // Billing (optional)
  billingData?: {
    businessName: string;
    nit: string;
    email: string;
  };
  requiresInvoice: boolean;
  
  // Items
  items: [
    {
      id: string;                // Product ID
      name: string;
      price: number;             // Price at purchase time
      quantity: number;
      color?: string;
      capacity?: string;
      mainImage: string;
    }
  ],
  
  // Totals
  subtotal: number;              // Sum of item prices
  shippingCost: number;
  total: number;                 // subtotal + shippingCost
  
  // Payment
  paymentMethod: 'MERCADOPAGO' | 'ADDI' | 'SISTECREDITO' | 'CONTRAENTREGA';
  paymentStatus: 'PENDING' | 'PAID' | 'FAILED';
  paymentId?: string;            // External payment ID
  paymentAccountId?: string;     // Treasury account ID
  paymentMethodName?: string;    // Display name
  
  // Status
  status: 'PENDIENTE_PAGO' | 'PAGADO' | 'PENDIENTE' | 'DESPACHADO' | 
          'EN_RUTA' | 'ENTREGADO' | 'CANCELADO' | 'RECHAZADO';
  statusDetail?: string;         // Rejection reason
  
  // Logistics
  trackingNumber?: string;       // Shipping guide number
  carrier?: string;              // e.g., "Servientrega"
  
  // Inventory
  isStockDeducted: boolean;      // Prevents double deduction
  
  // Timestamps
  createdAt: Timestamp;
  updatedAt?: Timestamp;
  
  // Notifications
  confirmationEmailSent?: boolean;
  dispatchEmailSent?: boolean;
  
  // Legacy
  buyerInfo?: {                  // Deprecated, use root fields
    name: string;
    email: string;
    phone: string;
  };
}

{
  id: string;                    // Same as Firebase Auth UID
  email: string;
  displayName: string;
  photoURL?: string;
  phoneNumber?: string;
  
  role: 'customer' | 'admin';
  
  createdAt: Timestamp;
  lastLogin?: Timestamp;
  
  // Preferences
  newsletter?: boolean;
}

{
  id: string;
  label: string;                 // e.g., "Casa", "Oficina"
  address: string;
  city: string;
  department: string;
  postalCode?: string;
  isDefault: boolean;
  createdAt: Timestamp;
}

{
  id: string;
  name: string;                  // e.g., "Smartphones"
  slug: string;                  // URL-friendly name
  description?: string;
  image?: string;                // Category banner
  order: number;                 // Display order
  isActive: boolean;
}

{
  id: string;
  name: string;                  // e.g., "Samsung"
  logo?: string;
  isActive: boolean;
}

{
  id: string;                    // Same as order ID
  orderId: string;
  source: 'WEBHOOK_MP' | 'WEBHOOK_ADDI' | 'WEBHOOK_SISTECREDITO' | 'TIENDA_WEB';
  
  // Customer
  clientName: string;
  clientPhone: string;
  clientDoc: string;
  clientAddress: string;         // Formatted: "address, city"
  
  // Items (same as order)
  items: Array<OrderItem>;
  total: number;
  
  // Status
  status: 'PENDIENTE_ALISTAMIENTO' | 'EN_ALISTAMIENTO' | 'LISTO' | 'DESPACHADO';
  type: 'VENTA_WEB' | 'VENTA_TIENDA';
  
  createdAt: Timestamp;
}

{
  id: string;
  userId: string;
  
  // Product Info
  productName: string;
  productBrand: string;
  serialNumber?: string;
  purchaseDate: Timestamp;
  purchaseProof?: string;        // Receipt image URL
  
  // Issue
  issueDescription: string;
  issueImages?: string[];        // Up to 3 images
  
  // Status
  status: 'PENDING' | 'IN_REVIEW' | 'APPROVED' | 'REJECTED' | 'COMPLETED';
  adminNotes?: string;
  resolution?: string;
  
  createdAt: Timestamp;
  updatedAt?: Timestamp;
}

{
  id: string;
  name: string;
  nit: string;
  email?: string;
  phone?: string;
  address?: string;
  isActive: boolean;
  createdAt: Timestamp;
}

{
  id: string;
  
  // Transaction
  amount: number;
  category: string;              // e.g., "Ingreso Ventas Online"
  description: string;
  paymentMethod: string;         // Account name
  
  // Type
  type: 'INCOME' | 'EXPENSE' | 'TRANSFER';
  
  // Relations
  orderId?: string;              // Link to order
  supplierName?: string;         // Supplier or customer name
  
  // Timestamps
  date: Timestamp;               // Transaction date
  createdAt: Timestamp;          // Record creation
}

db.collection('expenses').add({
  amount: 150000,
  category: "Ingreso Ventas Online",
  description: "Venta MP #abc12345",
  paymentMethod: "MercadoPago",
  type: 'INCOME',
  orderId: "abc12345...",
  supplierName: "Juan Pérez",
  date: serverTimestamp(),
  createdAt: serverTimestamp()
});

{
  id: string;
  name: string;                  // e.g., "MercadoPago", "Bancolombia"
  type: 'BANK' | 'CASH' | 'GATEWAY';
  balance: number;               // Current balance
  
  // Gateway Link
  gatewayLink?: 'MERCADOPAGO' | 'ADDI' | 'SISTECREDITO';
  
  // Flags
  isDefaultOnline?: boolean;     // Default for online payments
  isActive: boolean;
  
  createdAt: Timestamp;
}

{
  id: string;
  
  sourceAccountId: string;
  targetAccountId: string;
  amount: number;
  description: string;
  
  // Schedule
  scheduledDate: Timestamp;      // When to execute
  
  // Status
  status: 'PENDING' | 'COMPLETED' | 'FAILED';
  executedAt?: Timestamp;
  error?: string;
  
  createdAt: Timestamp;
}

{
  id: string;                    // Phone number (e.g., "573001234567")
  phoneNumber: string;
  clientName: string;            // From WhatsApp profile
  
  // Status
  status: 'open' | 'closed';
  unread: boolean;               // Has unread messages
  
  // Last Message
  lastMessage: string;           // Preview text
  lastMessageAt: Timestamp;
  lastCustomerInteraction: Timestamp;
  
  // Bot
  lastAutoReply?: Timestamp;     // Last auto-reply sent
  
  platform: 'whatsapp';
}

{
  id: string;
  
  type: 'incoming' | 'outgoing';
  content: string;               // Message text
  
  // Media
  messageType: 'text' | 'image' | 'audio';
  mediaUrl?: string;             // Firebase Storage URL
  
  // Meta
  whatsappId: string;            // Meta message ID
  isAutoReply?: boolean;         // Bot-generated
  
  timestamp: Timestamp;
}

{
  defaultPrice: number;          // Default shipping cost
  freeThreshold: number;         // Free shipping over this amount
  carriers: [
    {
      name: string;              // e.g., "Servientrega"
      trackingUrl: string;
      isActive: boolean;
    }
  ]
}

{
  xmlMap: {
    [productId: string]: string  // Pre-rendered XML blocks
  },
  lastGenerated: number;         // Unix timestamp (ms)
}

{
  "indexes": [],
  "fieldOverrides": []
}

{
  "indexes": [
    {
      "collectionGroup": "orders",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "status", "order": "ASCENDING" },
        { "fieldPath": "createdAt", "order": "DESCENDING" }
      ]
    },
    {
      "collectionGroup": "orders",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "userId", "order": "ASCENDING" },
        { "fieldPath": "createdAt", "order": "DESCENDING" }
      ]
    },
    {
      "collectionGroup": "products",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "status", "order": "ASCENDING" },
        { "fieldPath": "updatedAt", "order": "ASCENDING" }
      ]
    },
    {
      "collectionGroup": "scheduled_transfers",
      "queryScope": "COLLECTION",
      "fields": [
        { "fieldPath": "status", "order": "ASCENDING" },
        { "fieldPath": "scheduledDate", "order": "ASCENDING" }
      ]
    }
  ]
}

import { serverTimestamp } from 'firebase/firestore';

{
  createdAt: serverTimestamp(),
  updatedAt: serverTimestamp()
}

// Payment webhook (functions/mercadopago.js:205-305)
await db.runTransaction(async (t) => {
  // 1. Read phase
  const orderSnap = await t.get(orderRef);
  const productSnap = await t.get(productRef);
  
  // 2. Write phase
  t.update(productRef, { stock: newStock });
  t.update(accountRef, { balance: newBalance });
  t.set(remissionRef, data);
  t.update(orderRef, { status: 'PAGADO' });
});

// Delete product and its history
const historySnap = await db
  .collection(`products/${productId}/history`)
  .get();

const batch = db.batch();
historySnap.forEach(doc => batch.delete(doc.ref));
batch.delete(productRef);
await batch.commit();

// ✅ Good: Limited query
const recent = await db.collection('orders')
  .where('status', '==', 'PAGADO')
  .orderBy('createdAt', 'desc')
  .limit(20)
  .get();

// ❌ Bad: Unbounded query
const all = await db.collection('orders').get();

// Real-time updates (admin panel)
const unsubscribe = db.collection('orders')
  .where('status', '==', 'PENDIENTE')
  .onSnapshot(snapshot => {
    // Update UI
  });

// One-time read (Cloud Functions)
const snapshot = await db.collection('orders')
  .doc(orderId)
  .get();