-- ## iitp DB Schemas - Initial setup - Creation and Delete if tables exists 
-- ## ver 0.0.5 last updated data : 2025.08.18
-- ## Only for PostgreSQL
-- ## Open API Admin 관리용 Admin 서비스 데이터용 테이블 생성 스크립트 
-- ## 


-- ################################################
-- ## ADMIN 시스템 테이블 생성 
-- ################################################

-- #### IITP Admin Account(user) 정보 테이블  ####

-- # public.sys_adm_account definition

DROP TABLE IF EXISTS public.sys_adm_account;


CREATE TABLE public.sys_adm_account (
	adm_id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- system id, 고유 식별자 (자동 증가)
	login_id VARCHAR(128) NOT NULL,  -- admin login id
	password char(60) NOT NULL,	 -- admin login password (bcrypt Hassing)
	name VARCHAR(90) NOT NULL, -- admin name
	
	roles varchar(8) NOT NULL, -- admin role, "sys_admin_roles" comm code 참조
	status char(1) DEFAULT 'A'  NOT NULL, -- 데이터 상태,  "data_status" comm code 참조
	del_yn char(1) DEFAULT 'N'::bpchar NOT NULL, -- 삭제여부 (Y: 삭제)

	latest_login_at timestamptz, -- latest login time 
	
	affiliation VARCHAR(60), -- admin 소속
	description VARCHAR(600), -- admin 설명
	note VARCHAR(600), -- 비고
	
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각
	updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각
	deleted_at timestamptz NULL, -- 삭제 일시 (논리 삭제 시 기록)
	created_by varchar(40) NOT NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조
	updated_by varchar(40) NULL, -- 데이터 수정자
	deleted_by varchar(40) NULL, -- 데이터 삭제자
	
	CONSTRAINT pkey_sys_adm_account PRIMARY KEY (adm_id)
);

CREATE UNIQUE INDEX uidx_sys_adm_account_login ON public.sys_adm_account USING btree (login_id);
CREATE INDEX idx_sys_adm_account_name ON public.sys_adm_account USING btree (name);
CREATE INDEX idx_sys_adm_account_role_status ON public.sys_adm_account USING btree (roles, status);
COMMENT ON TABLE public.sys_adm_account IS 'IITP Admin Account(user) 정보 테이블';

COMMENT ON COLUMN public.sys_adm_account.adm_id IS ' system id, 고유 식별자 (자동 증가)';
COMMENT ON COLUMN public.sys_adm_account.login_id IS 'admin login id';
COMMENT ON COLUMN public.sys_adm_account.password IS 'admin login password (bcrypt Hassing), 초기 비번(12345!!!)';
COMMENT ON COLUMN public.sys_adm_account.name IS 'admin name';

COMMENT ON COLUMN public.sys_adm_account.roles IS 'admin role, "sys_admin_roles" comm code 참조';
COMMENT ON COLUMN public.sys_adm_account.status IS '"data_status" comm code 참조';
COMMENT ON COLUMN public.sys_adm_account.del_yn IS '삭제여부 (Y: 삭제)';

COMMENT ON COLUMN public.sys_adm_account.affiliation IS 'admin 소속';
COMMENT ON COLUMN public.sys_adm_account.description IS 'admin 설명';
COMMENT ON COLUMN public.sys_adm_account.note IS '비고';

COMMENT ON COLUMN public.sys_adm_account.latest_login_at IS '마지막 로그인 시각';

COMMENT ON COLUMN public.sys_adm_account.created_at IS '레코드 생성 시각';
COMMENT ON COLUMN public.sys_adm_account.updated_at IS '레코드 수정 시각';
COMMENT ON COLUMN public.sys_adm_account.deleted_at IS '삭제 일시 (논리 삭제 시 기록)';
COMMENT ON COLUMN public.sys_adm_account.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조';
COMMENT ON COLUMN public.sys_adm_account.updated_by IS '데이터 수정자';
COMMENT ON COLUMN public.sys_adm_account.deleted_by IS '데이터 삭제자';




-- #### IITP 공지사항 게시 정보 테이블 ####

-- # public.sys_notice definition

DROP TABLE IF EXISTS public.sys_notice;


