PowderCoatingLogix/scripts/README-ResetCompanyData.md

Company Data Reset Scripts

These scripts allow you to completely reset a company's transactional data for testing purposes while preserving lookup tables and the company structure.

Files

• PreviewCompanyDataReset.sql - Shows what would be deleted without making changes (safe to run)
• ResetCompanyData.sql - Actually deletes the data (use with caution!)

What Gets Deleted

The scripts remove ALL transactional data for the specified company:

• ✓ Jobs, Job Items, Job Notes, Job Photos
• ✓ Quotes and Quote Items
• ✓ Customers
• ✓ Inventory Items and Inventory Transactions
• ✓ Equipment and Maintenance Records
• ✓ Catalog Categories and Catalog Items
• ✓ Suppliers
• ✓ Appointments
• ✓ Pricing Tiers
• ✓ Company Operating Costs
• ✓ Company Preferences

What Gets Preserved

The scripts preserve all lookup tables and structure:

• ✓ The Company record itself
• ✓ Users associated with the company
• ✓ Job Status Lookups
• ✓ Job Priority Lookups
• ✓ Quote Status Lookups
• ✓ Inventory Category Lookups
• ✓ Appointment Status Lookups
• ✓ Appointment Type Lookups

Usage

Step 1: Preview (Recommended)

First, run the preview script to see what will be deleted:

1. Open PreviewCompanyDataReset.sql in SQL Server Management Studio (SSMS) or Azure Data Studio
2. Set the company identifier at the top of the script:

   -- Option 1: Use company code
   DECLARE @CompanyCode NVARCHAR(50) = 'DEMO';
   
   -- Option 2: OR use company ID (comment out the line above)
   -- DECLARE @CompanyId INT = 1;
   

3. Execute the script
4. Review the output showing:
   • Count of records to be deleted per table
   • Total records to be deleted
   • Sample data from each table
   • What will be preserved

Step 2: Reset (Actual Deletion)

After reviewing the preview, run the reset script:

1. Open ResetCompanyData.sql
2. Set the same company identifier:

   DECLARE @CompanyCode NVARCHAR(50) = 'DEMO';
   

3. IMPORTANT: Make a database backup if needed
4. Execute the script
5. Review the output showing:
   • Deleted record counts per table
   • Total deleted records
   • Preserved data

Step 3: Re-seed (Optional)

After resetting, you can re-populate the company with fresh demo data:

1. Log in to the application as a SuperAdmin
2. Navigate to: Platform Management > Seed Data
3. Click "Seed Data" for the company you just reset
4. Review the seeding results and any warnings

Safety Features

Transaction Rollback

Both scripts use transactions. If any error occurs during deletion, ALL changes are rolled back automatically.

Validation

• Scripts validate that the company exists before attempting deletion
• Clear error messages if company is not found

Detailed Logging

• Each deletion step is logged with record counts
• Final summary shows exactly what was deleted
• Preserved data is clearly listed

Examples

Reset the Demo Company

DECLARE @CompanyCode NVARCHAR(50) = 'DEMO';

Reset a Specific Company by ID

-- DECLARE @CompanyCode NVARCHAR(50) = 'DEMO';  -- Comment this out
DECLARE @CompanyId INT = 5;

Reset Multiple Companies

To reset multiple companies, execute the script once for each company (change the company code/ID between runs).

Common Scenarios

Testing Fresh Import

1. Run ResetCompanyData.sql for company "ABC"
2. Import CSV data for company ABC
3. Test the import results

Testing Seed Data Changes

1. Modify seed data routines in code
2. Run ResetCompanyData.sql for "DEMO"
3. Re-seed via UI to test new seed data
4. Verify results

Clean Slate for Demo

1. Preview what will be deleted
2. Run ResetCompanyData.sql
3. Re-seed with fresh demo data
4. Demo is ready with clean data

Warnings

⚠️ IMPORTANT: These scripts permanently delete data!

• Always run the preview script first
• Make a database backup before running reset on production data
• Cannot be undone (unless you restore from backup)
• Users are NOT deleted (they will exist but have no data)
• Company configuration is NOT deleted

Database Permissions

To run these scripts, you need:

• db_datawriter role (to delete data)
• db_datareader role (to read company info)
• Execute permissions on the database

Troubleshooting

Foreign Key Constraint Error

If you get a foreign key violation, the script may need updating for new tables. Check the error message for the table name and add it to the script in the correct order.

Company Not Found

Company not found! Please check @CompanyId or @CompanyCode

Solution: Verify the company code or ID is correct:

SELECT Id, CompanyCode, CompanyName FROM Companies;

Transaction Rollback

If the script fails and rolls back:

1. Check the error message in the output
2. Fix the issue (usually a missing table or new FK constraint)
3. Re-run the script

Support

For questions or issues with these scripts:

1. Check the script comments for usage details
2. Review the CLAUDE.md file for database structure
3. Contact the development team

---

Last Updated: 2026-02-16