CREATE TABLE Crash(
AmendededIndicator INT NULL,
CounterIndicator INT NULL,
PrivatePropertyIndicator INT NULL,
PublicLandIndicator INT NULL,
CaseTrackingID nVarChar(20) NOT NULL,
DateOfReport DATETIME NULL,
AccidentDate DATETIME NOT NULL,
AccidentTime DATETIME NULL,
ReportingOfficerID nVarChar(8) NULL,
ReportingOfficerName nVarChar(30) NULL,
ArrivalDate DATETIME NULL,
ArrivalTime DATETIME NULL,
RoadwayClearedDate DATETIME NULL,
RoadwayClearedTime DATETIME NULL,
LastResponderLeftDate DATETIME NULL,
LastResponderLeftTime DATETIME NULL,
AgencyID nVarChar(30) NULL,
AgencyDetail nVarChar(30) NULL,
AgencyCode nVarChar(20) NULL,
InvestigatedAtSceneIndicator INT NULL,
DistrictNumber nVarChar(4) NULL,
NumberKilled INT NULL,
NumberInjured INT NULL,
TotalVehicles INT NULL,
TotalNonMotorists INT NULL,
JuvenilesInvolvedIndicator INT NULL,
SecondaryCrashIndicator INT NULL,
ConstructionZoneIndicator INT NULL,
SchoolZoneIndicator INT NULL,
Latitude FLOAT NULL,
Longitude FLOAT NULL,
LocationCityName nVarChar(24) NULL,
LocationCountyName nVarChar(20) NULL,
CountyCode INT NULL,
OnRoad INT NULL,
OnRoadAddress INT NULL,
ReferenceIntersectingRoad INT NULL,
ReferenceIntersectingAddress INT NULL,
OffsetDistanceUnit INT NULL,
OffsetDistance FLOAT NULL,
OffsetDirection nVarChar(1) NULL,
HighwayNumber INT NULL,
Milepoint FLOAT NULL,
InterstateHighway INT NULL,
StateHighway INT NULL,
CityStreetCountyRd INT NULL,
OtherRoadway INT NULL,
MilepointOffsetDistanceUnit INT NULL,
MilepointOffsetDistance FLOAT NULL,
MilepointOffsetDirection nVarChar(1) NULL,
Location INT NULL,
NumberOfLanesBlocked INT NULL,
LanePosition nVarChar(3) NULL,
HarmfulEvent1 INT NULL,
HarmfulEvent2 INT NULL,
HarmfulEvent3 INT NULL,
HarmfulEvent4 INT NULL,
MostHarmfulEvent INT NULL,
RoadContourCurves INT NULL,
RoadContourGrade INT NULL,
ApproachOvertakingTurn INT NULL,
LightingCondition INT NULL,
RoadDescription INT NULL,
RoadCondition INT NULL,
WeatherCondition1 INT NULL,
WeatherCondition2 INT NULL,
FatalTimeNotified DATETIME NULL,
FatalTimeArrivedAtScene DATETIME NULL,
FatalTimeArrivedAtHospital DATETIME NULL,
FatalTrafficControlDeviceFunctioning INT NULL,
FatalRespondingServiceName nVarChar(50) NULL,
AccidentNarrative nVarChar(max),
ApprovedBy nVarChar(30) NULL,
ApprovedByID nVarChar(30) NULL,
ApprovedByDate DATETIME NULL,
pdModifiedName nVarChar(20) NULL,
pdModifiedDate DATETIME NULL,
pdQAComplete INT NULL,
CrashSeverity_Legacy INT NULL,

CONSTRAINT PK_CASENUM PRIMARY KEY CLUSTERED( CaseTrackingID ASC)
);

