-- ============================================
-- 02. 테이블 및 시퀀스 생성
-- 실행: DBA
-- 순서: 시퀀스 -> 기본테이블 -> 이력테이블
-- ============================================

BEGIN;

-- ============================================
-- 1. 시퀀스 생성
-- ============================================

CREATE SEQUENCE iccom.user_hstry_sq01
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;

CREATE SEQUENCE iccom.dept_hstry_sq01
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 20;

CREATE SEQUENCE iccom.popup_ntc_sq01
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- ============================================
-- 2. dept 테이블 생성
-- ============================================

CREATE TABLE iccom.dept (
    dept_cd character varying(20) NOT NULL,
    dept_stcd character varying(20) NOT NULL,
    dept_nm character varying(200) NOT NULL,
    dept_whol_nm character varying(200) NOT NULL,
    dept_eng_nm character varying(200),
    hghrk_inst_cd character varying(20),
    shghrk_inst_cd character varying(20),
    sfrnd_se_cd character varying(20),
    cycl numeric(7,0),
    rank_no numeric(4,0),
    rprs_telno character varying(11),
    addr character varying(200),
    daddr character varying(200),
    hmpg_url_addr character varying(2000),
    fxno character varying(20),
    eml_addr character varying(320),
    tmpr_ognz_yn character(1),
    vr_ognz_tr_yn character(1),
    vr_ognz_tr_up_cd character varying(20),
    frst_reg_id character varying(20) NOT NULL,
    frst_reg_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_mdfcn_id character varying(20) NOT NULL,
    last_mdfcn_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    rprs_inst_cd character varying(20),
    rprs_inst_nm character varying(200),
    road_mng_inst_cd character varying(20),
    road_mng_inst_nm character varying(200),
    fclt_mng_inst_cd character varying(20),
    fclt_mng_inst_nm character varying(200)
);

COMMENT ON TABLE iccom.dept IS '부서';
COMMENT ON COLUMN iccom.dept.dept_cd IS '부서코드';
COMMENT ON COLUMN iccom.dept.dept_stcd IS '부서상태코드';
COMMENT ON COLUMN iccom.dept.dept_nm IS '부서명';
COMMENT ON COLUMN iccom.dept.dept_whol_nm IS '부서전체명';
COMMENT ON COLUMN iccom.dept.dept_eng_nm IS '부서영문명';
COMMENT ON COLUMN iccom.dept.hghrk_inst_cd IS '최상위기관코드';
COMMENT ON COLUMN iccom.dept.shghrk_inst_cd IS '차상위기관코드';
COMMENT ON COLUMN iccom.dept.sfrnd_se_cd IS '자치단체구분코드';
COMMENT ON COLUMN iccom.dept.cycl IS '차수';
COMMENT ON COLUMN iccom.dept.rank_no IS '랭크번호';
COMMENT ON COLUMN iccom.dept.rprs_telno IS '대표전화번호';
COMMENT ON COLUMN iccom.dept.addr IS '주소';
COMMENT ON COLUMN iccom.dept.daddr IS '상세주소';
COMMENT ON COLUMN iccom.dept.hmpg_url_addr IS '홈페이지URL주소';
COMMENT ON COLUMN iccom.dept.fxno IS '팩스번호';
COMMENT ON COLUMN iccom.dept.eml_addr IS '이메일주소';
COMMENT ON COLUMN iccom.dept.tmpr_ognz_yn IS '임시조직여부';
COMMENT ON COLUMN iccom.dept.vr_ognz_tr_yn IS '가상조직트리여부';
COMMENT ON COLUMN iccom.dept.vr_ognz_tr_up_cd IS '가상조직트리상위코드';
COMMENT ON COLUMN iccom.dept.frst_reg_id IS '최초등록아이디';
COMMENT ON COLUMN iccom.dept.frst_reg_dt IS '최초등록일시';
COMMENT ON COLUMN iccom.dept.last_mdfcn_id IS '최종수정아이디';
COMMENT ON COLUMN iccom.dept.last_mdfcn_dt IS '최종수정일시';
COMMENT ON COLUMN iccom.dept.rprs_inst_cd IS '대표기관코드';
COMMENT ON COLUMN iccom.dept.rprs_inst_nm IS '대표기관명';
COMMENT ON COLUMN iccom.dept.road_mng_inst_cd IS '도로관리기관코드';
COMMENT ON COLUMN iccom.dept.road_mng_inst_nm IS '도로관리기관명';
COMMENT ON COLUMN iccom.dept.fclt_mng_inst_cd IS '시설물관리기관코드';
COMMENT ON COLUMN iccom.dept.fclt_mng_inst_nm IS '시설물관리기관명';

