-- ==============================================================================
-- PostgreSQL G2F and Spatial Data Schema for incheon
-- For G2F Layer Edit API Testing and 2D GIS Management
-- ==============================================================================

-- 스키마 생성 (user_lyr 테이블은 icmrb에 있음)
CREATE SCHEMA icmrb;
-- 스키마 설정 (incheon_geo 스키마 사용)

-- PostGIS 확장 생성 (존재하지 않을 경우)
CREATE EXTENSION IF NOT EXISTS postgis;



-- ==============================================================================
-- 1. 2D GIS 관리 > 배경지도 관리 (tn_basemap)
-- ==============================================================================

-- Drop table
-- DROP TABLE tn_basemap;

CREATE TABLE tn_basemap (
                            id varchar(20) NOT NULL, -- 배경지도 ID
                            title varchar(50) NULL, -- 배경지도 제목
                            source_url varchar(1000) NULL, -- 배경지도 URL
                            thumbnail_url varchar(1000) NULL, -- 배경지도 썸네일 URL
                            use_at bpchar(1) NULL, -- 배경지도 사용여부
                            frst_regist_pnttm timestamp NULL, -- 등록일시
                            frst_register_id varchar(20) NULL, -- 등록자
                            last_updt_pnttm timestamp NULL, -- 수정일시
                            last_updusr_id varchar(20) NULL, -- 수정자
                            CONSTRAINT basemap_pkey PRIMARY KEY (id)
);
COMMENT ON TABLE tn_basemap IS '3. 2D GIS 관리 > 배경지도 관리';

-- Column comments
COMMENT ON COLUMN tn_basemap.id IS '배경지도 ID';
COMMENT ON COLUMN tn_basemap.title IS '배경지도 제목';
COMMENT ON COLUMN tn_basemap.source_url IS '배경지도 URL';
COMMENT ON COLUMN tn_basemap.thumbnail_url IS '배경지도 썸네일 URL';
COMMENT ON COLUMN tn_basemap.use_at IS '배경지도 사용여부';
COMMENT ON COLUMN tn_basemap.frst_regist_pnttm IS '등록일시';
COMMENT ON COLUMN tn_basemap.frst_register_id IS '등록자';
COMMENT ON COLUMN tn_basemap.last_updt_pnttm IS '수정일시';
COMMENT ON COLUMN tn_basemap.last_updusr_id IS '수정자';

-- ==============================================================================
-- 2. user_lyr 테이블
-- ==============================================================================

CREATE TABLE icmrb.user_lyr
(
    user_lyr_id     serial4                             NOT NULL,
    user_id         varchar(50)                         NOT NULL,
    user_lyr_nm     varchar(100)                        NOT NULL,
    user_lyr_expln  text                                NULL,
    lyr_phys_nm     varchar(64)                         NOT NULL,
    lyr_srvc_prefix varchar(64)                         NOT NULL,
    lyr_srvc_nm     varchar(64)                         NOT NULL,
    lyr_clsf_cd     varchar(10)                         NOT NULL,
    cntm            int2                                NOT NULL,
    spce_ty         varchar(20)                         NOT NULL,
    crt_ymd         timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    crt_id          varchar(50)                         NOT NULL,
    chg_ymd         timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
    chg_id          varchar(50)                         NOT NULL,
    CONSTRAINT pk_user_lyr PRIMARY KEY (user_lyr_id),
    CONSTRAINT uk_lyr_phys_nm UNIQUE (lyr_phys_nm),
    CONSTRAINT uk_lyr_srvs_prefix_nm UNIQUE (lyr_srvc_prefix, lyr_srvc_nm),
    CONSTRAINT uk_user_id_lyr_nm UNIQUE (user_id, user_lyr_nm)
);
CREATE INDEX idx_user_lyr_lyr_clsf_cd ON icmrb.user_lyr USING btree (lyr_clsf_cd);
CREATE INDEX idx_user_lyr_user_id ON icmrb.user_lyr USING btree (user_id);