CREATE TABLE sys_notice (
    notice_id        int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- 공지사항 고유 ID
    title            VARCHAR(300) NOT NULL,                     -- 공지 제목
    content          VARCHAR(6000) NOT NULL,                    -- 공지 본문 내용
    notice_type      CHAR(1) NOT null DEFAULT 'G' CHECK (notice_type IN ('G', 'S', 'E')), -- 공지 유형 (예: G(general), S(system), E(event))
    pinned_yn        CHAR(1) DEFAULT 'N',                     -- 상단 고정 여부 (Y 시 우선 노출)
    public_yn        CHAR(1) DEFAULT 'Y',                      -- 공개 여부 (N이면 비공개 또는 관리자 전용)
    posted_at        timestamptz DEFAULT CURRENT_TIMESTAMP,       -- 게시 일시
    start_dt         DATE,                                      -- 공지 시작일 (노출 시작일)
    end_dt           DATE,                                      -- 공지 종료일 (노출 종료일)
    created_by       VARCHAR(100),                              -- 작성자
    updated_by       VARCHAR(100),                              -- 수정자
    created_at       timestamptz DEFAULT CURRENT_TIMESTAMP,       -- 생성 일시
    updated_at       timestamptz,                                 -- 수정 일시
	
	CONSTRAINT pkey_sys_notice PRIMARY KEY (notice_id)
);

CREATE INDEX idx_sys_notice_type_dt ON public.sys_notice USING btree (notice_type, start_dt,end_dt);

COMMENT ON TABLE sys_notice IS '공지사항 게시 정보 테이블';

COMMENT ON COLUMN sys_notice.notice_id IS '공지사항 고유 식별자';
COMMENT ON COLUMN sys_notice.title IS '공지 제목';
COMMENT ON COLUMN sys_notice.content IS '공지 본문 텍스트';
COMMENT ON COLUMN sys_notice.notice_type IS '공지 유형 (예: G(general), S(system), E(event))';
COMMENT ON COLUMN sys_notice.pinned_yn IS '공지 상단 고정 여부 (Y 시 우선 노출)';
COMMENT ON COLUMN sys_notice.public_yn IS '공개 여부 (N이면 비공개 또는 관리자 전용)';
COMMENT ON COLUMN sys_notice.posted_at IS '공지 게시 일시';
COMMENT ON COLUMN sys_notice.start_dt IS '공지 노출 시작일';
COMMENT ON COLUMN sys_notice.end_dt IS '공지 노출 종료일';
COMMENT ON COLUMN sys_notice.created_by IS '공지 작성자 ID 또는 이름';
COMMENT ON COLUMN sys_notice.updated_by IS '공지 수정자 ID 또는 이름';
COMMENT ON COLUMN sys_notice.created_at IS '레코드 생성 시각';
COMMENT ON COLUMN sys_notice.updated_at IS '레코드 수정 시각';





-- #### IITP 자주 묻는 질문(FAQ) 정보 테이블 ####

-- # public.sys_faq definition

DROP TABLE IF EXISTS public.sys_faq;

CREATE TABLE public.sys_faq (
    faq_id        int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- system id, 고유 식별자 (자동 증가)
    faq_type       VARCHAR(12) NOT NULL,          -- FAQ 유형, "faq_type" comm code 참조
    question       VARCHAR(600) NOT NULL,                 -- 질문
    answer         VARCHAR(3000) NOT NULL,                 -- 답변
    sort_order     INT DEFAULT 0,                 -- 정렬 우선순위
	hit_cnt        INT DEFAULT 0,                 -- 조회수
    use_yn         CHAR(1) DEFAULT 'Y',           -- 사용 여부 (Y/N)
    created_at     timestamptz DEFAULT CURRENT_TIMESTAMP,
    updated_at     timestamptz DEFAULT CURRENT_TIMESTAMP,
    created_by     varchar(40),
    updated_by     varchar(40),
	
	CONSTRAINT pkey_sys_faq PRIMARY KEY (faq_id)
);
CREATE INDEX idx_sys_faq_type_sort ON public.sys_faq USING btree (faq_type, sort_order,use_yn);


COMMENT ON TABLE public.sys_faq IS '자주 묻는 질문(FAQ) 정보 테이블';

