-- 3.5.31 Power
-- 1.0.31 Power.TechnologicalConnection
-- Добавляем справочник причин отказа в принятии заявки.
create table TCApplicationRefusalReasons
(
	TCApplicationRefusalReasonID	int IDENTITY(1,1),
	Description		nvarchar(200) NOT NULL,	
	PRIMARY KEY (TCApplicationRefusalReasonID ASC)
)
alter table TCApplications add RefusalReasonID int

ALTER TABLE TCApplications
	ADD FOREIGN KEY (RefusalReasonID)
	REFERENCES TCApplicationRefusalReasons  (TCApplicationRefusalReasonID)

GO
-- Храним тип заявки физлица в заявке а не в Individuals
alter table TCApplications add ApplicantType int
GO
-- Выставляем типы заявок на основе имеющихся данных.
Update TCApplications Set ApplicantType = 0
FROM TCApplications inner join LegalEntities on TCApplications.LegalEntityID = LegalEntities.LegalEntityID
inner join Individuals on LegalEntities.LegalEntityID = Individuals.IndividualID and LegalEntities.IsIndividual = 1 
WHERE Individuals.IsNoneDomestic is null or Individuals.IsNoneDomestic = 0

Update TCApplications Set ApplicantType = 1
FROM TCApplications inner join LegalEntities on TCApplications.LegalEntityID = LegalEntities.LegalEntityID
inner join Individuals on LegalEntities.LegalEntityID = Individuals.IndividualID and LegalEntities.IsIndividual = 1 
WHERE Individuals.IsNoneDomestic = 1 and Individuals.IERegCertificateNumber is null

Update TCApplications Set ApplicantType = 2
FROM TCApplications inner join LegalEntities on TCApplications.LegalEntityID = LegalEntities.LegalEntityID
inner join Individuals on LegalEntities.LegalEntityID = Individuals.IndividualID and LegalEntities.IsIndividual = 1 
WHERE Individuals.IsNoneDomestic = 1 and Individuals.IERegCertificateNumber is not null
GO
-- Убираем ненужную колонку
ALTER TABLE Individuals drop column IsNoneDomestic
GO
-- Добавляем поле для хранения значения обозначающего растояние от объекта заявителя до соответствующего объекта сетевой организации.
ALTER TABLE TCSpecs add DistanceType int
GO
CREATE TABLE TCArchiveChanges
(
	ID				int IDENTITY(1,1),
    Date            datetime NOT NULL,
	UserID			int NOT NULL, 
	TableName		nvarchar(50) NOT NULL, 
	RecordID		int NOT NULL,
	ModificationType	nvarchar(10) 
    PRIMARY KEY (ID)
)
GO
ALTER TABLE TCArchiveChanges
	ADD FOREIGN KEY (UserID)
	REFERENCES Users (UserID)
GO
-- Добавляем, что не хватает по Строительно-монтажным работам.
alter table TCActs add WorksNeed	bit
alter table TCActs add WorksPlanDate	datetime
alter table TCActs add WorksTabEditorID		int
GO
alter table TCActs 
	add foreign key(WorksTabEditorID) 
		references Персонал(Код_работника)
GO
Update TCActs set WorksNeed = 1