use [test]
GO

CREATE TABLE [dbo].[tblKladrDICCountyType]
(
	Id				uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value           nvarchar(40) NOT NULL, -- название района
	Comment         nvarchar(max) NULL,    -- некое средней длины дополнительное пояснение
)
GO

-- тип географического объекта
-- обозначает на какую таблицу ссылка: tblKladrTOBCountry, tblKladrTOBRegion, tblKladrTOBSettlement
CREATE TABLE [dbo].[tblKladrDICObjectType]
(
	Id        uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value     nvarchar(16)  NULL, -- тип гео объекта
	Comment   nvarchar(max) NULL  -- некое дополнительное пояснение
)
GO

-- тип региона внутри страны (первичные данные берутся из КЛАДР)
CREATE TABLE [dbo].[tblKladrDICRegionType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nchar(16) NOT NULL, -- тип региона - строка
	Comment nvarchar(max) NULL, -- некое средней длины дополнительное пояснение
)
GO

-- тип населенного пункта (первичные данные берутся из КЛАДР)
-- например: городок, деревня, аул, аал, заимка
CREATE TABLE [dbo].[tblKladrDICSettlementType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nchar(16) NOT NULL, -- тип населенного пункта - строка
	Comment nvarchar(max) NULL -- некое средней длины дополнительное пояснение
)
GO

-- тип улицы (первичные данные берутся из КЛАДР)
-- например: улица, переулок, проезд, площадь и т.п.
CREATE TABLE [dbo].[tblKladrDICStreetType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nchar(16) NOT NULL, -- тип улицы - строка
	Comment nvarchar(max) NULL  -- некое средней длины дополнительное пояснение
)
GO

-- страна
CREATE TABLE [dbo].[tblKladrTOBCountry]
(
	Id          uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	
	Value              nchar(40) NOT NULL, -- название страны
	Comment            nvarchar(max) NULL, -- некое средней длины дополнительное пояснение
	
	PartOfPhoneNumber  nchar(4)            -- номер страны внутри номера телефона
)
GO

-- регионы страны, максимального размера, то есть области/штаты, но не районы/дистрикты
CREATE TABLE [tblKladrTOBRegion]
(
	Id                uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value             nchar(40) NOT NULL, -- название региона
	Comment           nvarchar(max) NULL,   -- некое средней длины дополнительное пояснение
	Country           uniqueidentifier REFERENCES tblKladrTOBCountry(Id)    NULL, -- страна в которой регион      
	RegionType        uniqueidentifier REFERENCES tblKladrDICRegionType(Id) NULL, -- тип региона       
	PartOfAutoNumber  nchar(2),       -- номер региона внутри номера транспортного средства
	KLADRCode         nchar(17) NULL,  -- код для сопоставления с КЛАДР(здесь полные 17 цифр)
	IsActual		  bit, -- признак актуальности объекта
	PartOfPhoneNumber nchar(7)  NULL,  -- номер региона внутри номера телефона	
	TimeZone		  uniqueidentifier,
	LastChangeDate	  datetime2
)
GO

CREATE TABLE [dbo].[tblKladrTOBCounty]
(
	Id                uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value             nvarchar(40) NOT NULL, -- название района
	Comment           nvarchar(max) NULL,    -- некое средней длины sдополнительное пояснение
	Region			  uniqueidentifier references tblKladrTOBRegion(Id) NOT NULL, -- ссылка на регион
	CountyType		  uniqueidentifier references tblKladrDICCountyType(Id), --тип района
	KLADRCode		  nchar(17) NULL,  -- код для сопоставления с КЛАДР(здесь полные 17 цифр)
	PostalIndex		  nchar(6) NULL, -- почтовый индекс
	IsActual		  bit, -- признак актуальности объекта
	LastChangeDate datetime2
)
GO