COMMENT ON COLUMN public.sys_faq.faq_id      IS 'FAQ 고유 ID';
COMMENT ON COLUMN public.sys_faq.faq_type    IS 'FAQ 유형, "faq_type" comm code 참조';
COMMENT ON COLUMN public.sys_faq.question    IS 'FAQ 질문';
COMMENT ON COLUMN public.sys_faq.answer      IS 'FAQ 답변';
COMMENT ON COLUMN public.sys_faq.sort_order  IS '정렬 순서';
COMMENT ON COLUMN public.sys_faq.hit_cnt     IS 'FAQ 조회수';
COMMENT ON COLUMN public.sys_faq.use_yn      IS '사용 여부 (Y/N))';


COMMENT ON COLUMN public.sys_faq.created_by  IS '등록자 ID 또는 이름';
COMMENT ON COLUMN public.sys_faq.updated_by  IS '수정자 ID 또는 이름';
COMMENT ON COLUMN public.sys_faq.created_at  IS '등록 일시';
COMMENT ON COLUMN public.sys_faq.updated_at  IS '수정 일시';




-- #### IITP 일반 사용자 QnA 테이블 (질문과 답변 포함) ####

-- # public.sys_qna definition

DROP TABLE IF EXISTS public.sys_qna;

CREATE TABLE public.sys_qna (
    qna_id           BIGSERIAL NOT NULL,                            -- 질문 고유 식별자
    user_id          int4  NOT NULL,                                   -- 작성자 system ID
    qna_type         VARCHAR(32) NOT NULL,                             -- 질문 유형, "qna_type" comm code 참조
    title            VARCHAR(600) NOT NULL,                            -- 질문 제목
    content          VARCHAR(6000) NOT NULL,                           -- 질문 내용
    secret_yn        CHAR(1) DEFAULT 'N',                              -- 비공개 여부 (Y: 비공개, N: 공개)
    writer_name      varchar(90),                                     -- 작성자 이름 (선택 입력)
    answer_content   VARCHAR(6000),                                   -- 답변 내용
    answered_by      varchar(40),                                     -- 답변자 ID 또는 이름
	answered_yn      CHAR(1) DEFAULT 'N',                              -- 답변 공개(완료) 여부 (Y: 공개(완료), N: 비공개(미답변))
	hit_cnt        INT DEFAULT 0,                 -- 조회수
	del_yn char(1) DEFAULT 'N'::bpchar NOT NULL, -- 삭제여부 (Y: 삭제)
    answered_at      timestamptz,                                        -- 답변 일시
    created_at       timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,     -- 질문 등록일시
    updated_at       timestamptz DEFAULT CURRENT_TIMESTAMP,              -- 마지막 수정일시
    deleted_at       timestamptz,                                        -- 삭제 일시
    created_by       varchar(40),                                     -- 생성자
    updated_by       varchar(40),                                     -- 수정자
    deleted_by       varchar(40),                                      -- 삭제자
	
	CONSTRAINT pkey_sys_qna PRIMARY KEY (qna_id)
);
CREATE INDEX idx_sys_qna_type_screct ON public.sys_qna USING btree (qna_type, secret_yn);
CREATE INDEX idx_sys_qna_type_sort ON public.sys_qna USING btree (hit_cnt, created_at);


COMMENT ON TABLE public.sys_qna IS '일반 사용자 QnA 테이블 (질문과 답변 포함)';

COMMENT ON COLUMN public.sys_qna.qna_id IS 'QnA 고유 식별자 (Primary Key)';
COMMENT ON COLUMN public.sys_qna.user_id IS '작성자 system ID ';
COMMENT ON COLUMN public.sys_qna.qna_type IS 'QnA 질문 유형, "qna_type" comm code 참조';
COMMENT ON COLUMN public.sys_qna.title IS '질문 제목';
COMMENT ON COLUMN public.sys_qna.content IS '질문 본문 내용';
COMMENT ON COLUMN public.sys_qna.secret_yn IS '비공개 여부 (Y: 비공개, N: 공개)';
COMMENT ON COLUMN public.sys_qna.writer_name IS '작성자 이름 (옵션)';
COMMENT ON COLUMN public.sys_qna.answer_content IS '답변 내용';
COMMENT ON COLUMN public.sys_qna.answered_by IS '답변 작성자 ID 또는 이름';
COMMENT ON COLUMN public.sys_qna.answered_yn IS '답변 공개(완료) 여부 (Y: 공개(완료), N: 비공개(미답변))';
COMMENT ON COLUMN public.sys_qna.hit_cnt     IS 'QnA 조회수';
COMMENT ON COLUMN public.sys_qna.del_yn IS '삭제여부 (Y: 삭제)';
COMMENT ON COLUMN public.sys_qna.answered_at IS '답변 등록 일시';
COMMENT ON COLUMN public.sys_qna.created_at IS '질문 등록 일시';
COMMENT ON COLUMN public.sys_qna.updated_at IS '질문 수정 일시';
COMMENT ON COLUMN public.sys_qna.deleted_at IS '삭제 일시 (soft delete)';
COMMENT ON COLUMN public.sys_qna.created_by IS '생성자 ID';
COMMENT ON COLUMN public.sys_qna.updated_by IS '수정자 ID';
COMMENT ON COLUMN public.sys_qna.deleted_by IS '삭제자 ID';







