-- Transaction with items
transaction_items.transaction_id = transactions.id

-- Transaction with cashier
users.id = transactions.cashier_id

-- Transaction with branch
branches.id = transactions.branch_id

-- Transaction with payment method
payment_methods.code = transactions.payment_method

Merchant
└── Branch
    └── Warehouses (multiple)
        ├── Priority 0 (Default)
        ├── Priority 1 (Backup 1)
        └── Priority 2 (Backup 2)

-- Available stock formula
available_stock = qty_on_hand - qty_reserved

-- Stock per warehouse
SELECT 
    warehouse_id,
    inventory_id,
    qty_on_hand,
    qty_reserved,
    (qty_on_hand - qty_reserved) AS available
FROM warehouse_stocks
WHERE merchant_id = ?
  AND deleted_at IS NULL;

{
  "sub": 123,              // User ID
  "email": "[email protected]",
  "role": "cashier",
  "merchant_id": 1,
  "branch_id": 5,
  "exp": 1697123456,       // Expiration timestamp
  "iat": 1697120000        // Issued at timestamp
}

admin
├── manager
│   ├── supervisor
│   │   └── cashier
│   └── warehouse_staff
└── accountant

PENDING → PROCESSING → PAID
                    ↓
                  FAILED
                    ↓
                  REFUNDED

SELECT 
    DATE(transaction_date) AS date,
    COUNT(*) AS total_transactions,
    SUM(grand_total) AS total_sales,
    AVG(grand_total) AS avg_transaction,
    payment_method
FROM transactions
WHERE branch_id = ?
  AND transaction_date >= CURDATE()
  AND transaction_date < CURDATE() + INTERVAL 1 DAY
  AND status = 'paid'
  AND deleted_at IS NULL
GROUP BY DATE(transaction_date), payment_method
ORDER BY date DESC;

SELECT 
    p.product_name,
    SUM(ti.quantity) AS total_qty,
    SUM(ti.subtotal) AS total_revenue,
    COUNT(DISTINCT ti.transaction_id) AS transaction_count
FROM transaction_items ti
JOIN products p ON ti.product_id = p.id
JOIN transactions t ON ti.transaction_id = t.id
WHERE t.branch_id = ?
  AND t.transaction_date >= ?
  AND t.transaction_date < ?
  AND t.status = 'paid'
  AND t.deleted_at IS NULL
GROUP BY p.id, p.product_name
ORDER BY total_revenue DESC
LIMIT 10;

SELECT 
    i.inventory_name,
    w.warehouse_name,
    im.movement_type,
    SUM(im.quantity) AS total_quantity,
    COUNT(*) AS movement_count
FROM inventory_movements im
JOIN inventory i ON im.inventory_id = i.id
JOIN warehouses w ON im.warehouse_id = w.id
WHERE im.merchant_id = ?
  AND im.movement_date >= ?
  AND im.movement_date < ?
GROUP BY i.id, w.id, im.movement_type
ORDER BY i.inventory_name, w.warehouse_name;

// Soft delete
db.Model(&Transaction{}).
    Where("id = ?", id).
    Update("deleted_at", time.Now())

// Query without deleted
db.Where("deleted_at IS NULL").Find(&transactions)

// Include deleted (admin only)
db.Unscoped().Find(&transactions)

// Calculate offset
offset := (page - 1) * limit

// Query with pagination
db.Offset(offset).
    Limit(limit).
    Find(&results)

// Count total
var total int64
db.Model(&Transaction{}).Count(&total)

// Calculate total pages
totalPages := int(math.Ceil(float64(total) / float64(limit)))

// Start transaction
tx := db.Begin()

// Defer rollback
defer func() {
    if r := recover(); r != nil {
        tx.Rollback()
    }
}()

// Do operations
if err := tx.Create(&transaction).Error; err != nil {
    tx.Rollback()
    return err
}

if err := tx.Create(&items).Error; err != nil {
    tx.Rollback()
    return err
}

// Commit
return tx.Commit().Error