# HAZOP/HAZID Database Migration Guide

This directory contains SQL migration scripts for the HAZOP/HAZID module.

## Migration Files

1. **create-hazop-study-table.sql** - Creates the `HazopStudies` table
2. **create-hazop-action-table.sql** - Creates the `HazopActions` table

## Table Structure

### HazopStudies Table

Stores HAZOP and HAZID study data including metadata, nodes/hazards, and statistics.

**Columns:**
- `id` - Primary key (auto-increment)
- `data` - JSON field containing all study data
- `ProjectId` - Foreign key to Projects table
- `createdById` - Foreign key to Users table
- `createdAt` - Timestamp of creation
- `updatedAt` - Timestamp of last update

**Indexes:**
- Primary key on `id`
- Index on `ProjectId` for project-based queries
- Index on `createdById` for user-based queries
- JSON indexes on `studyType`, `status`, and `processType` for filtering

### HazopActions Table

Stores actions/recommendations from HAZOP/HAZID studies with tracking information.

**Columns:**
- `id` - Primary key (auto-increment)
- `data` - JSON field containing action details
- `status` - ENUM('open', 'in-progress', 'closed', 'cancelled')
- `dueDate` - Due date for action completion
- `closedAt` - Date when action was closed (nullable)
- `HazopStudyId` - Foreign key to HazopStudies table
- `ownerId` - Foreign key to Users table (action owner)
- `closedById` - Foreign key to Users table (user who closed the action, nullable)
- `createdAt` - Timestamp of creation
- `updatedAt` - Timestamp of last update

**Indexes:**
- Primary key on `id`
- Index on `HazopStudyId` for study-based queries
- Index on `ownerId` for owner-based queries
- Index on `closedById` for closure tracking
- Index on `status` for status filtering
- Index on `dueDate` for due date queries

## Migration Methods

### Method 1: Automatic Migration (Recommended for Development)

The Sequelize models will automatically create the tables when the application starts. This is the easiest method for development environments.

**Steps:**
1. Ensure the models are properly defined in `backend/models/`
2. Start the application - tables will be created automatically
3. Verify using: `node backend/verify-hazop-tables.js`

### Method 2: Manual SQL Migration (Recommended for Production)

For production environments, it's recommended to run the SQL scripts manually to have full control over the migration process.

**Steps:**
1. Connect to your MySQL database using phpMyAdmin or MySQL CLI
2. Run `create-hazop-study-table.sql` first
3. Run `create-hazop-action-table.sql` second
4. Verify the tables were created correctly

**MySQL CLI Example:**
```bash
mysql -u username -p database_name < backend/migrations/create-hazop-study-table.sql
mysql -u username -p database_name < backend/migrations/create-hazop-action-table.sql
```

**phpMyAdmin:**
1. Open phpMyAdmin
2. Select your database
3. Go to SQL tab
4. Copy and paste the contents of each SQL file
5. Click "Go" to execute

## Testing the Migration

### Test Script

A test script is provided to verify the migration works correctly:

```bash
cd backend
node test-hazop-migration.js
```

This script will:
1. Test database connection
2. Drop existing tables (if any)
3. Create HazopStudies table
4. Create HazopActions table
5. Insert sample data
6. Verify data retrieval
7. Clean up test data

### Verification Script

To verify the table structure without modifying data:

```bash
cd backend
node verify-hazop-tables.js
```

This script will display:
- Table structure (columns, types, constraints)
- Foreign key relationships
- Indexes

## Rollback

To rollback the migration (remove the tables):

```sql
-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Drop tables
DROP TABLE IF EXISTS `HazopActions`;
DROP TABLE IF EXISTS `HazopStudies`;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;
```

## Prerequisites

Before running the migration, ensure:
1. MySQL database is running and accessible
2. Database credentials are configured in `.env` file
3. The following tables exist (referenced by foreign keys):
   - `Projects` table
   - `Users` table

## Troubleshooting

### Error: "Unknown column 'ProjectId' in 'field list'"

This means the table was created without the foreign key columns. Solution:
1. Drop the tables using the rollback script above
2. Ensure the model definitions include the foreign key columns
3. Re-run the migration

### Error: "Cannot delete or update a parent row: a foreign key constraint fails"

This means there are existing records in HazopActions that reference HazopStudies. Solution:
1. Delete records from HazopActions first
2. Then delete from HazopStudies
3. Or use `SET FOREIGN_KEY_CHECKS = 0` before dropping tables

### Error: "Table 'Projects' doesn't exist" or "Table 'Users' doesn't exist"

The referenced tables must exist before creating the HAZOP tables. Solution:
1. Ensure the main application tables are created first
2. Run the application initialization scripts
3. Then run the HAZOP migration

## Migration Status

- ✅ HazopStudies table migration script created
- ✅ HazopActions table migration script created
- ✅ Migration tested on development database
- ✅ Foreign key constraints verified
- ✅ JSON data storage verified
- ✅ ENUM status field verified

## Next Steps

After successful migration:
1. Verify the tables exist: `SHOW TABLES LIKE 'Hazop%';`
2. Check table structure: `DESCRIBE HazopStudies;` and `DESCRIBE HazopActions;`
3. Test the API endpoints to ensure they can interact with the tables
4. Monitor the application logs for any database-related errors

## Support

If you encounter any issues with the migration:
1. Check the error message carefully
2. Review the troubleshooting section above
3. Verify database permissions
4. Ensure all prerequisites are met
5. Check the application logs for detailed error information
