-- =============================================================================
-- Script 109: Add Vehicle Make/Model/Type description columns for eNatis-driven
-- capture (Phase 2 of the eNatis API reconfiguration work).
--
-- Why:
--   eNatis returns Vehicle Make, Model and Type (combined MVCat + MVDesc) as
--   strings. Today the capture form maps these to FK IDs against Md.VehicleMakes
--   / Md.VehicleModels / Md.VehicleTypes, which requires the lookup row to
--   already exist. We're storing the eNatis strings directly so capture works
--   even when the lookup hasn't been pre-populated.
--
--   App.ApplicationLicenceVehicleDetails already has VehicleMakeDescription
--   (legacy). This script adds VehicleModelDescription + VehicleTypeDescription
--   there, and mirrors all three columns on Verified.Vehicles.
-- =============================================================================

-- App.ApplicationLicenceVehicleDetails ---------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE [object_id] = OBJECT_ID(N'[App].[ApplicationLicenceVehicleDetails]')
      AND [name] = N'VehicleModelDescription'
)
BEGIN
    ALTER TABLE [App].[ApplicationLicenceVehicleDetails]
        ADD [VehicleModelDescription] NVARCHAR(100) NULL;
END
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE [object_id] = OBJECT_ID(N'[App].[ApplicationLicenceVehicleDetails]')
      AND [name] = N'VehicleTypeDescription'
)
BEGIN
    ALTER TABLE [App].[ApplicationLicenceVehicleDetails]
        ADD [VehicleTypeDescription] NVARCHAR(255) NULL;
END
GO

-- Verified.Vehicles ----------------------------------------------------------
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE [object_id] = OBJECT_ID(N'[Verified].[Vehicles]')
      AND [name] = N'VehicleMakeDescription'
)
BEGIN
    ALTER TABLE [Verified].[Vehicles]
        ADD [VehicleMakeDescription] NVARCHAR(100) NULL;
END
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE [object_id] = OBJECT_ID(N'[Verified].[Vehicles]')
      AND [name] = N'VehicleModelDescription'
)
BEGIN
    ALTER TABLE [Verified].[Vehicles]
        ADD [VehicleModelDescription] NVARCHAR(100) NULL;
END
GO

IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE [object_id] = OBJECT_ID(N'[Verified].[Vehicles]')
      AND [name] = N'VehicleTypeDescription'
)
BEGIN
    ALTER TABLE [Verified].[Vehicles]
        ADD [VehicleTypeDescription] NVARCHAR(255) NULL;
END
GO
