# Organization API Documentation

## Overview
The Organization API has been recreated to match the database schema exactly. This API manages organizations in the multi-organization CMS system.

## Database Schema
Based on `database_schema.sql`, the `organizations` table has the following structure:

```sql
CREATE TABLE IF NOT EXISTS `organizations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) NOT NULL,
  `type` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `address` text DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `logo` varchar(500) DEFAULT NULL,
  `theme` varchar(500) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
)
```

## Key Features

### 1. Fixed Pagination Issue
- **Problem**: "Incorrect arguments to mysqld_stmt_execute" error
- **Solution**: Added proper integer validation for `page` and `limit` parameters
- **Implementation**: 
  ```javascript
  const validPage = Math.max(1, parseInt(page) || 1)
  const validLimit = Math.max(1, Math.min(100, parseInt(limit) || 50))
  ```

### 2. Enhanced Pagination Response
- Returns total count of records
- Calculates total pages
- Provides current page and limit information
- Example response:
  ```json
  {
    "pagination": {
      "page": 1,
      "limit": 50,
      "total": 100,
      "totalPages": 2
    }
  }
  ```

### 3. Multi-Organization Support
- Organizations are the root of the organizational hierarchy
- Related entities: Units → Departments → Designations → Users
- All organization_id fields default to 1 (Bangladesh Navy)

## API Endpoints

### 1. Get All Organizations
**GET** `/api/organizations`

**Query Parameters:**
- `page` (optional, default: 1) - Page number
- `limit` (optional, default: 50, max: 100) - Items per page
- `search` (optional) - Search in name, code, or description
- `is_active` (optional) - Filter by active status (true/false)
- `type` (optional) - Filter by organization type

**Response:**
```json
{
  "status": "success",
  "message": "Organizations retrieved successfully",
  "data": {
    "organizations": [...],
    "statistics": {
      "total_organizations": 100,
      "active_organizations": 95,
      "inactive_organizations": 5
    },
    "pagination": {
      "page": 1,
      "limit": 50,
      "total": 100,
      "totalPages": 2
    }
  }
}
```

### 2. Get Organization by ID
**GET** `/api/organizations/:id`

**Response:**
```json
{
  "status": "success",
  "message": "Organization retrieved successfully",
  "data": {
    "organization": {
      "id": 1,
      "name": "Bangladesh Navy",
      "code": "BN",
      "type": "Military",
      "description": "Naval Forces of Bangladesh",
      "address": "Naval Headquarters, Dhaka",
      "phone": "+880-2-9853871",
      "email": "info@navy.mil.bd",
      "website": "https://www.navy.mil.bd",
      "logo": null,
      "theme": null,
      "is_active": 1,
      "created_by": null,
      "created_by_name": null,
      "created_at": "2025-10-20T00:00:00.000Z",
      "updated_at": "2025-10-20T00:00:00.000Z",
      "total_units": 4,
      "total_users": 145
    }
  }
}
```

### 3. Get Organization Statistics
**GET** `/api/organizations/statistics`

**Response:**
```json
{
  "status": "success",
  "message": "Organization statistics retrieved successfully",
  "data": {
    "total_organizations": 100,
    "active_organizations": 95,
    "inactive_organizations": 5
  }
}
```

### 4. Get Organization Types
**GET** `/api/organizations/types`

**Response:**
```json
{
  "status": "success",
  "message": "Organization types retrieved successfully",
  "data": {
    "types": ["Military", "Government", "Corporate", "NGO"]
  }
}
```

### 5. Create Organization
**POST** `/api/organizations`

**Required Permission:** `organization.create`

**Request Body:**
```json
{
  "name": "New Organization",
  "code": "NEW-ORG",
  "type": "Corporate",
  "description": "Organization description",
  "address": "123 Main St",
  "phone": "+880-123-456789",
  "email": "contact@neworg.com",
  "website": "https://neworg.com",
  "logo": "/uploads/logo.png",
  "theme": "{\"primaryColor\":\"#003366\"}"
}
```

**Response:**
```json
{
  "status": "success",
  "message": "Organization created successfully",
  "data": {
    "organization": { ... }
  }
}
```

### 6. Update Organization
**PUT** `/api/organizations/:id`

**Required Permission:** `organization.update`

**Request Body:** (all fields optional)
```json
{
  "name": "Updated Name",
  "is_active": true,
  "theme": "{\"primaryColor\":\"#004488\"}"
}
```

**Response:**
```json
{
  "status": "success",
  "message": "Organization updated successfully",
  "data": {
    "organization": { ... }
  }
}
```

### 7. Delete Organization
**DELETE** `/api/organizations/:id`

**Required Permission:** `organization.delete`

**Constraints:**
- Cannot delete organization ID 1 (default organization)
- Cannot delete organizations with existing units or users

**Response:**
```json
{
  "status": "success",
  "message": "Organization deleted successfully"
}
```

## Validation Rules

### Create/Update Validation
- **name**: Required, 2-255 characters, alphanumeric with common punctuation
- **code**: Required, 2-50 characters, uppercase letters, numbers, underscores, hyphens
- **type**: Optional, max 100 characters
- **description**: Optional, max 2000 characters
- **address**: Optional, max 500 characters
- **phone**: Optional, max 20 characters, digits/spaces/hyphens/+/()
- **email**: Optional, valid email format, max 255 characters
- **website**: Optional, valid URL format, max 255 characters
- **logo**: Optional, max 500 characters
- **theme**: Optional, max 500 characters (stored as string, typically JSON)
- **is_active**: Optional (update only), boolean

## Required Permissions

To use the Organization API, users need the following permissions:

```sql
-- Add these permissions to the permissions table
INSERT INTO permissions (name, description, resource, action) VALUES
  ('organization.create', 'Create new organizations', 'organization', 'create'),
  ('organization.read', 'View organization information', 'organization', 'read'),
  ('organization.update', 'Update organization information', 'organization', 'update'),
  ('organization.delete', 'Delete organizations', 'organization', 'delete');