ALTER TABLE ONLY iccom.dept
    ADD CONSTRAINT dept_pk PRIMARY KEY (dept_cd);

-- ============================================
-- 3. user 테이블 생성
-- ============================================

CREATE TABLE iccom."user" (
    user_unq_id character varying(20) NOT NULL,
    user_id character varying(20) NOT NULL,
    user_stcd character varying(20) NOT NULL,
    user_nm character varying(100) NOT NULL,
    user_eng_nm character varying(100),
    user_whol_nm character varying(200),
    emp_no character varying(20),
    dept_cd character varying(20) NOT NULL,
    dept_nm character varying(200) NOT NULL,
    dept_whol_nm character varying(200),
    jbgd_cd character varying(20) NOT NULL,
    jbgd_nm character varying(100) NOT NULL,
    jbps_cd character varying(20),
    jbps_nm character varying(100),
    jbttl_nm character varying(100),
    gndr_cd character varying(20) NOT NULL,
    brdt character(8),
    rank_no numeric(4,0),
    hghrk_inst_cd character varying(20),
    shghrk_inst_cd character varying(20),
    work_type_cd character varying(20),
    etc_dept_nm character varying(200),
    etc_dept_cd character varying(20),
    vhrno character varying(100),
    cn character varying(100),
    photo_path_nm character varying(300),
    ofc_telno character varying(14),
    ofc_fxno character varying(20),
    tkcg_task_cn character varying(4000),
    eml_addr character varying(320),
    otsd_eml_addr character varying(320),
    mbl_telno character varying(14),
    hmpg_url_addr character varying(2000),
    house_telno character varying(11),
    house_addr character varying(200),
    house_daddr character varying(200),
    mrg_ymd date,
    frst_reg_id character varying(20) NOT NULL,
    frst_reg_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_mdfcn_id character varying(20) NOT NULL,
    last_mdfcn_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    user_idntf_no character varying(100),
    last_lgn_dt timestamp without time zone,
    elapr_lgn_id character varying(30),
    nxrp_id character varying(20),
    saeo_id character varying(20),
    saeo_dept_cd character varying(20),
    saeo_cvlcpt_dept_cd character varying(20),
    sgg_cvlcpt_rcpt_pic_id character varying(8),
    rprs_inst_cd character varying(20),
    rprs_inst_nm character varying(200),
    road_mng_inst_cd character varying(20),
    road_mng_inst_nm character varying(200),
    fclt_mng_inst_cd character varying(20),
    fclt_mng_inst_nm character varying(200)
);

