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
)

USE [CarDB]
GO

-- тип  покраски ТС
-- например: обычная, хамелеон, матовая, металик ...
CREATE TABLE [dbo].[tblVehOwnDICColoringType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value   nvarchar(16),      -- название типа покраски
	Comment nvarchar(max) NULL -- некое дополнительное пояснение	
)
GO

-- цвет покраски ТС - его описание 2-3 словами
-- например: кофе с молоком, navy, bianco, testa rossa, niger...
CREATE TABLE [dbo].[tblVehOwnDICColor]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value   nvarchar(32),      -- название цвета, 2-мя - 3-мя словами 
	Comment nvarchar(max) NULL, -- некое дополнительное пояснение	
)
GO

-- тип владельца транспортного средства
-- например, физическое или юридическое лицо
CREATE TABLE [dbo].[tblVehOwnDICVehicleOwnerType]
(
	Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	Value   nvarchar(32) NOT NULL, -- это надпись которую видит (сразу) пользователь в GUI 
	Comment nvarchar(max) NULL     -- некое средней длины дополнительное пояснение
)
GO

-- страховая компания и ее параметры
CREATE TABLE [dbo].[tblVehOwnBOBKaskoInsurer]
(
	Id          uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
	CompanyName nvarchar(64) NOT NULL, -- это название компании которое видит пользователь в GUI 
	PhoneNumber nvarchar(12) NULL,     -- номер телефона call центра страховщика
	Comment     nvarchar(max) NULL     -- некое средней длины дополнительное пояснение	
)
GO

-- автомобильная страховка связана с владением (N страховок к 1-му владению)
CREATE TABLE [dbo].[tblVehOwnBOBKaskoInsurance]
(
    Id        uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
    Comment   nvarchar(max) NULL,    -- некое средней длины дополнительное пояснение	
	
    Number    nvarchar(32) NOT NULL, -- номер страховки
    Insurer   uniqueidentifier REFERENCES tblVehOwnBOBKaskoInsurer(Id) NOT NULL, -- страховая компания
    BeginDate date NULL, -- дата начала страхового периода
    EndDate   date NULL, -- дата окончания страхового периода
    
    -- владение во время которого застраховали
    Ownership uniqueidentifier NOT NULL, -- REFERENCES tblVehOwnBOBVehicleOwnership(Id)
    LastChangeDate datetime2
)
GO

-- владение конкретным экземпляром транспортного средства (N владений к 1-му ТС)
CREATE TABLE [dbo].[tblVehOwnBOBVehicleOwnership]
(
    Id      uniqueidentifier PRIMARY KEY DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
    Comment nvarchar(max) NULL, -- коментарий к этому владению 	
	
    Vehicle        uniqueidentifier NOT NULL, -- транспортное средство REFERENCES tblVehicleBOBVehicle(Id)
	
    VehRegNumber   nvarchar(10) NULL, -- государственный регистрационный номер ТС
    PassprotNumber nvarchar(10) NULL, -- номер пасспорта транспортного средатва (номер ПТС)
    BeginDate      date NOT NULL,     -- дата начала владения транспортным средством
    EndDate        date NULL,         -- дата окончания владения транспортным средством
    Color          uniqueidentifier REFERENCES tblVehOwnDICColor(Id) NOT NULL,        -- цвет покраски ТС 
    ColoringType   uniqueidentifier REFERENCES tblVehOwnDICColoringType(Id) NOT NULL, -- тип  покраски ТС  
        
    VehOwnerType   uniqueidentifier REFERENCES tblVehOwnDICVehicleOwnerType(Id) NOT NULL,-- тип владельца
    VehicleOwner   uniqueidentifier NOT NULL, -- владелец
    
    CurrKaskoInsurance uniqueidentifier REFERENCES tblVehOwnBOBKaskoInsurance(Id) NULL,-- текущая страх-ка
	
    DealerCompany uniqueidentifier NULL, -- ссылка на компанию, продавшую машину
	
    SybaseIdAc_car numeric(16,0),
    LastChangeDate datetime2
)
GO