```

## Error Handling

### Common Error Responses

**404 Not Found:**
```json
{
  "status": "error",
  "message": "Organization not found"
}
```

**400 Bad Request (Duplicate Code):**
```json
{
  "status": "error",
  "message": "Organization code already exists",
  "error": "Error creating organization: Duplicate entry 'CODE' for key 'code'"
}
```

**400 Bad Request (Cannot Delete):**
```json
{
  "status": "error",
  "message": "Cannot delete organization with existing units or users. Please remove or reassign them first."
}
```

**400 Bad Request (Default Organization):**
```json
{
  "status": "error",
  "message": "Cannot delete the default organization"
}
```

## Files Modified

1. **backend/src/models/Organization.js**
   - Fixed pagination parameters to use proper integer values
   - Added total count query for accurate pagination
   - Enhanced error handling
   - Added calculated fields (total_units, total_users)

2. **backend/src/controllers/organizationController.js**
   - Updated to handle new pagination structure
   - Improved error responses
   - Added null check for req.user

3. **backend/src/routes/organizations.js**
   - Already properly configured with authentication and authorization

4. **backend/src/middleware/organizationValidation.js**
   - Already aligned with database schema constraints

## Testing the API

### Using cURL

```bash
# Get all organizations
curl -H "Authorization: Bearer YOUR_TOKEN" \
  http://localhost:3000/api/organizations

# Get organizations with filters
curl -H "Authorization: Bearer YOUR_TOKEN" \
  "http://localhost:3000/api/organizations?page=1&limit=10&is_active=true"

# Get specific organization
curl -H "Authorization: Bearer YOUR_TOKEN" \
  http://localhost:3000/api/organizations/1

# Create organization
curl -X POST \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"name":"Test Org","code":"TEST"}' \
  http://localhost:3000/api/organizations

# Update organization
curl -X PUT \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"is_active":false}' \
  http://localhost:3000/api/organizations/2
```

## Notes

1. **Theme Field**: Stored as VARCHAR(500), typically contains JSON string
2. **Code Uniqueness**: Organization codes must be unique across the system
3. **Default Organization**: Organization with ID 1 cannot be deleted
4. **Cascade Rules**: Organizations with related units or users cannot be deleted
5. **Created By**: Can be null (for organizations created before users)
6. **Pagination Limits**: Maximum 100 items per page to prevent performance issues