COMMENT ON TABLE iccom."user" IS '사용자';
COMMENT ON COLUMN iccom."user".user_unq_id IS '사용자고유아이디';
COMMENT ON COLUMN iccom."user".user_id IS '사용자아이디';
COMMENT ON COLUMN iccom."user".user_stcd IS '사용자상태코드';
COMMENT ON COLUMN iccom."user".user_nm IS '사용자명';
COMMENT ON COLUMN iccom."user".user_eng_nm IS '사용자영문명';
COMMENT ON COLUMN iccom."user".user_whol_nm IS '사용자전체명';
COMMENT ON COLUMN iccom."user".emp_no IS '사원번호';
COMMENT ON COLUMN iccom."user".dept_cd IS '부서코드';
COMMENT ON COLUMN iccom."user".dept_nm IS '부서명';
COMMENT ON COLUMN iccom."user".dept_whol_nm IS '부서전체명';
COMMENT ON COLUMN iccom."user".jbgd_cd IS '직급코드';
COMMENT ON COLUMN iccom."user".jbgd_nm IS '직급명';
COMMENT ON COLUMN iccom."user".jbps_cd IS '직위코드';
COMMENT ON COLUMN iccom."user".jbps_nm IS '직위명';
COMMENT ON COLUMN iccom."user".jbttl_nm IS '직책명';
COMMENT ON COLUMN iccom."user".gndr_cd IS '성별코드';
COMMENT ON COLUMN iccom."user".brdt IS '생년월일';
COMMENT ON COLUMN iccom."user".rank_no IS '랭크번호';
COMMENT ON COLUMN iccom."user".hghrk_inst_cd IS '최상위기관코드';
COMMENT ON COLUMN iccom."user".shghrk_inst_cd IS '차상위기관코드';
COMMENT ON COLUMN iccom."user".work_type_cd IS '근무유형코드';
COMMENT ON COLUMN iccom."user".etc_dept_nm IS '기타부서명';
COMMENT ON COLUMN iccom."user".etc_dept_cd IS '기타부서코드';
COMMENT ON COLUMN iccom."user".vhrno IS '차량번호';
COMMENT ON COLUMN iccom."user".cn IS 'CN';
COMMENT ON COLUMN iccom."user".photo_path_nm IS '사진경로명';
COMMENT ON COLUMN iccom."user".ofc_telno IS '사무실전화번호';
COMMENT ON COLUMN iccom."user".ofc_fxno IS '사무실팩스번호';
COMMENT ON COLUMN iccom."user".tkcg_task_cn IS '담당업무내용';
COMMENT ON COLUMN iccom."user".eml_addr IS '이메일주소';
COMMENT ON COLUMN iccom."user".otsd_eml_addr IS '외부이메일주소';
COMMENT ON COLUMN iccom."user".mbl_telno IS '휴대전화번호';
COMMENT ON COLUMN iccom."user".hmpg_url_addr IS '홈페이지URL주소';
COMMENT ON COLUMN iccom."user".house_telno IS '집전화번호';
COMMENT ON COLUMN iccom."user".house_addr IS '집주소';
COMMENT ON COLUMN iccom."user".house_daddr IS '집상세주소';
COMMENT ON COLUMN iccom."user".mrg_ymd IS '결혼일자';
COMMENT ON COLUMN iccom."user".frst_reg_id IS '최초등록아이디';
COMMENT ON COLUMN iccom."user".frst_reg_dt IS '최초등록일시';
COMMENT ON COLUMN iccom."user".last_mdfcn_id IS '최종수정아이디';
COMMENT ON COLUMN iccom."user".last_mdfcn_dt IS '최종수정일시';
COMMENT ON COLUMN iccom."user".user_idntf_no IS '사용자식별번호';
COMMENT ON COLUMN iccom."user".last_lgn_dt IS '최종로그인일시';
COMMENT ON COLUMN iccom."user".elapr_lgn_id IS '전자결재로그인아이디';
COMMENT ON COLUMN iccom."user".nxrp_id IS '세외수입아이디';
COMMENT ON COLUMN iccom."user".saeo_id IS '새올아이디';
COMMENT ON COLUMN iccom."user".saeo_dept_cd IS '새올부서코드';
COMMENT ON COLUMN iccom."user".saeo_cvlcpt_dept_cd IS '새올민원부서코드';
COMMENT ON COLUMN iccom."user".sgg_cvlcpt_rcpt_pic_id IS '시군구민원접수담당자아이디';
COMMENT ON COLUMN iccom."user".rprs_inst_cd IS '대표기관코드';
COMMENT ON COLUMN iccom."user".rprs_inst_nm IS '대표기관명';
COMMENT ON COLUMN iccom."user".road_mng_inst_cd IS '도로관리기관코드';
COMMENT ON COLUMN iccom."user".road_mng_inst_nm IS '도로관리기관명';
COMMENT ON COLUMN iccom."user".fclt_mng_inst_cd IS '시설물관리기관코드';
COMMENT ON COLUMN iccom."user".fclt_mng_inst_nm IS '시설물관리기관명';

ALTER TABLE ONLY iccom."user"
    ADD CONSTRAINT user_pk PRIMARY KEY (user_id);

