EXEC sp_rename 'TCApplications.TCApplicationTemlateID', 'TCApplicationTemplateID', 'COLUMN'
GO
ALTER TABLE TCApplications
	ADD FOREIGN KEY (ConsumerID)
	REFERENCES Потребитель (Код_потребителя)
	ON DELETE CASCADE
GO


CREATE TABLE ConsumerClasses
(
	ConsumerClassID int identity(1,1),
        Description	nvarchar(100) NOT NULL,
	PRIMARY KEY (ConsumerClassID)
)
GO
SET IDENTITY_INSERT ConsumerClasses ON
INSERT INTO ConsumerClasses(ConsumerClassID,Description) VALUES(1,'Бытовой потребитель')
INSERT INTO ConsumerClasses(ConsumerClassID,Description) VALUES(2,'Суммарная мощность <= 750 кВА')
INSERT INTO ConsumerClasses(ConsumerClassID,Description) VALUES(3,'Суммарная мощность > 750 кВА')
SET IDENTITY_INSERT ConsumerClasses OFF
GO
ALTER TABLE TCApplications
	ADD FOREIGN KEY (TCApplicationTemplateID)
	REFERENCES ConsumerClasses(ConsumerClassID)
GO
ALTER TABLE TCApplicationAttachmentTypes
	ADD FOREIGN KEY (TCApplicationTemplateID)
	REFERENCES ConsumerClasses(ConsumerClassID)
GO	
SET IDENTITY_INSERT Характеристики_полей ON
IF NOT EXISTS (SELECT 1 FROM Характеристики_полей WHERE  Код_характеристики = 434 ) 
	INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения) 
	VALUES (434,'Справочник_кабелей','ImaxW',NULL,NULL,'I$_maxW$~','Максимально допустимый ток зимой','А')

IF NOT EXISTS (SELECT 1 FROM Характеристики_полей WHERE  Код_характеристики = 435 ) 
	INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения) 
	VALUES (435,'TCApplicationCauses','Description',NULL,NULL,'Описание', NULL,NULL)

IF NOT EXISTS (SELECT 1 FROM Характеристики_полей WHERE  Код_характеристики = 436 ) 
	INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения) 
	VALUES (436,'TCApplicationAttachmentTypes','TCApplicationTemplateID','Description','ConsumerClasses','Класс потребителя',NULL,NULL)

IF NOT EXISTS (SELECT 1 FROM Характеристики_полей WHERE  Код_характеристики = 437 ) 
	INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения) 
	VALUES (437,'TCApplicationAttachmentTypes','AttachmentTypeName',NULL,NULL,'Наименование приложения',NULL,NULL)

IF NOT EXISTS (SELECT 1 FROM Характеристики_полей WHERE  Код_характеристики = 438 ) 
	INSERT INTO Характеристики_полей(Код_характеристики,Имя_таблицы,Имя_поля,Используемое_поле,Используемая_таблица,Обозначение,Описание,Единица_измерения) 
	VALUES (438,'TCApplicationAttachmentTypes','IsMandatory',NULL,NULL,'Является обязательным',NULL,NULL)
SET IDENTITY_INSERT Характеристики_полей OFF
GO
-- Решила, что имена у документов и типов должны быть! 
UPDATE TCApplicationAttachmentTypes SET AttachmentTypeName = '' WHERE AttachmentTypeName is NULL
GO
ALTER TABLE TCApplicationAttachmentTypes ALTER COLUMN AttachmentTypeName nvarchar(200) NOT NULL
GO
UPDATE TCApplicationAttachments SET AttachmentName = '' WHERE AttachmentName is NULL
GO
ALTER TABLE TCApplicationAttachments ALTER COLUMN AttachmentName  nvarchar(200) NOT NULL
GO
SET IDENTITY_INSERT TCApplicationAttachmentTypes ON
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 1)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (1,1,'Свидетельство о праве собственности',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 3)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (3,2,'Схема планировочной огранизации земельного участка',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 4)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (4,3,'Документ, подтвержающий полномочия на подписание договора',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 5)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (5,3,'Договор аренды',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 6)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (6,3,'Акт отвода земельного участка',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 7)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (7,3,'Выписка из государственного кадастра недвижимости',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 8)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (8,3,'Постановление администрации о предоставлении земельного участка под застройку',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 9)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (9,3,'Акт согласования величины технологической и аварийной брони',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 10)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (10,2,'План расположения энергопринимающих устройств',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 11)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (11,2,'Однолинейная схема электрических сетей заявителя, присоединяемых к электрическим сетям сетевой организации',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 12)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (12,2,'Свидетельство о государственной регистрации',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 13)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (13,2,'Свидетельство о постановке на учет в налоговом органе',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 14)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (14,2,'Устав(положение) юридического лица',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 15)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (15,2,'Свидетельство об индивидуальном предпринимательстве',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 18)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (18,1,'План расположения энергопринимающего устройства',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 19)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (19,1,'Проект модульной схемы присоединения энергопринимающих устройств заказчика к электрическим сетям исполнителя',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 20)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (20,1,'Общегражданский паспорт подключаемого абонента',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 21)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (21,1,'Заводской паспорт трехфазного оборудования',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 22)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (22,2,'Документ, подтверждающий полномочия на подписание договора',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 23)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (23,2,'Договор аренды',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 24)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (24,2,'Акт отвода земельного участка',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 25)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (25,2,'Выписка из государственного кадастра недвижимости',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 26)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (26,3,'Свидетельство о праве собственности',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 27)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (27,2,'Свидетельство о праве собственности',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 28)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (28,2,'Постановление администрации о предоставлении земельного участка под застройку',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 29)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (29,3,'Однолинейная схема электрических сетей заявителя, присоединяемых к электрическим сетям сетевой организации',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 30)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (30,3,'Перечень и мощность энергопринимающих устройств, которые могут быть присоединены к устройствам противоаварийной автоматики',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 31)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (31,3,'Свидетельство о государственной регистрации',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 32)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (32,3,'Свидетельство о постановке на учет в налоговом органе',1)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 33)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (33,3,'Устав(положение) юридического лица',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 34)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (34,3,'Свидетельство об индивидуальном предпринимательстве',0)
IF NOT EXISTS (SELECT 1 FROM TCApplicationAttachmentTypes WHERE TCApplicationAttachmentTypeID = 35)  INSERT INTO TCApplicationAttachmentTypes(TCApplicationAttachmentTypeID,TCApplicationTemplateID,AttachmentTypeName,IsMandatory) VALUES (35,3,'План расположения энергопринимающих устройств',1)
SET IDENTITY_INSERT TCApplicationAttachmentTypes OFF