-- ==============================================================================
-- 3. 샘플 공간 데이터 테이블들 (테스트용)
-- ==============================================================================

-- 3.1. 버스정류장 레이어 (POINT, 4326) - 한글 컬럼명 포함
CREATE TABLE icmrb.incheon_bus_stations (
                                            gid SERIAL PRIMARY KEY,
                                            geom GEOMETRY(Point, 4326),
                                            기준일 VARCHAR(80),           -- 한글 컬럼명!
                                            정류소 VARCHAR(80),           -- 정류소명
                                            번호 NUMERIC,                -- 정류소번호
                                            id NUMERIC(19),             -- ID
                                            권역 VARCHAR(80),            -- 권역
                                            행정동 VARCHAR(80),           -- 행정동
                                            x NUMERIC,                  -- X좌표
                                            위도 NUMERIC,                -- 위도
                                            y NUMERIC,                  -- Y좌표
                                            경도 NUMERIC                 -- 경도
);

-- GiST 인덱스
CREATE INDEX sx_incheon_bus_stations_geom ON icmrb.incheon_bus_stations USING GIST (geom);

-- 3.2. 건물 레이어 (MULTIPOLYGON, 5186) - 복잡한 실제 구조
CREATE TABLE icmrb.f_fac_building_28_202507 (
                                                gid SERIAL PRIMARY KEY,
                                                geom GEOMETRY(MultiPolygon, 5186),
                                                ufid VARCHAR(28),           -- 건물 고유식별자
                                                bld_nm VARCHAR(200),        -- 건물명
                                                dong_nm VARCHAR(200),       -- 동명
                                                grnd_flr NUMERIC(10),       -- 지상층수
                                                ugrnd_flr NUMERIC(10),      -- 지하층수
                                                pnu VARCHAR(19),            -- 필지번호
                                                archarea NUMERIC(10),       -- 건축면적
                                                totalarea NUMERIC(10),      -- 연면적
                                                platarea NUMERIC(10),       -- 대지면적
                                                height NUMERIC(10),         -- 높이
                                                strct_cd VARCHAR(2),        -- 구조코드
                                                usability VARCHAR(5),       -- 용도분류
                                                bc_rat NUMERIC(10),         -- 건폐율
                                                vl_rat NUMERIC(10),         -- 용적률
                                                bldrgst_pk VARCHAR(28),     -- 건축물대장PK
                                                useapr_day VARCHAR(8),      -- 사용승인일
                                                regist_day VARCHAR(8),      -- 등록일
                                                gb_cd VARCHAR(2),           -- 구분코드
                                                viol_bd_yn VARCHAR(1),      -- 위반건물여부
                                                geoidn VARCHAR(17),         -- 지오이드N
                                                bldg_pnu VARCHAR(19),       -- 건물PNU
                                                bldg_pnu_y VARCHAR(1),      -- 건물PNU여부
                                                bld_unlice VARCHAR(1),      -- 건물무허가여부
                                                bd_mgt_sn VARCHAR(25),      -- 건물관리일련번호
                                                sgg_oid NUMERIC(10),        -- 시군구 OID
                                                col_adm_se VARCHAR(5)       -- 수집행정구역
);

-- GiST 인덱스
CREATE INDEX sx_f_fac_building_28_202507_geom ON icmrb.f_fac_building_28_202507 USING GIST (geom);

-- 3.3. 농지 레이어 (MULTIPOLYGON, 5186)
CREATE TABLE icmrb.lsmd_cont_ue101_28_202507 (
                                                 gid SERIAL PRIMARY KEY,
                                                 geom GEOMETRY(MultiPolygon, 5186),           -- MultiPolygon 타입!
                                                 mnum VARCHAR(33),                            -- 관리번호
                                                 sgg_oid NUMERIC(10),                        -- 시군구 OID (NUMERIC)
                                                 col_adm_se VARCHAR(5),                      -- 관리구분 (VARCHAR)
                                                 ntfdate VARCHAR(8),                         -- 고시일자 (VARCHAR 8자리)
                                                 alias VARCHAR(200),                         -- 별칭
                                                 remark VARCHAR(200)                         -- 비고
);

