---------------------------------------------------
-- 건물/층 관리
---------------------------------------------------
--CREATE TABLE InbuBuilding
CREATE TABLE CmBuilding
(
	SiteId				int  NOT NULL ,					-- 현장 고유번호
	BuildingId          int  identity(1,1) NOT NULL ,	-- 빌딩 고유번호
	Name				nvarchar(50)  NOT NULL ,		-- 빌딩 이름
	
	PRIMARY KEY (SiteId ASC , BuildingId ASC)
);
DBCC CHECKIDENT( CmBuilding, RESEED, 1 )

CREATE TABLE CmFloor
(
	SiteId				int  NOT NULL ,					-- 현장 고유번호
	BuildingId          int  NOT NULL ,					-- 빌딩 고유번호
	FloorId             int  identity(1,1) NOT NULL ,	-- 층 고유번호
	Name				nvarchar(50)  NOT NULL ,		-- 층 이름

	PRIMARY KEY (SiteId ASC , BuildingId ASC , FloorId ASC)
);
DBCC CHECKIDENT( CmFloor, RESEED, 1 )

CREATE TABLE CmZone
(
	SiteId				int  NOT NULL ,					-- 현장 고유번호
	BuildingId          int  NOT NULL ,					-- 빌딩 고유번호
	FloorId             int  NOT NULL ,					-- 층 고유번호
	ZoneId				int  identity(1,1) NOT NULL ,	-- 존 고유번호
	Name				nvarchar(50)  NOT NULL ,		-- 존 이름
	
	PRIMARY KEY (SiteId ASC , BuildingId ASC , FloorId ASC , ZoneId ASC)
);
DBCC CHECKIDENT( CmZone, RESEED, 1 )

ALTER TABLE CmBuilding
	ADD CONSTRAINT FK__CmBuilding__CmSite FOREIGN KEY (SiteId) REFERENCES CmSite(SiteId)
;

ALTER TABLE CmFloor
	ADD CONSTRAINT FK__CmFloor_CmBuilding FOREIGN KEY (SiteId , BuildingId) REFERENCES CmBuilding(SiteId , BuildingId)
;

ALTER TABLE CmZone
	ADD CONSTRAINT FK__CmZone__CmBuilding FOREIGN KEY (SiteId , BuildingId) REFERENCES CmBuilding(SiteId , BuildingId)
;

ALTER TABLE CmZone
	ADD CONSTRAINT FK__CmZone__CmFloor FOREIGN KEY (SiteId , BuildingId , FloorId) REFERENCES CmFloor(SiteId , BuildingId , FloorId)
;

--ALTER TABLE CmBuilding
--	ADD CONSTRAINT FK__CmBuilding__CmFile FOREIGN KEY (SiteId,FileId) REFERENCES CmFile(SiteId,FileId)
--;

--ALTER TABLE CmFloor
--	ADD CONSTRAINT FK__CmFloor__CmFile FOREIGN KEY (SiteId,FileId) REFERENCES CmFile(SiteId,FileId)
--;