-- ################################################
-- ## 시스템 로그 기록 
-- ################################################



-- #### IITP 사용자 access(login/logout) 이력 테이블 ####

-- # public.sys_log_user_access definition

DROP TABLE IF EXISTS public.sys_log_user_access;

CREATE TABLE public.sys_log_user_access (
    log_id          BIGSERIAL NOT NULL,                            -- 로그 고유 ID
    user_id         int4 NOT NULL,                                  -- 사용자 ID
	user_type       CHAR(1) NOT NULL CHECK (user_type IN ('U', 'A')), -- 사용자 유형
    log_type        VARCHAR(16) NOT NULL CHECK (log_type IN ('LOGIN', 'LOGOUT', 'LOGOUT-T-EXP')), -- 수행한 액션 종류 (예: LOGIN, LOGOUT, LOGOUT-T-EXP)
    act_result      CHAR(1) NOT NULL CHECK (act_result IN ('S', 'F')),  -- 액션 결과 ( S, F)
    err_code       VARCHAR(10),                                      -- 실패 시 에러 코드
	err_msg        VARCHAR(200),                                             -- 실패 시 에러 메시지
    ip_addr      VARCHAR(50),                                      -- 요청 IP 주소	
	user_agent    VARCHAR(512),                           -- 사용자 디바이스 정보 (브라우저 등)
	access_tm     timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,     -- access 시각
	created_at       timestamptz DEFAULT CURRENT_TIMESTAMP,
	
	CONSTRAINT pkey_sys_log_user_access PRIMARY KEY (log_id)
);
CREATE INDEX idx_sys_log_user_access_user_type ON public.sys_log_user_access USING btree (user_id, user_type,log_type);


COMMENT ON TABLE public.sys_log_user_access IS '사용자 access(login/logout) 이력 테이블';

COMMENT ON COLUMN public.sys_log_user_access.log_id IS '로그 고유 ID';
COMMENT ON COLUMN public.sys_log_user_access.user_id IS '사용자 ID';
COMMENT ON COLUMN public.sys_log_user_access.user_type IS '사용자 유형(U:User, A:Admin)';

COMMENT ON COLUMN public.sys_log_user_access.log_type IS '수행한 액션 (예: LOGIN, LOGOUT,LOGOUT-T-EXP)';
COMMENT ON COLUMN public.sys_log_user_access.act_result IS '성공 여부( S:성공, F:실패)';
COMMENT ON COLUMN public.sys_log_user_access.err_code IS '에러 발생 시 코드';
COMMENT ON COLUMN public.sys_log_user_access.err_msg IS '에러 발생 시 상세 메시지';
COMMENT ON COLUMN public.sys_log_user_access.ip_addr IS '요청을 보낸 클라이언트 IP';
COMMENT ON COLUMN public.sys_log_user_access.user_agent IS '사용자 디바이스 정보 (브라우저 등)';
COMMENT ON COLUMN public.sys_log_user_access.access_tm IS 'access 시각';
COMMENT ON COLUMN public.sys_log_user_access.created_at IS '등록 일시';




-- #### IITP 어드민/사용자 데이터 변경 이력 테이블 ####

-- # public.sys_log_change_his definition

DROP TABLE IF EXISTS public.sys_log_change_his;