-- GiST 인덱스
CREATE INDEX sx_lsmd_cont_ue101_28_202507_geom ON icmrb.lsmd_cont_ue101_28_202507 USING GIST (geom);

-- 3.4. 테스트용 단순 Point 레이어 (4326)
CREATE TABLE icmrb.test_restaurants (
                                        gid SERIAL PRIMARY KEY,
                                        geom GEOMETRY(Point, 4326),
                                        상호 VARCHAR(200),           -- 상호명
                                        주소 VARCHAR(300),           -- 주소
                                        도로명주소 VARCHAR(300),      -- 도로명주소
                                        전화번호 VARCHAR(20),        -- 전화번호
                                        x NUMERIC,                  -- X좌표
                                        y NUMERIC,                  -- Y좌표
                                        주요메뉴 VARCHAR(200)        -- 주요메뉴
);

-- GiST 인덱스
CREATE INDEX sx_test_restaurants_geom ON icmrb.test_restaurants USING GIST (geom);

-- 3.5. 테스트용 LineString 레이어 (5186) - 도로
CREATE TABLE icmrb.test_roads (
                                  gid SERIAL PRIMARY KEY,
                                  geom GEOMETRY(LineString, 5186),
                                  road_name VARCHAR(200),      -- 도로명
                                  road_type VARCHAR(50),       -- 도로 유형
                                  length_m NUMERIC,            -- 길이(미터)
                                  width_m NUMERIC,             -- 폭(미터)
                                  surface_type VARCHAR(50),    -- 포장 유형
                                  created_date DATE            -- 생성일자
);

-- GiST 인덱스
CREATE INDEX sx_test_roads_geom ON icmrb.test_roads USING GIST (geom);

-- 3.6. 테스트용 Polygon 레이어 (5186) - 구역
CREATE TABLE icmrb.test_zones (
                                  gid SERIAL PRIMARY KEY,
                                  geom GEOMETRY(Polygon, 5186),
                                  zone_name VARCHAR(200),      -- 구역명
                                  zone_type VARCHAR(50),       -- 구역 유형
                                  area_sqm NUMERIC,            -- 면적(제곱미터)
                                  usage_type VARCHAR(50),      -- 용도
                                  regulation TEXT,             -- 규제사항
                                  manager VARCHAR(100)         -- 관리자
);

-- GiST 인덱스
CREATE INDEX sx_test_zones_geom ON icmrb.test_zones USING GIST (geom);

-- ==============================================================================
-- 4. 트리거 함수 (updated_at 자동 업데이트)
-- ==============================================================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- user_layers 테이블에 트리거 적용
CREATE TRIGGER update_user_layers_updated_at
    BEFORE UPDATE ON icmrb.user_lyr
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ==============================================================================
-- 5. 기본 데이터 삽입
-- ==============================================================================

