-- Run against the database containing dbo.Housing_Assembly before deploying
-- the application version that saves resistance. Values are stored in kOhm.
-- Existing data is not changed; existing columns with a different type are
-- reported for manual review instead of being altered.
IF OBJECT_ID(N'dbo.Housing_Assembly', N'U') IS NULL
BEGIN
    THROW 51003, 'dbo.Housing_Assembly was not found in the current database.', 1;
END;

IF COL_LENGTH(N'dbo.Housing_Assembly', N'PT_Resistor_1') IS NULL
BEGIN
    ALTER TABLE dbo.Housing_Assembly ADD [PT_Resistor_1] DECIMAL(5,2) NULL;
END
ELSE IF NOT EXISTS
(
    SELECT 1
    FROM sys.columns AS c
    WHERE c.object_id = OBJECT_ID(N'dbo.Housing_Assembly', N'U')
      AND c.name = N'PT_Resistor_1'
      AND c.system_type_id = TYPE_ID(N'decimal')
      AND c.user_type_id = TYPE_ID(N'decimal')
      AND c.precision = 5
      AND c.scale = 2
      AND c.is_nullable = 1
)
BEGIN
    THROW 51002, 'Housing_Assembly.PT_Resistor_1 must be decimal(5,2) NULL; review the existing column before migration.', 1;
END;
GO