-- ============================================================================
-- G2F 레이어 편집 이력 테이블 DDL
-- ============================================================================
-- 용도: 레이어 편집 이력 관리 (Added, Modified, Deleted)
-- 명명 규칙: {원본테이블명}_h
-- 이력 상태 코드 (STTS_CD):
--   'C' - Create (Added)
--   'U' - Update (Modified)
--   'D' - Delete (Deleted)
-- ============================================================================

-- ============================================================================
-- 1. 테스트용 본 테이블 생성 예시
-- ============================================================================

-- 스키마 생성 (없는 경우)
CREATE SCHEMA IF NOT EXISTS test_schema;

-- 본 테이블 생성 (Point 타입 예시)
CREATE TABLE IF NOT EXISTS test_schema.test_layer (
    gid SERIAL PRIMARY KEY,
    geom GEOMETRY(Point, 4326),  -- WGS84 좌표계
    name VARCHAR(100),
    description TEXT,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- 공간 인덱스 생성
CREATE INDEX IF NOT EXISTS idx_test_layer_geom
ON test_schema.test_layer USING GIST(geom);

-- 테스트 데이터 삽입
INSERT INTO test_schema.test_layer (geom, name, description, category)
VALUES
    (ST_SetSRID(ST_MakePoint(126.77, 37.30), 4326), '초기 데이터 1', '테스트용 초기 데이터', 'INITIAL'),
    (ST_SetSRID(ST_MakePoint(126.78, 37.31), 4326), '초기 데이터 2', '테스트용 초기 데이터', 'INITIAL'),
    (ST_SetSRID(ST_MakePoint(126.79, 37.32), 4326), '초기 데이터 3', '테스트용 초기 데이터', 'INITIAL');


-- ============================================================================
-- 1-1. USER 레이어 메타데이터 등록 (test_layer 연결)
-- ============================================================================

-- user_lyr 테이블 생성 (없는 경우)
-- 주의: 이미 테이블이 존재하면 이 부분은 무시됩니다
-- CREATE TABLE IF NOT EXISTS icmrb.user_lyr (
--     user_lyr_id SERIAL PRIMARY KEY,
--     user_id VARCHAR(50),
--     lyr_phys_nm VARCHAR(200) NOT NULL,  -- 스키마.테이블 형식
--     cntm INTEGER,                       -- SRID (좌표계)
--     spce_ty VARCHAR(50),                -- 공간 타입 (POINT, LINESTRING, POLYGON 등)
--     created_at TIMESTAMP DEFAULT NOW()
-- );

-- 테스트용 USER 레이어 메타데이터 등록
-- 기존 user_lyr_id=1이 있으면 업데이트, 없으면 삽입
-- 실제 테이블 구조에 맞춰 모든 필수 컬럼 포함
-- INSERT INTO icmrb.user_lyr (
--     user_lyr_id,
--     user_id,
--     user_lyr_nm,
--     user_lyr_expln,
--     lyr_phys_nm,
--     lyr_srvc_prefix,
--     lyr_srvc_nm,
--     lyr_clsf_cd,
--     cntm,
--     spce_ty,
--     crt_ymd,
--     crt_id,
--     chg_ymd,
--     chg_id
-- )
-- VALUES (
--     1,
--     'test_user',
--     '이력테스트레이어',
--     '레이어 편집 이력 기능 테스트용 레이어',
--     'test_schema.test_layer',
--     'test',
--     'test_layer',
--     'U',  -- User 레이어
--     4326,
--     'POINT',
--     NOW(),
--     'test_user',
--     NOW(),
--     'test_user'
-- )
-- ON CONFLICT (user_lyr_id) DO UPDATE
-- SET user_lyr_nm = EXCLUDED.user_lyr_nm,
--     user_lyr_expln = EXCLUDED.user_lyr_expln,
--     lyr_phys_nm = EXCLUDED.lyr_phys_nm,
--     lyr_srvc_prefix = EXCLUDED.lyr_srvc_prefix,
--     lyr_srvc_nm = EXCLUDED.lyr_srvc_nm,
--     lyr_clsf_cd = EXCLUDED.lyr_clsf_cd,
--     cntm = EXCLUDED.cntm,
--     spce_ty = EXCLUDED.spce_ty,
--     chg_ymd = NOW(),
--     chg_id = EXCLUDED.chg_id;


-- ============================================================================
-- 2. 이력 테이블 생성 (본 테이블과 동일 구조 + 이력 관리 컬럼)
-- ============================================================================

-- 이력 일련번호 시퀀스 생성
CREATE SEQUENCE IF NOT EXISTS test_schema.test_layer_h_hstry_sn_seq;

-- 이력 테이블 생성 (실제 표준에 맞춤)
CREATE TABLE IF NOT EXISTS test_schema.test_layer_h (
    -- 본 테이블과 동일한 컬럼들
    gid INTEGER,                               -- 원본 gid (Added의 경우 NULL 가능)
    geom GEOMETRY(Point, 4326),                -- 본 테이블과 동일한 geometry 타입
    name VARCHAR(100),
    description TEXT,
    category VARCHAR(50),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,

    -- 이력 관리 컬럼
    stts_cd VARCHAR(1) NOT NULL,               -- 이력 상태 코드: 'C', 'U', 'D'
    hstry_sn NUMERIC(22) DEFAULT nextval('test_schema.test_layer_h_hstry_sn_seq') NOT NULL,  -- 이력일련번호
    frst_reg_id VARCHAR(20) DEFAULT 'ADMIN' NOT NULL,     -- 최초등록아이디
    frst_reg_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,  -- 최초등록일시
    last_mdfcn_id VARCHAR(20) DEFAULT 'ADMIN' NOT NULL,   -- 최종수정아이디
    last_mdfcn_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,  -- 최종수정일시

    -- PK: 이력일련번호만 (gid는 NULL 가능하므로 제외)
    CONSTRAINT pk_test_layer_h PRIMARY KEY (hstry_sn)
);

-- 이력 테이블 공간 인덱스
CREATE INDEX IF NOT EXISTS idx_test_layer_h_geom
ON test_schema.test_layer_h USING GIST(geom);

-- 이력 상태 코드 인덱스
CREATE INDEX IF NOT EXISTS idx_test_layer_h_stts_cd
ON test_schema.test_layer_h(stts_cd);

-- gid 인덱스 (특정 피처의 이력 조회용)
CREATE INDEX IF NOT EXISTS idx_test_layer_h_gid
ON test_schema.test_layer_h(gid);

-- STTS_CD 제약 조건 (C, U, D만 허용)
ALTER TABLE test_schema.test_layer_h
DROP CONSTRAINT IF EXISTS chk_test_layer_h_stts_cd;

ALTER TABLE test_schema.test_layer_h
ADD CONSTRAINT chk_test_layer_h_stts_cd
CHECK (stts_cd IN ('C', 'U', 'D'));

-- 이력 테이블 설명
COMMENT ON TABLE test_schema.test_layer_h IS '레이어 편집 이력 테이블 (Added/Modified/Deleted)';
COMMENT ON COLUMN test_schema.test_layer_h.gid IS 'GID';
COMMENT ON COLUMN test_schema.test_layer_h.stts_cd IS '이력 상태 코드: C(Create), U(Update), D(Delete)';
COMMENT ON COLUMN test_schema.test_layer_h.hstry_sn IS '이력일련번호';
COMMENT ON COLUMN test_schema.test_layer_h.frst_reg_id IS '최초등록아이디';
COMMENT ON COLUMN test_schema.test_layer_h.frst_reg_dt IS '최초등록일시';
COMMENT ON COLUMN test_schema.test_layer_h.last_mdfcn_id IS '최종수정아이디';
COMMENT ON COLUMN test_schema.test_layer_h.last_mdfcn_dt IS '최종수정일시';