-- 5.1. 배경지도 데이터
INSERT INTO tn_basemap (id,title,source_url,thumbnail_url,use_at,frst_regist_pnttm,frst_register_id,last_updt_pnttm,last_updusr_id) VALUES
                                                                                                                                        ('vworld:Base','일반','https://cdn.vworld.kr/2d/Base/service/{z}/{x}/{y}.png','https://cdn.vworld.kr/2d/Base/service/12/3489/1587.png','Y','2025-07-23 13:49:03.103639','admin','2025-07-23 13:49:03.103639','admin'),
                                                                                                                                        ('vworld:white','백지도','https://cdn.vworld.kr/2d/white/service/{z}/{x}/{y}.png','https://cdn.vworld.kr/2d/white/service/12/3489/1587.png','Y','2025-07-23 13:49:04.167735','admin','2025-07-23 13:49:04.167735','admin'),
                                                                                                                                        ('vworld:midnight','야간','https://cdn.vworld.kr/2d/midnight/service/{z}/{x}/{y}.png','https://cdn.vworld.kr/2d/midnight/service/12/3489/1587.png','Y','2025-07-23 13:49:05.226245','admin','2025-07-23 13:49:05.226245','admin'),
                                                                                                                                        ('vworld:Hybrid','하이브리드','https://cdn.vworld.kr/2d/Hybrid/service/{z}/{x}/{y}.png
https://cdn.vworld.kr/2d/Satellite/service/{z}/{x}/{y}.jpeg','https://cdn.vworld.kr/2d/Hybrid/service/12/3489/1587.png
https://cdn.vworld.kr/2d/Satellite/service/12/3489/1587.jpeg','Y','2025-07-23 13:49:06.147349','admin','2025-07-23 13:49:06.147349','admin'),
                                                                                                                                        ('vworld:Satellite','영상','https://cdn.vworld.kr/2d/Satellite/service/{z}/{x}/{y}.jpeg','https://cdn.vworld.kr/2d/Satellite/service/12/3489/1587.jpeg','Y','2025-07-23 13:49:08.50219','admin','2025-07-23 13:49:08.50219','admin');

-- 5.2. user_layers 테스트 데이터 (구현 계획서의 확인된 샘플 레이어)
INSERT INTO icmrb.user_lyr (
    user_lyr_id, user_id, lyr_phys_nm, lyr_srvc_prefix, lyr_srvc_nm,
    user_lyr_nm, user_lyr_expln, lyr_clsf_cd, spce_ty, cntm, crt_id, chg_id
) VALUES
-- 건물 레이어 (MULTIPOLYGON, srid: 5186)
(3, 'seungwon92', 'icmrb.f_fac_building_28_202507', 'incheon_workspace', 'building_layer',
 '인천광역시 건물 정보', '인천 지역 건축물 현황 데이터', '건물', 'MULTIPOLYGON', 5186, 'seungwon92', 'seungwon92'),

-- 버스정류장 레이어 (POINT, srid: 4326)
(4, 'seungwon92', 'icmrb.incheon_bus_stations', 'incheon_workspace', 'bus_stations_layer',
 '인천 버스정류장 정보', '인천 지역 버스정류장 위치 및 정보', '교통', 'POINT', 4326, 'seungwon92', 'seungwon92'),

-- 농지 레이어 (MULTIPOLYGON, srid: 5186)
(6, 'admin', 'icmrb.lsmd_cont_ue101_28_202507','incheon_workspace', 'farmland_layer',
 '농지 이용현황', '농업진흥지역 및 농지 관리 데이터', '용도', 'MULTIPOLYGON', 5186, 'admin' , 'admin'),

-- 테스트용 음식점 레이어 (POINT, srid: 4326)
(64, 'testuser1', 'icmrb.test_restaurants', 'test_workspace', 'restaurants_layer',
 '테스트 음식점', '테스트용 음식점 데이터', '상업', 'POINT', 4326, 'testuser1', 'testuser1'),

-- 테스트용 도로 레이어 (LINESTRING, srid: 5186)
(65, 'testuser2', 'icmrb.test_roads', 'test_workspace', 'roads_layer',
 '테스트 도로', '테스트용 도로 네트워크 데이터', '교통', 'LINESTRING', 5186, 'testuser2', 'testuser2'),

-- 테스트용 구역 레이어 (POLYGON, srid: 5186)
(66, 'admin', 'icmrb.test_zones', 'test_workspace', 'zones_layer',
 '테스트 용도구역', '테스트용 용도지역 및 구역 데이터', '용도', 'POLYGON', 5186, 'admin', 'admin');

-- 5.3. 버스정류장 샘플 데이터 (POINT)
INSERT INTO icmrb.incheon_bus_stations (
    geom, 기준일, 정류소, 번호, id, 권역, 행정동, x, 위도, y, 경도
) VALUES
-- 인천 지역 실제 좌표 기반 샘플 데이터
(ST_SetSRID(ST_MakePoint(126.7052, 37.4563), 4326), '20250825', '인천터미널', 12001, 1, '중구', '항동1가', 126.7052, 37.4563, 126.7052, 37.4563),
(ST_SetSRID(ST_MakePoint(126.7158, 37.4601), 4326), '20250825', '차이나타운', 12002, 2, '중구', '선린동', 126.7158, 37.4601, 126.7158, 37.4601),
(ST_SetSRID(ST_MakePoint(126.6932, 37.4449), 4326), '20250825', '월미도', 12003, 3, '중구', '월미동', 126.6932, 37.4449, 126.6932, 37.4449),
(ST_SetSRID(ST_MakePoint(126.7315, 37.4388), 4326), '20250825', '인천대학교', 12004, 4, '연수구', '송도동', 126.7315, 37.4388, 126.7315, 37.4388),
(ST_SetSRID(ST_MakePoint(126.6518, 37.4206), 4326), '20250825', '송도국제도시', 12005, 5, '연수구', '송도동', 126.6518, 37.4206, 126.6518, 37.4206);

-- 5.4. 테스트용 음식점 데이터
INSERT INTO icmrb.test_restaurants (
    geom, 상호, 주소, 도로명주소, 전화번호, x, y, 주요메뉴
) VALUES
-- 사용된 좌표 변환 (Web Mercator -> WGS84 대략 변환)
(ST_SetSRID(ST_MakePoint(126.8, 37.2), 4326), '쌀국수명가', '경기도 안양시 동안구 평안동 905-3', '경기도 안양시 동안구 귀인로195번길 33', '031-2345-6789', 126.8, 37.2, '아시아>베트남 쌀국수'),
(ST_SetSRID(ST_MakePoint(126.75, 37.25), 4326), '인천갈비', '인천광역시 중구 신포동 123-45', '인천광역시 중구 자유공원남로 15', '032-1234-5678', 126.75, 37.25, '한식>갈비구이'),
(ST_SetSRID(ST_MakePoint(126.72, 37.28), 4326), '송도피자', '인천광역시 연수구 송도동 456-78', '인천광역시 연수구 센트럴로 200', '032-3456-7890', 126.72, 37.28, '양식>이탈리안'),
(ST_SetSRID(ST_MakePoint(126.68, 37.22), 4326), '월미짜장', '인천광역시 중구 월미동 789-01', '인천광역시 중구 월미문화로 81', '032-4567-8901', 126.68, 37.22, '중식>짜장면'),
(ST_SetSRID(ST_MakePoint(126.73, 37.26), 4326), '부평순대국', '인천광역시 부평구 부평동 234-56', '인천광역시 부평구 부평대로 45', '032-5678-9012', 126.73, 37.26, '한식>국밥류');

-- 5.5. 건물 샘플 데이터 (MULTIPOLYGON, 5186 좌표계)
-- Korea 2000 / Central Belt 2010 (EPSG:5186) 좌표계 사용
INSERT INTO icmrb.f_fac_building_28_202507 (
    geom, ufid, bld_nm, dong_nm, grnd_flr, ugrnd_flr, pnu, archarea, totalarea, platarea, height,
    strct_cd, usability, bc_rat, vl_rat, useapr_day, regist_day
) VALUES
-- 인천 지역 실제 건물을 모방한 테스트 데이터
(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((174500 449500, 174550 449500, 174550 449550, 174500 449550, 174500 449500)))'), 5186),
 'BUILD_001', '인천터미널빌딩', 'A동', 15, 3, '2871010100101234567', 2500, 25000, 3000, 60, '21', '04000', 60, 800, '20200315', '20200320'),