-- населенные пункты
CREATE TABLE [dbo].[tblKladrTOBSettlement]
(
	Id                uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value             nvarchar(40) NOT NULL, -- название населенного пункта 
	Comment           nvarchar(max) NULL,    -- некое средней длины дополнительное пояснение
	Region            uniqueidentifier REFERENCES tblKladrTOBRegion(Id) NOT NULL,         -- регион где нас. пункт      
	County            uniqueidentifier REFERENCES tblKladrTOBCounty(Id) NOT NULL,         -- район где нас. пункт      
	SettlementType    uniqueidentifier REFERENCES tblKladrDICSettlementType(Id) NULL, -- тип нас. пункта
	KLADRCode         nchar(17) NULL,         -- код для сопоставления с КЛАДР(здесь полные 17 цифр)
	PostalIndex		  nchar(6) NULL, -- почтовый индекс
	IsActual		  bit, -- признак актуальности объекта
	IsFake			  bit, -- признак фиктивности
	IsRAT			  bit, -- в поселении имеется присутствие РАТ
	LastChangeDate datetime2
)
GO

-- улица существующая в некотором населенном пункте (первоначально согласно кладр) 
CREATE TABLE [dbo].[tblKladrTOBStreet]
(
	Id    uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value nvarchar(40) NOT NULL, -- название населенного пункта 
	Settlement uniqueidentifier REFERENCES tblKladrTOBSettlement(Id) NULL, -- нас. пункт где улица
	StreetType uniqueidentifier REFERENCES tblKladrDICStreetType(Id) NULL, -- тип улицы (ул, пер...)
	KLADRCode  nchar(17) NULL, -- код для сопоставления с КЛАДР(здесь полные 17 цифр)
	PostalIndex nchar(6) NULL, -- почтовый индекс
	LastChangeDate datetime2	
)
GO

USE [CarDB]
GO

-- тип корпуса ТС, соответствует модели машины как 1 к 1
-- например, "седан", "хэтчбэк", "универсал", "джип" ...
CREATE TABLE [dbo].[tblVehicleDICBodyType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nvarchar(32) NOT NULL, -- это надпись которую видит (сразу) пользователь в GUI 
	Comment nvarchar(max) NULL     -- некое средней длины дополнительное пояснение
)

/*
Список типов тормозов транспортного средства
*/
CREATE TABLE [dbo].[tblVehicleDICBrakeType]
(
	Id		uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value	nvarchar(64)  NULL, 
	Comment nvarchar(max) null
)

-- Список типов брэндов: неизвестен, отечественный, зарубежный
CREATE TABLE [dbo].[tblVehicleDICBrandType]
(
	Id			uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value		nvarchar(32) NOT NULL, 
	Comment		nvarchar(max) NULL     
)

/*
Варианты расположения цилиндров
*/
CREATE TABLE [dbo].[tblVehicleDICCylindersArrangement]
(
	Id		uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value	nvarchar(32)  NULL, 
	Comment nvarchar(max) null
)

-- ведущие колеса транспортного средства: передние, задние, полный привод
CREATE TABLE [dbo].[tblVehicleDICDriveWheelsType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nchar(32) NOT NULL, -- ведущие колеса транспортного средства - строка
	Comment nvarchar(max) NULL  -- некое дополнительное пояснение
)

CREATE TABLE [dbo].[tblVehicleDICEngineArrangement]
(
	Id		uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value	nvarchar(32)  NULL, 
	Comment nvarchar(max) NULL
)

-- тип двигателя: карбюраторный, инжекторный, дизельный
CREATE TABLE [dbo].[tblVehicleDICEngineType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nchar(64) NOT NULL, -- тип двигателя - строка
	Comment nvarchar(max) NULL  -- некое дополнительное пояснение
)

-- тип и марка топлива
CREATE TABLE [dbo].[tblVehicleDICFuelType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nchar(64) NOT NULL, -- тип и марка топлива - строка
	Comment nvarchar(max) NULL  -- некое дополнительное пояснение
)

