64 lines
2.3 KiB
Transact-SQL
64 lines
2.3 KiB
Transact-SQL
-- Complete the multi-tenancy migration
|
|
-- Run this to fix the partial migration state
|
|
|
|
USE PowderCoatingDb;
|
|
GO
|
|
|
|
PRINT 'Checking database state...';
|
|
GO
|
|
|
|
-- Step 1: Ensure default company exists and has correct self-reference
|
|
IF EXISTS (SELECT * FROM Companies WHERE Id = 1)
|
|
BEGIN
|
|
UPDATE Companies SET CompanyId = Id WHERE Id = 1 AND (CompanyId = 0 OR CompanyId IS NULL);
|
|
PRINT 'Default company updated';
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
-- Insert if doesn't exist
|
|
SET IDENTITY_INSERT Companies ON;
|
|
INSERT INTO Companies (
|
|
Id, CompanyName, CompanyCode, PrimaryContactName, PrimaryContactEmail,
|
|
Phone, Address, City, State, ZipCode,
|
|
IsActive, SubscriptionStartDate, SubscriptionPlan, TimeZone,
|
|
CompanyId, CreatedAt, IsDeleted
|
|
) VALUES (
|
|
1, 'Demo Company', 'DEMO', 'Admin User', 'admin@demo.com',
|
|
'(555) 123-4567', '123 Demo Street', 'Demo City', 'CA', '90210',
|
|
1, GETUTCDATE(), 'Enterprise', 'America/New_York',
|
|
1, GETUTCDATE(), 0
|
|
);
|
|
SET IDENTITY_INSERT Companies OFF;
|
|
PRINT 'Default company created';
|
|
END
|
|
GO
|
|
|
|
-- Step 2: Update all AspNetUsers to reference the default company
|
|
UPDATE AspNetUsers
|
|
SET CompanyId = 1
|
|
WHERE CompanyId = 0 OR CompanyId IS NULL OR CompanyId NOT IN (SELECT Id FROM Companies);
|
|
PRINT 'AspNetUsers CompanyId updated';
|
|
GO
|
|
|
|
-- Step 3: Update all other tables to reference default company
|
|
UPDATE Customers SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE Jobs SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE JobItems SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE Quotes SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE QuoteItems SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE InventoryItems SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE InventoryTransactions SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE Equipment SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE MaintenanceRecords SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE Suppliers SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE PricingTiers SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE JobPhotos SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE JobNotes SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE CustomerNotes SET CompanyId = 1 WHERE CompanyId = 0;
|
|
UPDATE JobStatusHistory SET CompanyId = 1 WHERE CompanyId = 0;
|
|
PRINT 'All entity CompanyIds updated to reference default company';
|
|
GO
|
|
|
|
PRINT 'Data migration complete. You can now apply the EF migration.';
|
|
GO
|