--=============================================================================================================================
if exists(selecT 1 from sys.tables where object_id=object_id('ResidentHourlyStepRecords'))
	drop table ResidentHourlyStepRecords

if exists(selecT 1 from sys.tables where object_id=object_id('ResidentEpaComplianceRecords'))
	drop table ResidentEpaComplianceRecords

--=============================================================================================================================
-- Drop default constraint and column: ComplianceNotificationInHours
DECLARE @ConstraintName NVARCHAR(255);

-- Find and drop default constraint for ComplianceNotificationInHours
SELECT @ConstraintName = dc.name
FROM sys.default_constraints dc
JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE c.name = 'ComplianceNotificationInHours'
  AND c.object_id = OBJECT_ID('Accounts');

IF @ConstraintName IS NOT NULL
BEGIN
    EXEC('ALTER TABLE Accounts DROP CONSTRAINT ' + @ConstraintName);
END

-- Drop the column if it exists
IF EXISTS (
    SELECT 1 
    FROM sys.columns 
    WHERE Name = N'ComplianceNotificationInHours' 
      AND Object_ID = OBJECT_ID(N'Accounts')
)
BEGIN
    ALTER TABLE Accounts 
    DROP COLUMN ComplianceNotificationInHours;
END
-- Repeat for ComplianceNotificationEnabled
SET @ConstraintName = NULL;

SELECT @ConstraintName = dc.name
FROM sys.default_constraints dc
JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE c.name = 'ComplianceNotificationEnabled'
  AND c.object_id = OBJECT_ID('Accounts');

IF @ConstraintName IS NOT NULL
BEGIN
    EXEC('ALTER TABLE Accounts DROP CONSTRAINT ' + @ConstraintName);
END

IF EXISTS (
    SELECT 1 
    FROM sys.columns 
    WHERE Name = N'ComplianceNotificationEnabled' 
      AND Object_ID = OBJECT_ID(N'Accounts')
)
BEGIN
    ALTER TABLE Accounts 
    DROP COLUMN ComplianceNotificationEnabled;
END
