CREATE TABLE [dbo].[Report]
(
	[ID] INT NOT NULL PRIMARY KEY IDENTITY,
    [Archived] BIT NOT NULL DEFAULT 0,
    [TimeStarted] datetime2 NOT NULL,
    [TimeFinished] datetime2 NOT NULL,
    [VehicleID] INT NOT NULL, 
    [MechanicID] INT NOT NULL, 
    [AppointmentID] INT NOT NULL, 
    [CustomerID] INT NOT NULL, 
    [Date] DATETIME2 NOT NULL, 
    [Odometer] INT NOT NULL,
    [RoadTestComments] VARCHAR(MAX) NULL,
    [GeneralComments] VARCHAR(MAX) NULL,
    [StoredFaultCodes] VARCHAR(MAX) NULL,
    [AirConditioning] VARCHAR(30) NOT NULL, 
    [Lights] VARCHAR(30) NOT NULL, 
    [LightsComments] VARCHAR(MAX) NULL,
    [FrontWipers] VARCHAR(30) NOT NULL, 
    [RearWiper] VARCHAR(30) NULL, 
    [Battery] VARCHAR(30) NOT NULL, 
    [BatteryComments] VARCHAR(MAX) NULL, 
    [RHFTyre] VARCHAR(30) NOT NULL,
    [LHFTyre] VARCHAR(30) NOT NULL,
    [RHRTyre] VARCHAR(30) NOT NULL,
    [LHRTyre] VARCHAR(30) NOT NULL, 
    [TyresComments] VARCHAR(MAX) NULL, 
    [NeedsAlignment] BIT NOT NULL DEFAULT 0, 
    [AirFilter] VARCHAR(30) NOT NULL, 
    [CabinFilter] VARCHAR(30) NOT NULL, 
    [DriveBelts] VARCHAR(30) NOT NULL, 
    [TimingBelt] VARCHAR(30) NULL, 
    [Radiator] VARCHAR(30) NOT NULL, 
    [Hoses] VARCHAR(30) NOT NULL, 
    [HosesComments] VARCHAR(MAX) NULL, 
    [EngineOil] VARCHAR(30) NOT NULL,
    [Coolant] VARCHAR(30) NOT NULL,
    [BrakeFluid] VARCHAR(30) NOT NULL, 
    [PowerSteeringFluid] VARCHAR(30) NOT NULL, 
    [TransmissionFluid] VARCHAR(30) NOT NULL, 
    [WindscreenWasherFluid] VARCHAR(30) NOT NULL, 
    [FluidsComments] VARCHAR(MAX) NULL, 
    [SparkPlugs] VARCHAR(30) NULL, 
    [FuelFilter] VARCHAR(30) NOT NULL,
    [FrontSuspension] VARCHAR(30) NOT NULL,
    [FrontSuspensionComments] VARCHAR(MAX) NULL, 
    [RearSuspension] VARCHAR(30) NOT NULL,
    [RearSuspensionComments] VARCHAR(MAX) NULL,
    [FrontBrakes] VARCHAR(30) NOT NULL,
    [FrontBrakesComments] VARCHAR(MAX) NULL, 
    [RearBrakes] VARCHAR(30) NOT NULL,
    [RearBrakesComments] VARCHAR(MAX) NULL,
    [Exhaust] VARCHAR(30) NOT NULL,
    [ExhaustComments] VARCHAR(MAX) NULL,
    [Drivetrain] VARCHAR(30) NOT NULL,
    [DrivetrainComments] VARCHAR(MAX) NULL,
    [OilLeaks] VARCHAR(30) NOT NULL,
    [OilLeaksComments] VARCHAR(MAX) NULL,
    [CoolantLeaks] VARCHAR(30) NOT NULL,
    [CoolantLeaksComments] VARCHAR(MAX) NULL,
    [OtherLeaksComments] VARCHAR(MAX) NULL,
    [Other] VARCHAR(MAX) NULL,
    [OtherInspectionComments] VARCHAR(MAX) NULL, 
    CONSTRAINT [FK_Report_Vehicle] FOREIGN KEY (VehicleID) REFERENCES [Vehicle]([ID]) ON DELETE NO ACTION, 
    CONSTRAINT [FK_Report_Mechanic] FOREIGN KEY (MechanicID) REFERENCES [Mechanic]([ID]) ON DELETE NO ACTION, 
    CONSTRAINT [FK_Report_Appointment] FOREIGN KEY (AppointmentID) REFERENCES [Appointment]([ID]) ON DELETE CASCADE
)
