-- ## iitp DB Schemas - Initial setup - Creation and Delete if tables exists 
-- ## ver 0.0.5 last updated data : 2025.10.02
-- ## Only for PostgreSQL
-- ## 장애인 자립 지원 허브 플랫폼 용 
-- ##




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


-- #### [주의] 통계 데이터 수집으로 sys_stats_src_api_info, stats_src_data_info 테이블의 데이터가 변경되면, sys_data_summary_info 테이블 데이터도 update 해야 함.
-- #### [주의] mv_poi, poi_*,  emp_dis_* 테이블의 데이터가 변경되면, sys_data_summary_info 테이블 데이터도 update 해야 함.

-- # public.sys_data_summary_info definition
DROP TABLE IF EXISTS public.sys_data_summary_info;


CREATE TABLE public.sys_data_summary_info (
	data_id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 CYCLE) NOT NULL, -- system id, 고유 식별자 (자동 증가)
	target_data_id int4, --  Target Data info sys ID, data_type 에 따라서 id 참조. 예) stats_src_data_info.src_data_id
	
	data_type varchar(16) NOT NULL, -- Data Type (basic/poi/emp) 
    self_rlty_type varchar(24),      -- 자립 테마 타입 (phys,emo, econ,soc)
	category varchar(24),           -- 카테고리 (기초, 신체 자립 현황, 의료 자립현황, 보조기기 사용 현황, 진로 교육 현황, 고용 현황, 사회망 현황, ...)
	
	title varchar(300) NOT NULL, -- 데이터명
	sys_tbl_id varchar(40) NOT NULL, -- iitp 데이터 테이블 ID
	
	src_org_name varchar(50) NOT NULL,      -- 데이터 제공처(기관명)
	src_latest_chn_dt varchar(12) NOT NULL, -- 수집기관 최종 자료갱신일 (예:2024-07-19)
	
	sys_data_ref_dt varchar(12) NULL, 		-- 데이터를 iitp 시스템에서 마지막 수집/참조 일자 (예:2024-07-19)
	sys_data_reg_dt varchar(12) NOT NULL,   -- 데이터를 iitp 시스템에 등록한 일자 (예:2024-07-19) 
	
	data_keywords varchar(220),        -- 데이터 키워드 (최대 3개), 데이터 포맷 :: [,,] 형식
	data_format varchar(60),            -- 제공 데이터 포맷 ([csv, json])
	
	data_desc varchar(300),            -- 데이터 설명
	data_usage_scope varchar(300),     -- 이용 데이터 범위 설명
	
	open_api_url varchar(300) NOT NULL, -- open api req url
	
	status char(1) DEFAULT 'A'  NOT NULL, -- 데이터 상태,  "data_status" comm code 참조
	del_yn char(1) DEFAULT 'N'::bpchar NOT NULL, -- 삭제여부 (Y: 삭제)
	
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각
	updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각
	deleted_at timestamptz NULL, -- 삭제 일시 (논리 삭제 시 기록)
	CONSTRAINT pkey_sys_data_sumary_info PRIMARY KEY (data_id)
);
CREATE UNIQUE INDEX uidx_sys_data_sumary_info_sys_tbl_id ON public.sys_data_summary_info USING btree (sys_tbl_id);
CREATE INDEX  idx_sys_data_sumary_info_title ON public.sys_data_summary_info USING btree (title);
CREATE INDEX  idx_sys_data_sumary_info_data_type ON public.sys_data_summary_info USING btree (data_type);
CREATE INDEX  idx_sys_data_sumary_info_self_rlty_type ON public.sys_data_summary_info USING btree (self_rlty_type);



COMMENT ON TABLE public.sys_data_summary_info IS 'IITP 전체 데이터별 요약 정보';

-- Column comments
COMMENT ON COLUMN public.sys_data_summary_info.data_id IS 'system id, 고유 식별자 (자동 증가)';
COMMENT ON COLUMN public.sys_data_summary_info.target_data_id IS 'Target Data info sys ID, target_data_type 에 따라서 id 참조. 예) stats_src_data_info.src_data_id';

COMMENT ON COLUMN public.sys_data_summary_info.data_type IS 'Target Data Type (basic/poi/emp)';
COMMENT ON COLUMN public.sys_data_summary_info.self_rlty_type IS '자립 테마 타입 (phys,emo, econo,soc)';
COMMENT ON COLUMN public.sys_data_summary_info.category IS '카테고리 (기초, 신체 자립 현황, 의료 자립현황, 보조기기 사용 현황, 진로 교육 현황, 고용 현황, 사회망 현황, ...)';