-- где произведено ТС: неизвестно, в россии, за рубежом
CREATE TABLE [dbo].[tblVehicleDICRussianProduction]
(
	Id			uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value		nvarchar(32) NOT NULL, 
	Comment		nvarchar(max) NULL     
)

/*
Список вариантов подвесок
*/
CREATE TABLE [dbo].[tblVehicleDICSuspension]
(
	Id		uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value	nvarchar(max)  NULL, 
	Comment nvarchar(max) null
)

-- тип коробки передач: ручная, автоматическая ...
CREATE TABLE [dbo].[tblVehicleDICTransmissionType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nchar(128) NOT NULL, -- тип коробки передач - строка
	Comment nvarchar(max) NULL  -- некое дополнительное пояснение
)

-- Параметры ТС.
-- Может быть связана с tblVehicleBOBVehicle или с tblVehicleBOBModel
-- Параметры, указанные как ссылка из tblVehicleBOBVehicle, 
-- должны иметь приоритет над параметрами из tblVehicleBOBModel
CREATE TABLE [dbo].[tblVehicleBOBVehicleParams]
(
	Id		   uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	BodyType   uniqueidentifier REFERENCES tblVehicleDICBodyType(Id)   NOT NULL, -- тип корпуса машины
		
	Weigth   int NULL, -- вес машины в килограммах
	
	Length   int NULL, -- длина машины в миллиметрах
	Width    int NULL, -- длина машины в миллиметрах
	Height   int NULL, -- длина машины в миллиметрах
	
	WheelBase       int NULL, -- колесная база в миллиметрах
	FrontWheelGauge int NULL, -- колея передних колес в миллиметрах
	RearWheelGauge  int NULL, -- колея задних колес в миллиметрах
	RoadClearance   int NULL, -- клиренс в миллиметрах

	FuelType     uniqueidentifier REFERENCES tblVehicleDICFuelType(Id) NULL,-- тип и марка топлива
	GasCanBeUsed bit NULL, -- может ли машина ездить на газе

	EngineType   uniqueidentifier REFERENCES tblVehicleDICEngineType(Id)       NULL, -- тип двигла
	EngineVolume int NULL, -- объем двигателя в кубических сантиметрах
	EnginePower  int NULL, -- мощьность двигателя в лошадиных силах
	
	Transmission uniqueidentifier REFERENCES tblVehicleDICTransmissionType(Id) NULL, -- тип коробки
	
	DriveWheels  uniqueidentifier REFERENCES tblVehicleDICDriveWheelsType(Id)  NULL, -- колеса ведущие
	
	PlacesCount  int NULL, -- количество мест
	DoorsCount   int NULL,  -- количество дверей
	
	Torque nvarchar(64) null, -- Крутящий момент, Н•м при об/мин
	IsSupercharge bit null, -- Наличие наддува
	
	ValvesForCylinder int null, -- Количество клапанов на цилиндр
	IsABS bit null, -- Наличие АБС
	IsESP bit null, -- Система курсовой устойчивости
	Acceleration nvarchar(24) null, -- Разгон с места до 100 км/ч, с
	MaxSpeed int null, -- Максимальная скорость, км/ч
	FuelExpense nvarchar(24) null, -- Средний условный расход топлива, л/100 км
	
	FrontBrake uniqueidentifier REFERENCES tblVehicleDICBrakeType(Id)       NULL, -- Тормоза передние
	BackBrake uniqueidentifier REFERENCES tblVehicleDICBrakeType(Id)       NULL, -- Тормоза задние
	CylindersArrangement uniqueidentifier REFERENCES tblVehicleDICCylindersArrangement(Id) NULL, 
	EngineArrangement uniqueidentifier REFERENCES tblVehicleDICEngineArrangement(Id)       NULL, 
	FrontSuspension uniqueidentifier REFERENCES tblVehicleDICSuspension null, -- Передняя подвеска
	BackSuspension uniqueidentifier REFERENCES tblVehicleDICSuspension null, -- Задняя подвеска
	LastChangeDate datetime2
)

