[IITP] 장애인 데이터 과제 2차년도

-- ## 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 참조';