(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((174600 449600, 174670 449600, 174670 449680, 174600 449680, 174600 449600)))'), 5186),
 'BUILD_002', '차이나타운상가', 'B동', 8, 2, '2871010100101234568', 1800, 12000, 2200, 35, '21', '05000', 65, 650, '20190820', '20190825'),

(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((174400 449400, 174480 449400, 174480 449490, 174400 449490, 174400 449400)))'), 5186),
 'BUILD_003', '월미도관광센터', 'C동', 5, 1, '2871010100101234569', 1200, 5500, 1500, 25, '11', '06000', 70, 400, '20210510', '20210515');

-- 5.6. 농지 샘플 데이터 (MULTIPOLYGON, 5186 좌표계)
INSERT INTO icmrb.lsmd_cont_ue101_28_202507 (
    geom, mnum, sgg_oid, col_adm_se, ntfdate, alias, remark
) VALUES
-- 농업진흥지역을 모방한 테스트 데이터
(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((173000 448000, 173500 448000, 173500 448500, 173000 448500, 173000 448000)))'), 5186),
 'FARM_28_001', 28710, 'A001', '20230401', '강화군 농지 1구역', '농업진흥지역'),

(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((173600 448600, 174100 448600, 174100 449100, 173600 449100, 173600 448600)))'), 5186),
 'FARM_28_002', 28710, 'A001', '20230401', '강화군 농지 2구역', '농업진흥지역'),