CREATE NONCLUSTERED INDEX IX_CrashDate ON CRASH (AccidentDate ASC);
CREATE NONCLUSTERED INDEX IX_CrashTime ON CRASH (AccidentTime ASC);
CREATE NONCLUSTERED INDEX IX_Address1 ON CRASH (OnRoadAddress ASC, OnRoad  ASC);
CREATE NONCLUSTERED INDEX IX_Address2 ON CRASH (ReferenceIntersectingAddress ASC, ReferenceIntersectingRoad ASC);
CREATE NONCLUSTERED INDEX IX_Hwy_Milepoint ON CRASH (HighwayNumber, Milepoint ASC);
CREATE NONCLUSTERED INDEX IX_LatLong ON CRASH (Latitude ASC, Longitude ASC);
CREATE NONCLUSTERED INDEX IX_Street_Id ON CRASH (OnRoad ASC);
CREATE NONCLUSTERED INDEX IX_Intersectg_Street_Id ON CRASH (ReferenceIntersectingRoad ASC);


CREATE TABLE TrafficUnit(
CaseTrackingID nVarChar(20) NOT NULL,
TrafficUnitNumber INT NOT NULL,
HitAndRun INT NULL,
NonContact INT NULL,
Parked INT NULL,
UnlicensedDriver INT NULL,
CDLCode nVarChar(1) NULL,
DriverLicenseJurisdictionCode nVarChar(2) NULL,
PrimaryViolation nVarChar(255) NULL,
DUIIndicator INT NULL,
ViolationCode nVarChar(30) NULL,
CitationNumber nVarChar(20) NULL,
CommonCode nVarChar(5) NULL,
NameSameAsDriverIndicator INT NULL,
LicensePlateJurisdictionCode nVarChar(2) NULL,
VehicleVIN nVarChar(17) NULL,
VehicleModelYear INT NULL,
VehicleMake nVarChar(50) NULL,
VehicleModel nVarChar(50) NULL,
VehicleBodyType nVarChar(50) NULL,
VehicleColor nVarChar(30) NULL,
TowedCode INT NULL,
TowedOrganizationName nVarchar(50) NULL,
TowedLocationName nVarchar(50) NULL,
NoDamageIndicator INT NULL,
DamagePosition01Code INT NULL,
DamagePosition02Code INT NULL,
DamagePosition03Code INT NULL,
DamagePosition04Code INT NULL,
DamagePosition05Code INT NULL,
DamagePosition06Code INT NULL,
DamagePosition07Code INT NULL,
DamagePosition08Code INT NULL,
DamagePosition09Code INT NULL,
DamagePosition10Code INT NULL,
DamagePosition11Code INT NULL,
DamagePosition12Code INT NULL,
DamagePosition13Code INT NULL,
DamagePosition14Code INT NULL,
DamagePosition15Code INT NULL,
DamagePosition16Code INT NULL,
DamagePosition17Code INT NULL,
DamagePosition18Code INT NULL,
DamagePosition19Code INT NULL,
UnderCarriageDamageCode INT NULL,
NoInsuranceIndicator INT NULL,
NoProofOfInsuranceIndicator INT NULL,
SelfInsuredIndicator INT NULL,
NumberOfTrailers INT NULL,
TrailerVIN nVarChar(17) NULL,
TrailerDisablingDamage INT NULL,
VehicleDefectCondition INT NULL,
FatalCrashAvoidanceManeuver INT NULL,
FatalFireHazmatInvolvment INT NULL,
VehicleType INT NULL,
SpecialFunction INT NULL,
EmergencyLightsActivated INT NULL,
DirectionOfTravel INT NULL,
VehicleMovement INT NULL,
RoadwaySpeedLimit INT NULL,
EstimatedVehicleSpeed INT NULL,
DriverStatedSpeed INT NULL,
DriverAction1 INT NULL,
DriverAction2 INT NULL,
MostApprtHumanContrFactor1 INT NULL,
MostApprtHumanContrFactor2 INT NULL,
MostApprtHumanContrFactor3 INT NULL,
AutonomousVehicleCapability INT NULL,
DriverCededControl INT NULL,
NonMotoristType INT NULL,
NonMotoristMovement INT NULL,
NonMotoristLocation INT NULL,
NonMotoristLegOfIntersection INT NULL,
NonMotoristTypeOfDesignatedFacility INT NULL,
NonMotoristAction1 INT NULL,
NonMotoristAction2 INT NULL,
NonMotoristContrFctr1 INT NULL,
NonMotoristContrFctr2 INT NULL,
NonMotoristContrFctr3 INT NULL,
ProtectiveDevices1 INT NULL,
ProtectiveDevices2 INT NULL,
ProtectiveDevices3 INT NULL,
ProtectiveDevices4 INT NULL,


CONSTRAINT PK_TrafficUnit PRIMARY KEY CLUSTERED( CaseTrackingID ASC, TrafficUnitNumber ASC)
);


