# Organization API Implementation Guide

## Quick Reference - Apply These Patterns

### ✅ Phase 1 COMPLETED
- [x] authController.js - organization_id added to JWT token
- [x] auth middleware - organization_id added to req.user

## Pattern 1: SELECT Queries (READ)

### BEFORE:
```javascript
const [rows] = await db.query('SELECT * FROM users WHERE id = ?', [id]);
```

### AFTER (Regular Users):
```javascript
const [rows] = await db.query(
  'SELECT * FROM users WHERE id = ? AND organization_id = ?', 
  [id, req.user.organization_id]
);
```

### AFTER (With Super Admin Override):
```javascript
// Super Admin can access all organizations
const isSuperAdmin = req.user.role_id === 1;
const query = isSuperAdmin
  ? 'SELECT * FROM users WHERE id = ?'
  : 'SELECT * FROM users WHERE id = ? AND organization_id = ?';
const params = isSuperAdmin ? [id] : [id, req.user.organization_id];
const [rows] = await db.query(query, params);
```

## Pattern 2: INSERT Queries (CREATE)

### BEFORE:
```javascript
const [result] = await db.query(
  'INSERT INTO units (name, code, description) VALUES (?, ?, ?)',
  [name, code, description]
);
```

### AFTER:
```javascript
const [result] = await db.query(
  'INSERT INTO units (organization_id, name, code, description) VALUES (?, ?, ?, ?)',
  [req.user.organization_id, name, code, description]
);
```

## Pattern 3: UPDATE Queries

### BEFORE:
```javascript
const [result] = await db.query(
  'UPDATE units SET name = ?, code = ? WHERE id = ?',
  [name, code, id]
);
```

### AFTER:
```javascript
const [result] = await db.query(
  'UPDATE units SET name = ?, code = ? WHERE id = ? AND organization_id = ?',
  [name, code, id, req.user.organization_id]
);
```

## Pattern 4: DELETE Queries

### BEFORE:
```javascript
const [result] = await db.query('DELETE FROM units WHERE id = ?', [id]);
```

### AFTER:
```javascript
const [result] = await db.query(
  'DELETE FROM units WHERE id = ? AND organization_id = ?',
  [id, req.user.organization_id]
);
```

## Pattern 5: JOIN Queries

### BEFORE:
```javascript
const [rows] = await db.query(`
  SELECT u.*, d.name as department_name, r.name as rank_name
  FROM users u
  LEFT JOIN departments d ON u.department_id = d.id
  LEFT JOIN ranks r ON u.rank_id = r.id
  WHERE u.id = ?
`, [id]);
```

### AFTER:
```javascript
const [rows] = await db.query(`
  SELECT u.*, d.name as department_name, r.name as rank_name
  FROM users u
  LEFT JOIN departments d ON u.department_id = d.id AND d.organization_id = ?
  LEFT JOIN ranks r ON u.rank_id = r.id AND r.organization_id = ?
  WHERE u.id = ? AND u.organization_id = ?
`, [req.user.organization_id, req.user.organization_id, id, req.user.organization_id]);
```

## Pattern 6: COUNT/AGGREGATE Queries

### BEFORE:
```javascript
const [[{ count }]] = await db.query('SELECT COUNT(*) as count FROM users');
```

### AFTER:
```javascript
const [[{ count }]] = await db.query(
  'SELECT COUNT(*) as count FROM users WHERE organization_id = ?',
  [req.user.organization_id]
);
```

## Pattern 7: Tree/Hierarchy Queries

### BEFORE:
```javascript
const [units] = await db.query(`
  SELECT u.*, parent.name as parent_name
  FROM units u
  LEFT JOIN units parent ON u.parent_id = parent.id
  ORDER BY u.name
`);
```

### AFTER:
```javascript
const [units] = await db.query(`
  SELECT u.*, parent.name as parent_name
  FROM units u
  LEFT JOIN units parent ON u.parent_id = parent.id 
    AND parent.organization_id = ?
  WHERE u.organization_id = ?
  ORDER BY u.name