COMMENT ON COLUMN public.sys_data_summary_info.title IS '데이터명';
COMMENT ON COLUMN public.sys_data_summary_info.sys_tbl_id IS 'iitp 데이터 목록 테이블 ID';

COMMENT ON COLUMN public.sys_data_summary_info.src_org_name IS '데이터 제공처(기관명)';
COMMENT ON COLUMN public.sys_data_summary_info.src_latest_chn_dt IS '수집기관 최종 자료갱신일 (예:2024-07-19)';

COMMENT ON COLUMN public.sys_data_summary_info.sys_data_ref_dt IS '데이터를 iitp 시스템에서 마지막 수집/참조 일자 (예:2024-07-19)';
COMMENT ON COLUMN public.sys_data_summary_info.sys_data_reg_dt IS '데이터를 iitp 시스템에 등록한 일자 (예:2024-07-19)';

COMMENT ON COLUMN public.sys_data_summary_info.data_keywords IS '데이터 키워드 (최대 3개), 데이터 포맷 :: [,,] 형식';
COMMENT ON COLUMN public.sys_data_summary_info.data_format IS '제공 데이터 포맷 ([csv, json])';

COMMENT ON COLUMN public.sys_data_summary_info.data_desc IS '데이터 설명';
COMMENT ON COLUMN public.sys_data_summary_info.data_usage_scope IS '이용 데이터 범위 설명';

COMMENT ON COLUMN public.sys_data_summary_info.open_api_url IS 'open api req url';

COMMENT ON COLUMN public.sys_data_summary_info.status IS '"data_status" comm code 참조';
COMMENT ON COLUMN public.sys_data_summary_info.del_yn IS '삭제여부 (Y: 삭제)';

COMMENT ON COLUMN public.sys_data_summary_info.created_at IS '레코드 생성 시각';
COMMENT ON COLUMN public.sys_data_summary_info.updated_at IS '레코드 수정 시각';
COMMENT ON COLUMN public.sys_data_summary_info.deleted_at IS '삭제 일시 (논리 삭제 시 기록)';




-- # public.selfdiag_data_category definition
DROP TABLE IF EXISTS public.selfdiag_data_category;

CREATE TABLE selfdiag_data_category (
    id          SERIAL PRIMARY KEY,
    category    VARCHAR(50) NOT NULL,  -- 카테고리: theme, data_type
    name        VARCHAR(100) NOT NULL,  -- 카테고리명: 자립테마별, 데이터 유형별
    menu_id     VARCHAR(50) NOT NULL,  -- 메뉴 id (theme : phy,emo, econ,soc, data_type : basic, poi, emp)
    menu_name   VARCHAR(100) NOT NULL, -- 메뉴 항목명: 신체적 자립, 정서적 자립 등
	
    description VARCHAR(600) NOT NULL,  -- 메뉴별 간단한 설명
    created_at  timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at  timestamptz 
);

COMMENT ON TABLE public.selfdiag_data_category IS 'IITP 데이터 테이블 분류 정보';

-- 컬럼 설명 추가
COMMENT ON COLUMN selfdiag_data_category.category IS '카테고리: theme, data_type';
COMMENT ON COLUMN selfdiag_data_category.name IS '카테고리명: 자립테마별, 데이터 유형별, ';
COMMENT ON COLUMN selfdiag_data_category.menu_id IS '메뉴 id (theme : phy,emo, econ,soc, data_type : basic, poi, emp)';
COMMENT ON COLUMN selfdiag_data_category.menu_name IS '메뉴 항목명: 신체적 자립, 정서적 자립 등';
COMMENT ON COLUMN selfdiag_data_category.description IS '메뉴별 상세 설명';
COMMENT ON COLUMN selfdiag_data_category.created_at IS '데이터 생성일';
COMMENT ON COLUMN selfdiag_data_category.updated_at IS '데이터 수정일';