(ST_SetSRID(ST_GeomFromText('MULTIPOLYGON(((174200 449200, 174700 449200, 174700 449700, 174200 449700, 174200 449200)))'), 5186),
 'FARM_28_003', 28710, 'A001', '20230401', '강화군 농지 3구역', '농업보호구역');

-- 5.7. 테스트용 도로 데이터 (LINESTRING, 5186 좌표계)
INSERT INTO icmrb.test_roads (
    geom, road_name, road_type, length_m, width_m, surface_type, created_date
) VALUES
-- 인천 지역 도로를 모방한 테스트 데이터
(ST_SetSRID(ST_GeomFromText('LINESTRING(174000 449000, 174200 449100, 174500 449200, 174800 449300)'), 5186),
 '자유로', '일반국도', 1200, 25, '아스팔트', '2023-05-15'),

(ST_SetSRID(ST_GeomFromText('LINESTRING(174300 449500, 174400 449300, 174600 449100, 174900 448900)'), 5186),
 '인천대로', '광역시도', 800, 20, '아스팔트', '2023-06-20'),

(ST_SetSRID(ST_GeomFromText('LINESTRING(174100 449800, 174300 449600, 174500 449400, 174700 449200)'), 5186),
 '송도중앙로', '시도', 600, 15, '아스팔트', '2023-07-10');

-- 5.8. 테스트용 구역 데이터 (POLYGON, 5186 좌표계)
INSERT INTO icmrb.test_zones (
    geom, zone_name, zone_type, area_sqm, usage_type, regulation, manager
) VALUES
-- 용도지역을 모방한 테스트 데이터
(ST_SetSRID(ST_GeomFromText('POLYGON((173500 448500, 174000 448500, 174000 449000, 173500 449000, 173500 448500))'), 5186),
 '송도국제업무지구', '특별계획지구', 250000, '상업지역', '층고제한: 최대 50층', '인천경제자유구역청'),

(ST_SetSRID(ST_GeomFromText('POLYGON((174100 449100, 174600 449100, 174600 449600, 174100 449600, 174100 449100))'), 5186),
 '중구 원도심재생구역', '도시재생구역', 180000, '주거지역', '건폐율: 최대 60%', '인천광역시 중구청'),

(ST_SetSRID(ST_GeomFromText('POLYGON((174700 449700, 175200 449700, 175200 450200, 174700 450200, 174700 449700))'), 5186),
 '부평산업단지', '일반산업단지', 320000, '공업지역', '용적률: 최대 300%', '인천광역시 부평구청');

-- ==============================================================================
-- 6. 시퀀스 값 조정 (다음 INSERT를 위해)
-- ==============================================================================
-- 각 테이블의 시퀀스를 현재 최대값 이후로 설정
SELECT setval('icmrb.user_layers_layer_id_seq', 100, true);
SELECT setval('icmrb.incheon_bus_stations_gid_seq', 10, true);
SELECT setval('icmrb.f_fac_building_28_202507_gid_seq', 10, true);
SELECT setval('icmrb.lsmd_cont_ue101_28_202507_gid_seq', 10, true);
SELECT setval('icmrb.test_restaurants_gid_seq', 10, true);
SELECT setval('icmrb.test_roads_gid_seq', 10, true);
SELECT setval('icmrb.test_zones_gid_seq', 10, true);