-- ============================================
-- 4. dept_hstry 테이블 생성
-- ============================================

CREATE TABLE iccom.dept_hstry (
    dept_hstry_sn numeric(22,0) DEFAULT nextval('iccom.dept_hstry_sq01'::regclass) NOT NULL,
    dept_cd character varying(20) NOT NULL,
    dept_stcd character varying(20),
    dept_nm character varying(200),
    dept_whol_nm character varying(200),
    dept_eng_nm character varying(200),
    hghrk_inst_cd character varying(20),
    shghrk_inst_cd character varying(20),
    sfrnd_se_cd character varying(20),
    cycl numeric,
    rank_no numeric,
    rprs_telno character varying(11),
    addr character varying(200),
    daddr character varying(200),
    hmpg_url_addr character varying(2000),
    fxno character varying(20),
    eml_addr character varying(320),
    tmpr_ognz_yn character(1),
    vr_ognz_tr_yn character(1),
    vr_ognz_tr_up_cd character varying(20),
    frst_reg_id character varying(20) NOT NULL,
    frst_reg_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_mdfcn_id character varying(20) NOT NULL,
    last_mdfcn_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    cngyp_cd character varying(20) NOT NULL,
    rprs_inst_cd character varying(20),
    rprs_inst_nm character varying(200),
    road_mng_inst_cd character varying(20),
    road_mng_inst_nm character varying(200),
    fclt_mng_inst_cd character varying(20),
    fclt_mng_inst_nm character varying(200)
);

COMMENT ON TABLE iccom.dept_hstry IS '부서이력';
COMMENT ON COLUMN iccom.dept_hstry.dept_hstry_sn IS '부서이력일련번호';
COMMENT ON COLUMN iccom.dept_hstry.dept_cd IS '부서코드';
COMMENT ON COLUMN iccom.dept_hstry.dept_stcd IS '부서상태코드';
COMMENT ON COLUMN iccom.dept_hstry.dept_nm IS '부서명';
COMMENT ON COLUMN iccom.dept_hstry.dept_whol_nm IS '부서전체명';
COMMENT ON COLUMN iccom.dept_hstry.dept_eng_nm IS '부서영문명';
COMMENT ON COLUMN iccom.dept_hstry.hghrk_inst_cd IS '최상위기관코드';
COMMENT ON COLUMN iccom.dept_hstry.shghrk_inst_cd IS '차상위기관코드';
COMMENT ON COLUMN iccom.dept_hstry.sfrnd_se_cd IS '자치단체구분코드';
COMMENT ON COLUMN iccom.dept_hstry.cycl IS '차수';
COMMENT ON COLUMN iccom.dept_hstry.rank_no IS '랭크번호';
COMMENT ON COLUMN iccom.dept_hstry.rprs_telno IS '대표전화번호';
COMMENT ON COLUMN iccom.dept_hstry.addr IS '주소';
COMMENT ON COLUMN iccom.dept_hstry.daddr IS '상세주소';
COMMENT ON COLUMN iccom.dept_hstry.hmpg_url_addr IS '홈페이지URL주소';
COMMENT ON COLUMN iccom.dept_hstry.fxno IS '팩스번호';
COMMENT ON COLUMN iccom.dept_hstry.eml_addr IS '이메일주소';
COMMENT ON COLUMN iccom.dept_hstry.tmpr_ognz_yn IS '임시조직여부';
COMMENT ON COLUMN iccom.dept_hstry.vr_ognz_tr_yn IS '가상조직트리여부';
COMMENT ON COLUMN iccom.dept_hstry.vr_ognz_tr_up_cd IS '가상조직트리상위코드';
COMMENT ON COLUMN iccom.dept_hstry.frst_reg_id IS '최초등록아이디';
COMMENT ON COLUMN iccom.dept_hstry.frst_reg_dt IS '최초등록일시';
COMMENT ON COLUMN iccom.dept_hstry.last_mdfcn_id IS '최종수정아이디';
COMMENT ON COLUMN iccom.dept_hstry.last_mdfcn_dt IS '최종수정일시';
COMMENT ON COLUMN iccom.dept_hstry.cngyp_cd IS '변경유형코드';
COMMENT ON COLUMN iccom.dept_hstry.rprs_inst_cd IS '대표기관코드';
COMMENT ON COLUMN iccom.dept_hstry.rprs_inst_nm IS '대표기관명';
COMMENT ON COLUMN iccom.dept_hstry.road_mng_inst_cd IS '도로관리기관코드';
COMMENT ON COLUMN iccom.dept_hstry.road_mng_inst_nm IS '도로관리기관명';
COMMENT ON COLUMN iccom.dept_hstry.fclt_mng_inst_cd IS '시설물관리기관코드';
COMMENT ON COLUMN iccom.dept_hstry.fclt_mng_inst_nm IS '시설물관리기관명';

