use tempdb;

if object_id('o_SOCRBASE') is not null
	drop table o_SOCRBASE;

create table o_SOCRBASE(
	level tinyint null,
	SCNAME varchar( 10 ) null,
	SOCRNAME varchar( 29 ) null,
	KOD_T_ST int null
);


if object_id('o_KLADR') is not null
	drop table o_KLADR;

create table o_KLADR(
	NAME varchar( 40 ) null,
	SOCR varchar( 10 ) null,
	CODE char( 13 ) null,
	[INDEX] char( 6 ) null,
	GNINMB char( 4 ) null,
	UNO CHAR( 4 ) null,
	OCATD char( 11 ) null,
	[STATUS] char( 1 ) null
);

if object_id('o_STREET') is not null
	drop table o_STREET;

create table o_STREET(
	NAME varchar( 40 ) null,
	SOCR varchar( 10 ) null,
	CODE char( 17 ) null,
	[INDEX] char( 6 ) null,
	GNINMB char( 4 ) null,
	UNO CHAR( 4 ) null,
	OCATD char( 11 ) null
);


if object_id('o_ALTNAMES') is not null
	drop table o_ALTNAMES;

create table o_ALTNAMES(
	OLDCODE varchar( 19 ) null,
	NEWCODE varchar( 19 ) null,
	LEVEL tinyint null
);

create table Prefix(
	PrefixID smallint not null,
	level tinyint not null,
	Name varchar( 10 ) not null,
	FullName varchar( 29 ) not null,
	Dot bit constraint DF_Prefix_Dot default( 0 )
);



create table Kladr(
	KladrID int  identity( 1 ,  1 ),
	ParentID int null,
	PrefixID smallint not null,
	Name varchar( 40 ) not null,
	Code varchar( 19 ) not null,
	PostIndex char( 6 ) null,
	GNINMB char( 4 ) null,
	UNO char( 4 ) null,
	OCATD char( 11 ) null,
	Status char( 1 ) null
);

GO

create unique clustered index clst on Prefix(PrefixID);
create unique clustered index clst on Kladr(ParentID, KladrID);
create unique index IX_Code on Kladr(Code);

alter table Prefix add constraint PK_Prefix primary key nonclustered(PrefixID);


alter table Kladr add constraint PK_Kladr primary key nonclustered(KladrID);
alter table Kladr add constraint FK_Kladr_Prefix foreign key(PrefixID) references Prefix(PrefixID);
alter table Kladr add constraint FK_Kladr_Kladr foreign key(ParentID) references Kladr(KladrID);

-- индексы на временные таблицы
create unique clustered index clst on o_KLADR(Code)
create unique clustered index clst on o_STREET(Code)

GO
-- префиксы
insert into Prefix(PrefixID, level, Name, FullName)
select
	KOD_T_ST, level, SCNAME, SOCRNAME
from tempdb..o_SOCRBASE


-- уровень 1
insert into Kladr(ParentID, PrefixID, Name, Code, PostIndex, GNINMB, UNO, OCATD, Status)
select
	null, P.PrefixID, K.NAME, K.CODE, K.[INDEX], K.GNINMB, K.UNO, K.OCATD, K.Status
from tempdb..o_KLADR K
	join Prefix P on K.SOCR = P.Name and P.level =  1 
where 
	CODE like '__00000000000'
	and not exists(
		select * from Kladr where Code = K.CODE
	)
order by K.Code

-- уровень 2
insert into Kladr(ParentID, PrefixID, Name, Code, PostIndex, GNINMB, UNO, OCATD, Status)
select
	PK.KladrID, P.PrefixID, K.NAME, K.CODE, K.[INDEX], K.GNINMB, K.UNO, K.OCATD, K.Status
from tempdb..o_KLADR K
	join Prefix P on K.SOCR = P.Name and P.level =  2 
	join Kladr PK on left(K.Code,  2 ) = left(PK.Code,  2 ) and right(PK.Code,  2 ) = '00'
where 
	K.CODE like '_____00000000' and K.CODE not like '__00000000000'
	and not exists(
		select * from Kladr where Code = K.CODE
	)
order by K.Code


-- уровень 3
insert into Kladr(ParentID, PrefixID, Name, Code, PostIndex, GNINMB, UNO, OCATD, Status)
select
	PK.KladrID, P.PrefixID, K.NAME, K.CODE, K.[INDEX], K.GNINMB, K.UNO, K.OCATD, K.Status
from tempdb..o_KLADR K
	join Prefix P on K.SOCR = P.Name and P.level =  3 
	join Kladr PK on left(K.Code,  5 ) = left(PK.Code,  5 ) and right(PK.Code,  2 ) = '00'
where 
	K.CODE like '________00000' and K.CODE not like '_____00000000' and K.CODE not like '__00000000000'
	and not exists(
		select * from Kladr where Code = K.CODE
	)
order by K.Code

-- уровень 4
insert into Kladr(ParentID, PrefixID, Name, Code, PostIndex, GNINMB, UNO, OCATD, Status)
select
	PK.KladrID, P.PrefixID, K.NAME, K.CODE, K.[INDEX], K.GNINMB, K.UNO, K.OCATD, K.Status
from tempdb..o_KLADR K
	join Prefix P on K.SOCR = P.Name and P.level =  4 
	join Kladr PK on left(K.Code,  8 ) = left(PK.Code,  8 ) and right(PK.Code,  2 ) = '00' and K.CODE != PK.Code
where 
	K.CODE like '___________00' and K.CODE not like '________00000' 
		and K.CODE not like '_____00000000' and K.CODE not like '__00000000000'
	and not exists(
		select * from Kladr where Code = K.CODE
	)
order by K.Code

-- уровень 5
	insert into Kladr(ParentID, PrefixID, Name, Code, PostIndex, GNINMB, UNO, OCATD, Status)
	select
		PK.KladrID, P.PrefixID, S.NAME, S.CODE, S.[INDEX], S.GNINMB, S.UNO, S.OCATD, null
	from tempdb..o_STREET S
		join Prefix P on S.SOCR = P.Name and P.level =  5 
		join Kladr PK on left(S.Code,  11 ) = left(PK.Code,  11 ) and right(PK.Code,  2 ) = '00' and S.CODE != PK.Code
	where 
		right(S.Code,  2 ) = '00'
		and not exists(
			select * from Kladr where Code = S.CODE
		)
order by S.Code