CREATE TABLE Occupant(
CaseTrackingID nVarChar(20) NOT NULL,
TrafficUnitNumber INT NOT NULL,
pdNumber INT NOT NULL,
PositionInVehicle INT NULL,
DrivingRestrictions INT NULL,
DrivingEndorsements INT NULL,
Ejection INT NULL,
EjectionPath INT NULL,
SafetyEquipmentAvailable nVarChar(2) NULL,
SafetyEquipmentUse INT NULL,
SafetyEquipmentHelmet nVarChar(2) NULL,
AirbagDeployment INT NULL,
AirbagType nVarChar(2) NULL,
InjurySeverity INT NULL,
AlcoholSuspected INT NULL,
TestedForAlcohol INT NULL,
MarijuanaSuspected INT NULL,
TestedForMarijuana INT NULL,
OtherDrugsSuspected INT NULL,
TestedForOtherDrugs INT NULL,
Age INT NULL,
PersonSexCode nVarChar(1) NULL,
DeadAtScene INT NULL,
TransportedBy INT NULL,
ExpiredDate DATETIME NULL,
ExpiredTime DATETIME NULL,
EMSTripNumber nVarChar(30) NULL,
TakenTo nVarChar(30) NULL,

CONSTRAINT PK_Occupant PRIMARY KEY CLUSTERED( CaseTrackingID ASC, TrafficUnitNumber ASC, PdNumber ASC)
);


CREATE TABLE CMV(
CaseTrackingID nVarChar(20) NOT NULL,
TrafficUnitNumber INT NOT NULL,
CarrierName nVarChar(50) NULL,
DOTNumber nVarChar(30) NULL,
OverHeight INT NULL,
OverWeight INT NULL,
OverLength INT NULL,
OverWidth INT NULL,
Permitted INT NULL,
CarrierType INT NULL,
GrossVehWeightRating INT NULL,
TotalNumberOfAxles INT NULL,
VehicleConfiguration INT NULL,
CargoBodyType INT NULL,
CrashEventSequence1 INT NULL,
CrashEventSequence2 INT NULL,
CrashEventSequence3 INT NULL,
CrashEventSequence4 INT NULL,
HazmatPlacards INT NULL,
HazmatRelease INT NULL,
HazmatCode nVarChar(4) NULL,
HazmatClass nVarChar(1) NULL,
LiquidHazmat INT NULL,

CONSTRAINT PK_CMV PRIMARY KEY CLUSTERED( CaseTrackingID ASC, TrafficUnitNumber ASC)
);


CREATE TABLE DamageProp(
CaseTrackingID nVarChar(20) NOT NULL,
OwnerNumber INT NOT NULL,
PublicPropertyDamaged INT NULL,
DamagedPropDesc nVarChar(max) NULL,


CONSTRAINT PK_DamageProp PRIMARY KEY CLUSTERED( CaseTrackingID ASC, OwnerNumber ASC)
);


CREATE TABLE Diagrams(
CaseTrackingID nVarChar(20) NOT NULL,
Image nVarChar(MAX) NULL,

CONSTRAINT PK_Diagrams PRIMARY KEY CLUSTERED( CaseTrackingID ASC)
);



CREATE TABLE Streets(
StreetId INT NOT NULL,
StreetName nVarchar(50) NULL,
CityName nVarChar(24) NULL,
CountyCode INT NULL,
PdModifiedName nVarChar(30) NULL,
PdModifiedDate DATETIME NULL,

CONSTRAINT PK_StreetId PRIMARY KEY CLUSTERED(StreetId ASC)
);

CREATE UNIQUE NONCLUSTERED INDEX IX_StreetName ON Streets(StreetName ASC);