ALTER TABLE ONLY iccom.dept_hstry
    ADD CONSTRAINT dept_hstry_pk PRIMARY KEY (dept_hstry_sn);

CREATE INDEX dept_hstry_ix01 ON iccom.dept_hstry USING btree (dept_cd, frst_reg_dt DESC);
CREATE INDEX dept_hstry_ix02 ON iccom.dept_hstry USING btree (frst_reg_dt DESC);

-- ============================================
-- 5. user_hstry 테이블 생성
-- ============================================

CREATE TABLE iccom.user_hstry (
    user_hstry_sn numeric(22,0) DEFAULT nextval('iccom.user_hstry_sq01'::regclass) NOT NULL,
    user_unq_id character varying(20) NOT NULL,
    user_id character varying(20),
    user_stcd character varying(20),
    user_nm character varying(100),
    user_eng_nm character varying(100),
    user_whol_nm character varying(200),
    emp_no character varying(20),
    dept_cd character varying(20),
    dept_nm character varying(200),
    dept_whol_nm character varying(200),
    jbgd_cd character varying(20),
    jbgd_nm character varying(100),
    jbps_cd character varying(20),
    jbps_nm character varying(100),
    jbttl_nm character varying(100),
    gndr_cd character varying(20),
    brdt character(8),
    rank_no numeric,
    hghrk_inst_cd character varying(20),
    shghrk_inst_cd character varying(20),
    work_type_cd character varying(20),
    etc_dept_nm character varying(200),
    etc_dept_cd character varying(20),
    vhrno character varying(100),
    cn character varying(100),
    photo_path_nm character varying(300),
    ofc_telno character varying(14),
    ofc_fxno character varying(20),
    tkcg_task_cn character varying(4000),
    eml_addr character varying(320),
    otsd_eml_addr character varying(320),
    mbl_telno character varying(14),
    hmpg_url_addr character varying(2000),
    house_telno character varying(11),
    house_addr character varying(200),
    house_daddr character varying(200),
    mrg_ymd date,
    frst_reg_id character varying(20) NOT NULL,
    frst_reg_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_mdfcn_id character varying(20) NOT NULL,
    last_mdfcn_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    cngyp_cd character varying(20) NOT NULL,
    last_lgn_dt timestamp without time zone,
    user_idntf_no character varying(100),
    elapr_lgn_id character varying(30),
    nxrp_id character varying(20),
    saeo_id character varying(20),
    saeo_dept_cd character varying(20),
    saeo_cvlcpt_dept_cd character varying(20),
    sgg_cvlcpt_rcpt_pic_id character varying(8),
    rprs_inst_cd character varying(20),
    rprs_inst_nm character varying(200),
    road_mng_inst_cd character varying(20),
    road_mng_inst_nm character varying(200),
    fclt_mng_inst_cd character varying(20),
    fclt_mng_inst_nm character varying(200)
);