CREATE TABLE sys_log_change_his (
    log_id           BIGSERIAL PRIMARY KEY,
    
    actor_type       CHAR(1)      NOT NULL CHECK (actor_type IN ('U', 'A')),  -- 사용자 유형
    actor_id         BIGINT       NOT NULL, -- user_id or admin_id depending on actor_type

    action_type      VARCHAR(36)  NOT NULL, -- 변경 타입 (예: 'DEL_ACCOUNT', 'EDIT_ACCOUNT')
    target_type      VARCHAR(64),          --  변경 대상 타입(예: 'USER_PROFILE', 'ADMIN_MENU', 'ROLE', etc.)
    target_id        BIGINT,                -- 대상 ID

    act_result      CHAR(1) NOT NULL CHECK (act_result IN ('S', 'F')),  -- 액션 결과 ( S, F)
    chg_summary     JSONB,                 -- 주요 변경 내용 요약, 예: {"bf": {...}, "af: {...}}
    
	err_code       VARCHAR(10),           -- 실패 시 에러 코드
	err_msg        VARCHAR(200),              -- 실패 시 에러 메시지
	
    ip_addr        VARCHAR(50),                 -- 요청 IP 주소
    user_agent    VARCHAR(512),               -- 사용자 디바이스 정보 (브라우저 등)

	act_tm         timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,     -- action 시각
    created_at     timestamptz DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_sys_log_change_his_act_type_id ON public.sys_log_change_his USING btree (actor_type, actor_id, action_type);
CREATE INDEX idx_sys_log_change_his_tgt_type_id ON public.sys_log_change_his USING btree (target_type, target_id);


COMMENT ON TABLE public.sys_log_change_his IS '어드민/사용자 데이터 변경 이력 테이블';

COMMENT ON COLUMN public.sys_log_change_his.log_id IS '로그 고유 ID';
COMMENT ON COLUMN public.sys_log_change_his.actor_type IS '사사용자 유형(U:User, A:Admin)';
COMMENT ON COLUMN public.sys_log_change_his.actor_id IS 'user_id or admin_id depending on actor_type';

COMMENT ON COLUMN public.sys_log_change_his.action_type IS '변경 타입 (예: DEL_ACCOUNT, EDIT_ACCOUNT)';
COMMENT ON COLUMN public.sys_log_change_his.target_type IS '변경 대상 타입 (예: USR_PROFILE, ADM_MENU, ROLE, etc.)';
COMMENT ON COLUMN public.sys_log_change_his.target_id IS '대상 ID';

COMMENT ON COLUMN public.sys_log_change_his.act_result IS '성공 여부( S:성공, F:실패)';
COMMENT ON COLUMN public.sys_log_change_his.chg_summary IS '주요 변경 내용 요약, 예: {"bf": {...}, "af: {...}}';

COMMENT ON COLUMN public.sys_log_change_his.err_code IS '에러 발생 시 코드';
COMMENT ON COLUMN public.sys_log_change_his.err_msg IS '에러 발생 시 상세 메시지';
COMMENT ON COLUMN public.sys_log_change_his.ip_addr IS '요청을 보낸 클라이언트 IP';
COMMENT ON COLUMN public.sys_log_change_his.user_agent IS '사용자 디바이스 정보 (브라우저 등)';
COMMENT ON COLUMN public.sys_log_change_his.act_tm IS 'action 시각';
COMMENT ON COLUMN public.sys_log_change_his.created_at IS '등록 일시';









-- ################################################
-- ## 통계 데이터 수집 기록 
-- ################################################

-- #### KOSIS 통계 데이터 수집 기록 ####

-- # public.sys_ext_api_info definition

-- Drop table if table exists

CREATE TABLE public.sys_log_stats_intg_his (
	log_id int8 GENERATED BY DEFAULT AS identity
	
	intg_tbl_id VARCHAR(50) NOT NULL, -- 데이터 동합할 내부 db table id
	stat_tbl_id varchar(40) NOT NULL, -- 원데이터 통계 id
	action_type varchar(24) NOT NULL, -- action type "log_his_stat_act_type" comm code 참조
	
	ret_yn char(1) NOT NULL, -- action 실패 여부
	err_code char(6),   -- 실패시 에러 코드
	err_msg varchar(300),   -- 실패시 에러 메시지
	
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각
	created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조
	
	CONSTRAINT pkey_log_his_stats_intg PRIMARY KEY (log_id)
	};
	
	
	
	COMMENT ON COLUMN public.log_his_stats_intg.created_at IS '레코드 생성 시각';
	COMMENT ON COLUMN public.log_his_stats_intg.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조';