-- 샘플 데이터 INSERT
INSERT INTO selfdiag_data_category (category, name, menu_id, menu_name, description)
VALUES
('theme', '자립테마별', 'phys', '신체적 자립', '신체적 자립 수준 및 건강 상태 등 신체 기능과 관련된 데이터'),
('theme', '자립테마별', 'emo', '정서적 자립', '심리적 안정 및 지원 등 정서 안정과 관련된 데이터'),
('theme', '자립테마별', 'econ', '경제적 자립', '장애인 진로교육 및 고용 현황 등 일자리와 관련된 데이터'),
('theme', '자립테마별', 'soc', '사회적 자립', '사회복지시설 및 지역사회 참여 현황 등 사회적 기능과 관련된 데이터'),
('data_type', '데이터 유형별', 'basic', '기초 데이터', '장애인과 관련된 기본 통계 및 기초 자료'),
('data_type', '데이터 유형별', 'poi', '이동권 데이터', '교통·보행 등 장애인 이동 및 접근성과 관련된 자료'),
('data_type', '데이터 유형별', 'emp', '일자리 데이터', '고용 현황, 취업 지원, 직업 활동과 관련된 자료'),
('self_rlty_type', '자립유형', 'basic', '기초', '기본 적인 장애 자립 관련 정보(정책, 기관, 시설)'),
('self_rlty_type', '자립유형', 'phys', '신체', '신체적 자립 관련 정보 (정책, 기관, 시설)'),
('self_rlty_type', '자립유형', 'emo', '정서', '정서적 자립 관련된 정보 (정책, 기관, 시설)'),
('self_rlty_type', '자립유형', 'econ', '경제', '경제적 자립 관련된 정보 (정책, 기관, 시설)'),
('self_rlty_type', '자립유형', 'soc', '사회', '사회적 자립 관련된 정보 (정책, 기관, 시설)');







-- # public.selfdiag_policy definition
DROP TABLE IF EXISTS public.selfdiag_policy;