COMMENT ON TABLE iccom.user_hstry IS '사용자이력';
COMMENT ON COLUMN iccom.user_hstry.user_hstry_sn IS '사용자이력일련번호';
COMMENT ON COLUMN iccom.user_hstry.user_unq_id IS '사용자고유아이디';
COMMENT ON COLUMN iccom.user_hstry.user_id IS '사용자아이디';
COMMENT ON COLUMN iccom.user_hstry.user_stcd IS '사용자상태코드';
COMMENT ON COLUMN iccom.user_hstry.user_nm IS '사용자명';
COMMENT ON COLUMN iccom.user_hstry.user_eng_nm IS '사용자영문명';
COMMENT ON COLUMN iccom.user_hstry.user_whol_nm IS '사용자전체명';
COMMENT ON COLUMN iccom.user_hstry.emp_no IS '사원번호';
COMMENT ON COLUMN iccom.user_hstry.dept_cd IS '부서코드';
COMMENT ON COLUMN iccom.user_hstry.dept_nm IS '부서명';
COMMENT ON COLUMN iccom.user_hstry.dept_whol_nm IS '부서전체명';
COMMENT ON COLUMN iccom.user_hstry.jbgd_cd IS '직급코드';
COMMENT ON COLUMN iccom.user_hstry.jbgd_nm IS '직급명';
COMMENT ON COLUMN iccom.user_hstry.jbps_cd IS '직위코드';
COMMENT ON COLUMN iccom.user_hstry.jbps_nm IS '직위명';
COMMENT ON COLUMN iccom.user_hstry.jbttl_nm IS '직책명';
COMMENT ON COLUMN iccom.user_hstry.gndr_cd IS '성별코드';
COMMENT ON COLUMN iccom.user_hstry.brdt IS '생년월일';
COMMENT ON COLUMN iccom.user_hstry.rank_no IS '랭크번호';
COMMENT ON COLUMN iccom.user_hstry.hghrk_inst_cd IS '최상위기관코드';
COMMENT ON COLUMN iccom.user_hstry.shghrk_inst_cd IS '차상위기관코드';
COMMENT ON COLUMN iccom.user_hstry.work_type_cd IS '근무유형코드';
COMMENT ON COLUMN iccom.user_hstry.etc_dept_nm IS '기타부서명';
COMMENT ON COLUMN iccom.user_hstry.etc_dept_cd IS '기타부서코드';
COMMENT ON COLUMN iccom.user_hstry.vhrno IS '차량번호';
COMMENT ON COLUMN iccom.user_hstry.cn IS 'CN';
COMMENT ON COLUMN iccom.user_hstry.photo_path_nm IS '사진경로명';
COMMENT ON COLUMN iccom.user_hstry.ofc_telno IS '사무실전화번호';
COMMENT ON COLUMN iccom.user_hstry.ofc_fxno IS '사무실팩스번호';
COMMENT ON COLUMN iccom.user_hstry.tkcg_task_cn IS '담당업무내용';
COMMENT ON COLUMN iccom.user_hstry.eml_addr IS '이메일주소';
COMMENT ON COLUMN iccom.user_hstry.otsd_eml_addr IS '외부이메일주소';
COMMENT ON COLUMN iccom.user_hstry.mbl_telno IS '휴대전화번호';
COMMENT ON COLUMN iccom.user_hstry.hmpg_url_addr IS '홈페이지URL주소';
COMMENT ON COLUMN iccom.user_hstry.house_telno IS '집전화번호';
COMMENT ON COLUMN iccom.user_hstry.house_addr IS '집주소';
COMMENT ON COLUMN iccom.user_hstry.house_daddr IS '집상세주소';
COMMENT ON COLUMN iccom.user_hstry.mrg_ymd IS '결혼일자';
COMMENT ON COLUMN iccom.user_hstry.frst_reg_id IS '최초등록아이디';
COMMENT ON COLUMN iccom.user_hstry.frst_reg_dt IS '최초등록일시';
COMMENT ON COLUMN iccom.user_hstry.last_mdfcn_id IS '최종수정아이디';
COMMENT ON COLUMN iccom.user_hstry.last_mdfcn_dt IS '최종수정일시';
COMMENT ON COLUMN iccom.user_hstry.cngyp_cd IS '변경유형코드';
COMMENT ON COLUMN iccom.user_hstry.last_lgn_dt IS '최종로그인일시';
COMMENT ON COLUMN iccom.user_hstry.user_idntf_no IS '사용자식별번호';
COMMENT ON COLUMN iccom.user_hstry.elapr_lgn_id IS '전자결재로그인아이디';
COMMENT ON COLUMN iccom.user_hstry.nxrp_id IS '세외수입아이디';
COMMENT ON COLUMN iccom.user_hstry.saeo_id IS '새올아이디';
COMMENT ON COLUMN iccom.user_hstry.saeo_dept_cd IS '새올부서코드';
COMMENT ON COLUMN iccom.user_hstry.saeo_cvlcpt_dept_cd IS '새올민원부서코드';
COMMENT ON COLUMN iccom.user_hstry.sgg_cvlcpt_rcpt_pic_id IS '시군구민원접수담당자아이디';
COMMENT ON COLUMN iccom.user_hstry.rprs_inst_cd IS '대표기관코드';
COMMENT ON COLUMN iccom.user_hstry.rprs_inst_nm IS '대표기관명';
COMMENT ON COLUMN iccom.user_hstry.road_mng_inst_cd IS '도로관리기관코드';
COMMENT ON COLUMN iccom.user_hstry.road_mng_inst_nm IS '도로관리기관명';
COMMENT ON COLUMN iccom.user_hstry.fclt_mng_inst_cd IS '시설물관리기관코드';
COMMENT ON COLUMN iccom.user_hstry.fclt_mng_inst_nm IS '시설물관리기관명';