`, [req.user.organization_id, req.user.organization_id]);
```

## Pattern 8: Search Queries

### BEFORE:
```javascript
const [users] = await db.query(`
  SELECT * FROM users 
  WHERE name LIKE ? OR email LIKE ?
  LIMIT ? OFFSET ?
`, [`%${search}%`, `%${search}%`, limit, offset]);
```

### AFTER:
```javascript
const [users] = await db.query(`
  SELECT * FROM users 
  WHERE organization_id = ? AND (name LIKE ? OR email LIKE ?)
  LIMIT ? OFFSET ?
`, [req.user.organization_id, `%${search}%`, `%${search}%`, limit, offset]);
```

## Example: Complete Controller Update

### userController.js - Before & After

```javascript
// ========== BEFORE ==========
class UserController {
  static async getAllUsers(req, res, next) {
    try {
      const [users] = await db.query('SELECT * FROM users WHERE is_active = 1');
      res.json({ status: 'success', data: { users } });
    } catch (error) {
      next(error);
    }
  }

  static async getUserById(req, res, next) {
    try {
      const { id } = req.params;
      const [rows] = await db.query('SELECT * FROM users WHERE id = ?', [id]);
      
      if (rows.length === 0) {
        return res.status(404).json({ status: 'error', message: 'User not found' });
      }
      
      res.json({ status: 'success', data: { user: rows[0] } });
    } catch (error) {
      next(error);
    }
  }

  static async createUser(req, res, next) {
    try {
      const { name, email, rank_id, unit_id } = req.body;
      
      const [result] = await db.query(
        'INSERT INTO users (name, email, rank_id, unit_id) VALUES (?, ?, ?, ?)',
        [name, email, rank_id, unit_id]
      );
      
      res.status(201).json({ 
        status: 'success', 
        data: { id: result.insertId } 
      });
    } catch (error) {
      next(error);
    }
  }

  static async updateUser(req, res, next) {
    try {
      const { id } = req.params;
      const { name, email } = req.body;
      
      const [result] = await db.query(
        'UPDATE users SET name = ?, email = ? WHERE id = ?',
        [name, email, id]
      );
      
      if (result.affectedRows === 0) {
        return res.status(404).json({ status: 'error', message: 'User not found' });
      }
      
      res.json({ status: 'success', message: 'User updated successfully' });
    } catch (error) {
      next(error);
    }
  }

  static async deleteUser(req, res, next) {
    try {
      const { id } = req.params;
      
      const [result] = await db.query('DELETE FROM users WHERE id = ?', [id]);
      
      if (result.affectedRows === 0) {
        return res.status(404).json({ status: 'error', message: 'User not found' });
      }
      
      res.json({ status: 'success', message: 'User deleted successfully' });
    } catch (error) {
      next(error);
    }
  }
}

// ========== AFTER ==========
class UserController {
  static async getAllUsers(req, res, next) {
    try {
      // Filter by organization_id from authenticated user
      const [users] = await db.query(
        'SELECT * FROM users WHERE organization_id = ? AND is_active = 1',
        [req.user.organization_id]
      );
      
      res.json({ status: 'success', data: { users } });
    } catch (error) {
      next(error);
    }
  }

  static async getUserById(req, res, next) {
    try {
      const { id } = req.params;
      
      // Validate organization ownership
      const [rows] = await db.query(
        'SELECT * FROM users WHERE id = ? AND organization_id = ?',
        [id, req.user.organization_id]
      );
      
      if (rows.length === 0) {
        return res.status(404).json({ 
          status: 'error', 
          message: 'User not found or access denied' 
        });
      }
      
      res.json({ status: 'success', data: { user: rows[0] } });
    } catch (error) {
      next(error);
    }
  }

  static async createUser(req, res, next) {
    try {
      const { name, email, rank_id, unit_id } = req.body;
      
      // Include organization_id from authenticated user
      const [result] = await db.query(
        'INSERT INTO users (organization_id, name, email, rank_id, unit_id) VALUES (?, ?, ?, ?, ?)',
        [req.user.organization_id, name, email, rank_id, unit_id]
      );
      
      res.status(201).json({ 
        status: 'success', 
        data: { id: result.insertId } 
      });
    } catch (error) {
      next(error);
    }
  }

  static async updateUser(req, res, next) {
    try {
      const { id } = req.params;
      const { name, email } = req.body;
      
      // Validate organization ownership in WHERE clause
      const [result] = await db.query(
        'UPDATE users SET name = ?, email = ? WHERE id = ? AND organization_id = ?',
        [name, email, id, req.user.organization_id]
      );
      
      if (result.affectedRows === 0) {
        return res.status(404).json({ 
          status: 'error', 
          message: 'User not found or access denied' 
        });
      }
      
      res.json({ status: 'success', message: 'User updated successfully' });
    } catch (error) {
      next(error);
    }
  }

  static async deleteUser(req, res, next) {
    try {
      const { id } = req.params;
      
      // Validate organization ownership
      const [result] = await db.query(
        'DELETE FROM users WHERE id = ? AND organization_id = ?',
        [id, req.user.organization_id]
      );
      
      if (result.affectedRows === 0) {
        return res.status(404).json({ 
          status: 'error', 
          message: 'User not found or access denied' 
        });
      }
      
      res.json({ status: 'success', message: 'User deleted successfully' });
    } catch (error) {
      next(error);
    }
  }
}
```

