# Query de Campanhas do Merchant Logado

## Fluxo Completo

### 1. Controller: `getMyCampaigns` (controllers/campaignsController.js)

```javascript
exports.getMyCampaigns = asyncHandler(async (req, res, next) => {
    const { page = 1, limit = 10, active } = req.query;
    const userId = req.user.user_id;

    // 1. Buscar merchant_users do usuário logado
    const MerchantUsers = require('../models/MerchantUsersModel');
    const merchantUsers = await MerchantUsers.findByUser(userId);
    
    // 2. Para cada merchant_user, buscar o establishment_id
    const establishmentIdsSet = new Set();
    
    for (const mu of merchantUsers) {
        if (mu.merchant_id || mu.establishment_id) {
            // Buscar merchant para obter establishment_id
            const merchant = await Merchants.findById(mu.merchant_id || mu.establishment_id);
            
            if (merchant) {
                const estId = merchant.establishment_id || merchant.est_id;
                if (estId) {
                    establishmentIdsSet.add(String(estId));
                }
            }
        }
    }
    
    establishmentIds = Array.from(establishmentIdsSet);

    // 3. Buscar campanhas filtrando por establishment_ids
    const options = {
        page: parseInt(page),
        limit: parseInt(limit),
        active: active !== undefined ? active === 'true' : null,
        establishment_ids: establishmentIds
    };

    const result = await Campaigns.findAll(options);
});
```

### 2. Query SQL: Buscar Merchant Users

**Modelo: MerchantUsersModel.findByUser(userId)**

```sql
-- Se merchant_users tem merchant_id e establishment_id
SELECT 
    mu.*,
    m.establishment_id,
    m.est_id
FROM merchant_users mu
LEFT JOIN merchants m ON mu.merchant_id = m.merchant_id
WHERE mu.user_id = $1
AND mu.is_active = true;

-- Ou se merchant_users tem apenas merchant_id
SELECT 
    mu.*,
    m.establishment_id,
    m.est_id
FROM merchant_users mu
LEFT JOIN merchants m ON mu.merchant_id = m.merchant_id
WHERE mu.user_id = $1
AND mu.is_active = true;
```

### 3. Query SQL: Buscar Campanhas por Establishment IDs

**Modelo: CampaignsModel.findAll({ establishment_ids: [...] })**

```sql
-- Query principal (adaptada dinamicamente conforme estrutura do banco)
SELECT 
    c.*,
    e.name as establishment_name,
    e.type as establishment_type,
    e.est_id as e_establishment_id,
    e.est_id as e_est_id,
    e.address as establishment_address,
    e.phone as establishment_phone,
    e.email as establishment_email,
    e.image_url as establishment_image_url,
    e.image_secondary as establishment_image_secondary,
    e.qr_code as establishment_qr_code,
    e.color as establishment_color,
    e.is_active as establishment_is_active,
    e.created_at as establishment_created_at,
    e.updated_at as establishment_updated_at,
    u.username as created_by_username,
    m.merchant_id,
    m.can_set_custom_points
FROM campaigns c
LEFT JOIN establishments e ON c.est_id = e.est_id  -- ou c.est_id = e.est_id
LEFT JOIN users u ON c.created_by = u.user_id
LEFT JOIN merchants m ON c.merchant_id = m.merchant_id
WHERE c.est_id = ANY($1)  -- ou c.est_id = ANY($1)
  AND c.is_active = $2  -- ou c.active = $2 (se fornecido)
ORDER BY c.created_at DESC  -- ou c.valid_until ASC (se orderBy = 'valid_until_asc')
LIMIT $3 OFFSET $4;

-- Query de contagem
SELECT COUNT(*) as total
FROM campaigns c
WHERE c.est_id = ANY($1)  -- ou c.est_id = ANY($1)
  AND c.is_active = $2;  -- ou c.active = $2 (se fornecido)
```

## Exemplo Completo

### Passo a Passo:

1. **Usuário logado:** `userId = 18`

2. **Buscar merchant_users:**
```sql
SELECT mu.*, m.establishment_id, m.est_id
FROM merchant_users mu
LEFT JOIN merchants m ON mu.merchant_id = m.merchant_id
WHERE mu.user_id = '18'
AND mu.is_active = true;
```

**Resultado exemplo:**
```
merchant_id: 131
establishment_id: 321
```

3. **Buscar campanhas do establishment_id = 321:**
```sql
SELECT 
    c.*,
    e.name as establishment_name,
    e.type as establishment_type,
    e.address as establishment_address,
    e.phone as establishment_phone,
    e.email as establishment_email,
    e.image_url as establishment_image_url,
    e.image_secondary as establishment_image_secondary,
    e.qr_code as establishment_qr_code,
    e.color as establishment_color,
    e.is_active as establishment_is_active,
    e.created_at as establishment_created_at,
    e.updated_at as establishment_updated_at,
    u.username as created_by_username
FROM campaigns c
LEFT JOIN establishments e ON c.est_id = e.est_id
LEFT JOIN users u ON c.created_by = u.user_id
WHERE c.est_id = ANY(ARRAY['321'])
ORDER BY c.created_at DESC
LIMIT 10 OFFSET 0;
```

## Endpoint

**GET /api/campaigns/my-campaigns**

**Query Parameters:**
- `page` (opcional, padrão: 1)
- `limit` (opcional, padrão: 10)
- `active` (opcional: 'true' ou 'false')

**Resposta:**
```json
{
  "success": true,
  "data": [
    {
      "id": "...",
      "campaignName": "...",
      "establishment": {
        "id": 321,
        "name": "...",
        ...
      },
      ...
    }
  ],
  "pagination": {
    "page": 1,
    "limit": 10,
    "total": 5,
    "pages": 1
  }
}
```

## Observações Importantes

1. **Filtro por Establishment IDs:** A query filtra campanhas apenas dos estabelecimentos aos quais o merchant logado tem acesso via `merchant_users`.

2. **Múltiplos Estabelecimentos:** Se o merchant tiver acesso a múltiplos estabelecimentos, a query retorna campanhas de todos eles usando `ANY(ARRAY[...])`.

3. **Estrutura Dinâmica:** A query se adapta dinamicamente conforme a estrutura do banco:
   - Usa `est_id` ou `establishment_id` conforme disponível
   - Usa `is_active` ou `active` conforme disponível
   - Faz JOIN com `merchants` se a coluna `merchant_id` existir em `campaigns`

4. **Ordenação:** Por padrão ordena por `created_at DESC`, mas pode ordenar por `valid_until` se especificado no `orderBy`.