ALTER TABLE ONLY iccom.user_hstry
    ADD CONSTRAINT user_hstry_pk PRIMARY KEY (user_hstry_sn);

CREATE INDEX user_hstry_ix01 ON iccom.user_hstry USING btree (user_unq_id, frst_reg_dt DESC);
CREATE INDEX user_hstry_ix02 ON iccom.user_hstry USING btree (frst_reg_dt DESC);

-- ============================================
-- 6. popup_ntc 테이블 생성
-- ============================================

CREATE TABLE iccom.popup_ntc (
    popup_ntc_sn numeric(20,0) DEFAULT nextval('iccom.popup_ntc_sq01'::regclass) NOT NULL,
    sys_cd character varying(20) NOT NULL,
    ttl character varying(200) NOT NULL,
    cn text NOT NULL,
    pstg_bgng_ymd date NOT NULL,
    pstg_end_ymd date NOT NULL,
    url_addr character varying(2000),
    sort_seq numeric(10,0) NOT NULL,
    use_yn character(1) DEFAULT 'Y'::bpchar NOT NULL,
    frst_reg_id character varying(20) NOT NULL,
    frst_reg_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    last_mdfcn_id character varying(20) NOT NULL,
    last_mdfcn_dt timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
);

COMMENT ON TABLE iccom.popup_ntc IS '팝업공지';
COMMENT ON COLUMN iccom.popup_ntc.popup_ntc_sn IS '팝업공지일련번호';
COMMENT ON COLUMN iccom.popup_ntc.sys_cd IS '시스템코드';
COMMENT ON COLUMN iccom.popup_ntc.ttl IS '제목';
COMMENT ON COLUMN iccom.popup_ntc.cn IS '내용';
COMMENT ON COLUMN iccom.popup_ntc.pstg_bgng_ymd IS '게시시작일자';
COMMENT ON COLUMN iccom.popup_ntc.pstg_end_ymd IS '게시종료일자';
COMMENT ON COLUMN iccom.popup_ntc.url_addr IS 'URL주소';
COMMENT ON COLUMN iccom.popup_ntc.sort_seq IS '정렬순서';
COMMENT ON COLUMN iccom.popup_ntc.use_yn IS '사용여부';
COMMENT ON COLUMN iccom.popup_ntc.frst_reg_id IS '최초등록아이디';
COMMENT ON COLUMN iccom.popup_ntc.frst_reg_dt IS '최초등록일시';
COMMENT ON COLUMN iccom.popup_ntc.last_mdfcn_id IS '최종수정아이디';
COMMENT ON COLUMN iccom.popup_ntc.last_mdfcn_dt IS '최종수정일시';

ALTER TABLE ONLY iccom.popup_ntc
    ADD CONSTRAINT popup_ntc_pk PRIMARY KEY (popup_ntc_sn);

COMMIT;

-- 확인
SELECT 'Tables created successfully' as result;