## Checklist for Each Controller

### For Every GET endpoint:
- [ ] Add `organization_id = ?` to WHERE clause
- [ ] Pass `req.user.organization_id` as parameter
- [ ] Update error message to include "or access denied"
- [ ] Handle Super Admin special case if needed

### For Every POST endpoint:
- [ ] Add `organization_id` to INSERT column list
- [ ] Add `req.user.organization_id` to VALUES
- [ ] Validate referenced IDs are in same organization

### For Every PUT endpoint:
- [ ] Add `AND organization_id = ?` to WHERE clause
- [ ] Pass `req.user.organization_id` as parameter
- [ ] Update error message to include "or access denied"

### For Every DELETE endpoint:
- [ ] Add `AND organization_id = ?` to WHERE clause
- [ ] Pass `req.user.organization_id` as parameter
- [ ] Update error message to include "or access denied"

## Controllers to Update

1. ✅ authController.js - COMPLETED
2. ⏳ userController.js
3. ⏳ unitController.js
4. ⏳ departmentController.js
5. ⏳ organizationalController.js (designations)
6. ⏳ rankController.js
7. ⏳ contactController.js
8. ⏳ messageController.js
9. ⏳ conversationController.js
10. ⏳ notificationController.js
11. ⏳ dashboardController.js
12. ⏳ superAdminController.js (may need special handling)

## Testing Each Endpoint

```javascript
// Test with different organizations
// User from Org 1 should NOT see data from Org 2

// 1. Login as user from Organization 1
POST /auth/login
{ "login_id": "user1@org1.com", "password": "password" }
// Save token1

// 2. Login as user from Organization 2
POST /auth/login
{ "login_id": "user2@org2.com", "password": "password" }
// Save token2

// 3. Create resource in Org 1
POST /units
Authorization: Bearer token1
{ "name": "Unit Org 1", "code": "U1" }

// 4. Try to access Org 1 resource with Org 2 token (should fail)
GET /units/:id
Authorization: Bearer token2
// Expected: 404 or 403

// 5. List resources (should only show org-specific data)
GET /units
Authorization: Bearer token1
// Should only return units from Org 1
```

## Common Pitfalls to Avoid

1. **Forgetting JOIN clauses**: Add organization_id to JOIN ON conditions
2. **Subqueries**: Must also filter by organization_id
3. **Aggregate queries**: Include WHERE organization_id = ?
4. **Foreign key validation**: Ensure referenced records are in same org
5. **Error messages**: Don't reveal if resource exists in another org

## Files Updated So Far

✅ src/controllers/authController.js
✅ src/middleware/auth.js

---
**Status**: Phase 1 Complete, Ready for Phase 2
**Next**: Update User model and controller