CREATE TABLE selfdiag_policy (
    policy_id       SERIAL PRIMARY KEY,                 -- PK
    category        VARCHAR(90) NOT NULL,                  -- 구분 (예: 생활 안정 지원, 생활요금 감면)
    policy_name     VARCHAR(300) NOT NULL,                 -- 항목명 (예: 장애인연금, 장애수당)
    self_rlty_type  VARCHAR(50),                           -- 관련 자립 유형 (기초, 신체, 경제, 정서, 사회 등)
    region          VARCHAR(100),                          -- 사업 단위 (전국, 시군구 등)
    gender          VARCHAR(50),                           -- 대상 성별 (남성, 여성, "여성, 남성")
    age_cond        VARCHAR(50),                           -- 연령 조건 (minor=미성년자, adult=성인, all=미성년+성인')
    dis_level       VARCHAR(50),                           -- 장애 정도 (중증, 경증)
    fin_cond        VARCHAR(100),                          -- 재정 조건 (중위소득 50~100% 등)
    target          VARCHAR(3000),                                  -- 대상자 설명
    benefit         VARCHAR(6000),                                  -- 지원 내용 설명
    link            VARCHAR(512),                                  -- 상세 이동 링크(URL)
    created_at      timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,               -- 생성 시각
    updated_at      timestamptz                 -- 수정 시각
);

-- COMMENT 구문
COMMENT ON TABLE selfdiag_policy IS '장애인 자립 자가진단 - 정책·서비스 정보';

COMMENT ON COLUMN selfdiag_policy.policy_id IS 'PK: 데이터 고유 ID';
COMMENT ON COLUMN selfdiag_policy.category IS '지원 구분 (생활 안정 지원, 생활요금 감면 등)';
COMMENT ON COLUMN selfdiag_policy.policy_name IS '지원 항목명 (장애인연금, 장애수당 등)';
COMMENT ON COLUMN selfdiag_policy.self_rlty_type IS '관련 자립 유형 (기초, 신체, 경제, 정서, 사회 등)';
COMMENT ON COLUMN selfdiag_policy.region IS '사업 단위 (전국, 시/군/구 등)';
COMMENT ON COLUMN selfdiag_policy.gender IS '성별 조건 (여성, 남성, "여성, 남성")';
COMMENT ON COLUMN selfdiag_policy.age_cond IS '연령 조건 (minor=미성년자, adult=성인, all=미성년+성인)';
COMMENT ON COLUMN selfdiag_policy.dis_level IS '장애 정도 조건 (중증, 경증, 혼합)';
COMMENT ON COLUMN selfdiag_policy.fin_cond IS '재정 조건 (중위소득 기준 등)';
COMMENT ON COLUMN selfdiag_policy.target IS '대상자 상세 설명';
COMMENT ON COLUMN selfdiag_policy.benefit IS '지원 내용 상세';
COMMENT ON COLUMN selfdiag_policy.link IS '상세 페이지 URL';
COMMENT ON COLUMN selfdiag_policy.created_at IS '데이터 생성 시각';
COMMENT ON COLUMN selfdiag_policy.updated_at IS '데이터 최종 수정 시각';




-- # public.selfdiag_provider definition
DROP TABLE IF EXISTS public.selfdiag_provider;

CREATE TABLE selfdiag_provider (
    provider_id         SERIAL PRIMARY KEY,           -- PK
    provider_name       VARCHAR(200) NOT NULL,           -- 제공기관명 (지점명까지 포함)
    service_name        VARCHAR(200) NOT NULL,           -- 제공 서비스명
    address             VARCHAR(300) NOT NULL,           -- 주소 (시군구 + 상세주소 포함)
    phone               VARCHAR(50),                    -- 전화번호
    rep_name 			VARCHAR(100),                   -- 대표자명 (선택 입력)
    description         VARCHAR(900),                           -- 비고/기관 상세설명
    created_at           timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 생성일시
    updated_at           timestamptz                       -- 수정일시
);

COMMENT ON TABLE selfdiag_provider IS '장애인 자립 자가진단 - 정책 제공 기관(Provider) 정보 테이블';

COMMENT ON COLUMN selfdiag_provider.provider_id IS 'PK, 제공 기관 식별자';
COMMENT ON COLUMN selfdiag_provider.provider_name IS '제공기관명 (지점명 포함)';
COMMENT ON COLUMN selfdiag_provider.service_name IS '해당 기관이 제공하는 서비스명';
COMMENT ON COLUMN selfdiag_provider.address IS '기관 주소 (시군구, 상세주소 포함)';
COMMENT ON COLUMN selfdiag_provider.phone IS '기관 전화번호';
COMMENT ON COLUMN selfdiag_provider.rep_name IS '대표자명';
COMMENT ON COLUMN selfdiag_provider.description IS '기관 및 서비스에 대한 상세 설명, 비고';
COMMENT ON COLUMN selfdiag_provider.created_at IS '데이터 생성일시';
COMMENT ON COLUMN selfdiag_provider.updated_at IS '데이터 최종 수정일시';







-- # public.selfdiag_facility definition
DROP TABLE IF EXISTS public.selfdiag_facility;

CREATE TABLE selfdiag_facility (
    facility_id       SERIAL PRIMARY KEY,
    device           VARCHAR(300), -- 설치 기기
    install_area     VARCHAR(100),          -- 설치 지역
    install_site     VARCHAR(300),          -- 설치 장소
    install_spot     VARCHAR(300),          -- 설치 위치 (건물 내 위치 등)
    hang_dong        VARCHAR(60),           -- 행정동
    address           VARCHAR(300),          -- 상세 주소
    opening_hours     VARCHAR(300),          -- 이용 시간
    quantity          INT DEFAULT 0,                   -- 설치 대수
    note              VARCHAR(900),                  -- 비고
    created_at         timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at         timestamptz
);

COMMENT ON TABLE selfdiag_provider IS '장애인 자립 자가진단 - 정책 제공 시설(facility) 정보 테이블';

COMMENT ON COLUMN selfdiag_facility.facility_id IS 'PK, 설치 기기 식별자';
COMMENT ON COLUMN selfdiag_facility.device            IS '설치 기기명';
COMMENT ON COLUMN selfdiag_facility.install_area          IS '설치 지역(시/구)';
COMMENT ON COLUMN selfdiag_facility.install_site             IS '설치 장소명';
COMMENT ON COLUMN selfdiag_facility.install_spot   IS '건물 내 설치 위치 상세';
COMMENT ON COLUMN selfdiag_facility.hang_dong        IS '행정동';
COMMENT ON COLUMN selfdiag_facility.address           IS '상세 주소';
COMMENT ON COLUMN selfdiag_facility.opening_hours     IS '이용 시간';
COMMENT ON COLUMN selfdiag_facility.quantity          IS '설치 대수';
COMMENT ON COLUMN selfdiag_facility.note              IS '비고';
COMMENT ON COLUMN selfdiag_facility.created_at IS '데이터 생성일시';
COMMENT ON COLUMN selfdiag_facility.updated_at IS '데이터 최종 수정일시';