-- марка ТС это название ее производителя, 1 марка соотв. N моделям транспротного средства
-- например, "BMW", "Audi", "Maserati", "Saleen", "Koenigsegg", "Тагаз", "SsangYoung" ...
-- если тип брэнда не понадобится (что весьма вероятно), то надо его похарчить в тиши
CREATE TABLE [dbo].[tblVehicleTOBVehicleMark]
(
	Id			uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value		nvarchar(32) NOT NULL, -- это надпись которую видит (сразу) пользователь в GUI 
	Comment		nvarchar(max) NULL,     -- некое средней длины доп. пояснение
	BrandType	uniqueidentifier references tblVehicleDICBrandType(Id) null 
)

-- класс машин или группа моделей
-- на самом деле это в простонародье как раз модель 
-- надо бы переименовать в будущем чтобы не путаться
-- например: "C", "E", "3", "A8" 
-- внимание! СООТВЕТСТВУЕТ НА САМОМ ДЕЛЕ МОДЕЛИ (В ТЕРМИНАХ auto.mail.ru)
CREATE TABLE [dbo].[tblVehicleTOBModelGroup]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value   nvarchar(32) NOT NULL, -- строка с надписью в интерфейсе
	Comment nvarchar(max) NULL,    -- некое дополнительное пояснение
	
	Mark    uniqueidentifier REFERENCES tblVehicleTOBVehicleMark(Id) NOT NULL  -- марка машины
)

-- модель машины, 1 марка машины соответствует N моделям транспортного средства
-- например, "E36 335i MT" 
-- в полное название конкретной модели входит: 
--     название корпуса (производительское, если есть, например E45), 
--     объем двигателя,
--     буквы типа двигателя (если есть),
--     остальные буквы обозначающие модификацию
-- внимание! СООТВЕТСТВУЕТ НА САМОМ ДЕЛЕ МОДИФИКАЦИИ (В ТЕРМИНАХ auto.mail.ru)
CREATE TABLE [dbo].[tblVehicleBOBModel]
(
    Id         uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	Value      nvarchar(70)                                            NOT NULL, -- название модели, полное
	Comment    nvarchar(max)                                           NULL, -- некое длинное пояснение
    ModelGroup uniqueidentifier REFERENCES tblVehicleTOBModelGroup(Id) NOT NULL, -- класс/группа моделей
	VehicleParams uniqueidentifier REFERENCES [tblVehicleBOBVehicleParams](Id) NULL, -- ссылка на параметры ТС

	-- фиг его знает почему, но оно тут :) 
	-- это придумал не я, я его хотел зпихнуть в tblVehicleBOBVehicle
	-- ну и ладно сами себе карлосы кастанеды
	IsRussianProduction uniqueidentifier REFERENCES tblVehicleDICRussianProduction(Id) NULL, -- российское производство

	LastChangeDate datetime2
)

-- серия выпуска для модели, одной модели может соответствовать много серий или ни одной 
CREATE TABLE [dbo].[tblVehicleBOBSeries]
(
    Id                uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL, 
	SeriesLabel       nvarchar(16)   NULL, -- краткое обозначение серии для ориентации в таблице
    Comment           nvarchar(max)  NULL, -- некий длинный коментарий или пояснение для серии  
    
    Model             uniqueidentifier REFERENCES tblVehicleBOBModel(Id) NOT NULL, -- модель машины
	ProdBegDate       date NOT NULL, -- дата начала производства этой серии
	ProdEndDate       date NOT NULL, -- дата конца  производства этой серии	
	VINIsReq          bit  NOT NULL, -- признак необходимости ввода VIN для этой серии
	ChassisNumIsReq   bit  NOT NULL, -- признак необходимости ввода номера шасси для этой серии
	VINRegExp         nvarchar(1024) NULL, -- строка с рег. выраженим для проверки VIN  
	ChassisNumRegExp  nvarchar(1024) NULL,  -- строка с рег. выраженим для проверки номера шасси
	LastChangeDate datetime2
)