https://www.erdcloud.com/d/SrmgGDxNJqNaDjJPt

-- PostGIS 확장 활성화 (필수)
CREATE EXTENSION IF NOT EXISTS postgis;

-- youfi 스키마 생성
CREATE SCHEMA IF NOT EXISTS youfi;

-- ========================================
-- ENUM 타입 정의
-- ========================================

-- 사용자 역할
CREATE TYPE youfi.user_role AS ENUM ('USER', 'ADMIN');

-- 알림 타입
CREATE TYPE youfi.notification_type AS ENUM ('INVITE_REQUEST', 'FOUND_REPORT', 'NEARBY_ALERT');

-- 성별
CREATE TYPE youfi.gender_type AS ENUM ('MALE', 'FEMALE', 'UNKNOWN');

-- 실종 케이스 상태
CREATE TYPE youfi.case_status_type AS ENUM ('OPEN', 'CLOSED', 'ARCHIVED');

-- AI 에셋 타입
CREATE TYPE youfi.asset_type AS ENUM ('GENERATED_IMAGE', 'AGE_PROGRESSION');

-- 관계 요청 상태
CREATE TYPE youfi.relationship_request_status AS ENUM ('PENDING', 'ACCEPTED', 'REJECTED');

-- ========================================
-- 테이블 생성
-- ========================================

-- 사용자 테이블
CREATE TABLE youfi.users (
    id BIGSERIAL PRIMARY KEY,
    uid VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone_e164 VARCHAR(16) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL,
    role youfi.user_role NOT NULL DEFAULT 'USER',
    profile_url VARCHAR(500),
    profile_background_color VARCHAR(20),
    card INTEGER,
    level INTEGER,
    exp INTEGER,
    title VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 기기 테이블
CREATE TABLE youfi.devices (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES youfi.users(id) ON DELETE CASCADE,
    device_uuid VARCHAR(255) NOT NULL UNIQUE,
    battery_level INTEGER CHECK (battery_level >= 0 AND battery_level <= 100),
    os_type VARCHAR(20),
    os_version VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    registered_at TIMESTAMP,
    fcm_token VARCHAR(500)
);

-- GPS 추적 테이블
CREATE TABLE youfi.gps_tracks (
    id BIGSERIAL PRIMARY KEY,
    device_id BIGINT REFERENCES youfi.devices(id) ON DELETE CASCADE,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    recorded_at TIMESTAMP NOT NULL
);

-- 관계 테이블
CREATE TABLE youfi.relationships (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES youfi.users(id) ON DELETE CASCADE,
    member_id BIGINT REFERENCES youfi.users(id) ON DELETE CASCADE,
    relation VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 초대 테이블
CREATE TABLE youfi.invitations (
    id BIGSERIAL PRIMARY KEY,
    inviter_user_id BIGINT REFERENCES youfi.users(id) ON DELETE CASCADE,
    invitee_user_id BIGINT REFERENCES youfi.users(id) ON DELETE CASCADE,
    relation VARCHAR(100),
    status youfi.relationship_request_status NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    responded_at TIMESTAMP
);

-- 실종자 테이블
CREATE TABLE youfi.missing_persons (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_date DATE,
    gender youfi.gender_type,
    height INTEGER,
    weight INTEGER,
    body TEXT,
    body_etc TEXT,
    clothes_top TEXT,
    clothes_bottom TEXT,
    clothes_etc TEXT,
    missing_date TIMESTAMP NOT NULL,
    address TEXT,
    photo_url VARCHAR(500),
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    predicted_face_url VARCHAR(500),
    appearance_image_url VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 실종 케이스 테이블
CREATE TABLE youfi.missing_cases (
    id BIGSERIAL PRIMARY KEY,
    missing_person_id BIGINT NOT NULL REFERENCES youfi.missing_persons(id) ON DELETE CASCADE,
    case_status youfi.case_status_type NOT NULL DEFAULT 'OPEN',
    reported_by BIGINT NOT NULL REFERENCES youfi.users(id) ON DELETE CASCADE,
    reported_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 목격/발견 신고 테이블
CREATE TABLE youfi.sightings (
    id BIGSERIAL PRIMARY KEY,
    missing_case_id BIGINT NOT NULL REFERENCES youfi.missing_cases(id) ON DELETE CASCADE,
    reporter_id BIGINT NOT NULL REFERENCES youfi.users(id) ON DELETE CASCADE,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 경찰청 실종자 테이블
CREATE TABLE youfi.missing_person_police (
    id BIGINT PRIMARY KEY,
    occurrence_date VARCHAR(8),
    dress TEXT,
    age_now INTEGER,
    missing_age INTEGER,
    status_code VARCHAR(10),
    gender VARCHAR(10),
    special_features TEXT,
    occurrence_address TEXT,
    name VARCHAR(100),
    photo_length INTEGER,
    photo_url VARCHAR(500),
    collected_at TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- AI 생성 에셋 테이블
CREATE TABLE youfi.ai_assets (
    id BIGSERIAL PRIMARY KEY,
    missing_person_id BIGINT NOT NULL REFERENCES youfi.missing_persons(id) ON DELETE CASCADE,
    asset_type youfi.asset_type NOT NULL,
    asset_url TEXT NOT NULL,
    sequence_order INTEGER,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 이미지 테이블
CREATE TABLE youfi.images (
    id BIGSERIAL PRIMARY KEY,
    file_path TEXT NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_size BIGINT,
    mime_type VARCHAR(100),
    user_id BIGINT NOT NULL REFERENCES youfi.users(id) ON DELETE CASCADE,
    uploaded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 알림 테이블
CREATE TABLE youfi.notifications (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES youfi.users(id) ON DELETE CASCADE,
    type youfi.notification_type,
    title VARCHAR(200),
    message VARCHAR(500),
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_at TIMESTAMP,
    related_entity_id BIGINT,
    related_location GEOGRAPHY(POINT, 4326)
);

-- 전화번호 인증 테이블
CREATE TABLE youfi.phone_verification (
    id BIGSERIAL PRIMARY KEY,
    token VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(20),
    verified BOOLEAN NOT NULL DEFAULT FALSE,
    expires_at TIMESTAMP NOT NULL
);

-- 블랙리스트 토큰 테이블
CREATE TABLE youfi.blacklisted_tokens (
    id BIGSERIAL PRIMARY KEY,
    token VARCHAR(512) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    blacklisted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason VARCHAR(50),
    user_id VARCHAR(100)
);

-- ========================================
-- 인덱스 생성
-- ========================================

-- users 테이블 인덱스
CREATE INDEX idx_users_uid ON youfi.users(uid);
CREATE INDEX idx_users_phone_e164 ON youfi.users(phone_e164);

-- devices 테이블 인덱스
CREATE INDEX idx_devices_user_id ON youfi.devices(user_id);
CREATE INDEX idx_devices_device_uuid ON youfi.devices(device_uuid);

-- gps_tracks 테이블 인덱스
CREATE INDEX idx_gps_tracks_device_id ON youfi.gps_tracks(device_id);
CREATE INDEX idx_gps_tracks_recorded_at ON youfi.gps_tracks(recorded_at);
CREATE INDEX idx_gps_tracks_location ON youfi.gps_tracks USING GIST(location);

-- relationships 테이블 인덱스
CREATE INDEX idx_relationships_user_id ON youfi.relationships(user_id);
CREATE INDEX idx_relationships_member_id ON youfi.relationships(member_id);

-- invitations 테이블 인덱스
CREATE INDEX idx_invitations_inviter_user_id ON youfi.invitations(inviter_user_id);
CREATE INDEX idx_invitations_invitee_user_id ON youfi.invitations(invitee_user_id);
CREATE INDEX idx_invitations_status ON youfi.invitations(status);

-- missing_persons 테이블 인덱스
CREATE INDEX idx_missing_persons_name ON youfi.missing_persons(name);
CREATE INDEX idx_missing_persons_missing_date ON youfi.missing_persons(missing_date);
CREATE INDEX idx_missing_persons_location ON youfi.missing_persons USING GIST(location);

-- missing_cases 테이블 인덱스
CREATE INDEX idx_missing_cases_missing_person_id ON youfi.missing_cases(missing_person_id);
CREATE INDEX idx_missing_cases_reported_by ON youfi.missing_cases(reported_by);
CREATE INDEX idx_missing_cases_case_status ON youfi.missing_cases(case_status);

-- sightings 테이블 인덱스
CREATE INDEX idx_sightings_missing_case_id ON youfi.sightings(missing_case_id);
CREATE INDEX idx_sightings_reporter_id ON youfi.sightings(reporter_id);
CREATE INDEX idx_sightings_location ON youfi.sightings USING GIST(location);

-- ai_assets 테이블 인덱스
CREATE INDEX idx_ai_assets_missing_person_id ON youfi.ai_assets(missing_person_id);
CREATE INDEX idx_ai_assets_asset_type ON youfi.ai_assets(asset_type);
CREATE INDEX idx_ai_assets_missing_person_type_seq ON youfi.ai_assets(missing_person_id, asset_type, sequence_order);

-- images 테이블 인덱스
CREATE INDEX idx_images_user_id ON youfi.images(user_id);

-- notifications 테이블 인덱스
CREATE INDEX idx_notifications_user_id ON youfi.notifications(user_id);
CREATE INDEX idx_notifications_type ON youfi.notifications(type);
CREATE INDEX idx_notifications_is_read ON youfi.notifications(is_read);

-- phone_verification 테이블 인덱스
CREATE INDEX idx_phone_verification_token ON youfi.phone_verification(token);

-- blacklisted_tokens 테이블 인덱스 (엔티티에 정의됨)
CREATE INDEX idx_blacklisted_tokens_token ON youfi.blacklisted_tokens(token);
CREATE INDEX idx_blacklisted_tokens_expires_at ON youfi.blacklisted_tokens(expires_at);

-- ========================================
-- 트리거: 자동 updated_at 업데이트
-- ========================================

-- updated_at 자동 갱신 함수
CREATE OR REPLACE FUNCTION youfi.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- users 테이블 트리거
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON youfi.users
    FOR EACH ROW
    EXECUTE FUNCTION youfi.update_updated_at_column();

-- missing_persons 테이블 트리거
CREATE TRIGGER update_missing_persons_updated_at
    BEFORE UPDATE ON youfi.missing_persons
    FOR EACH ROW
    EXECUTE FUNCTION youfi.update_updated_at_column();

-- missing_person_police 테이블 트리거
CREATE TRIGGER update_missing_person_police_updated_at
    BEFORE UPDATE ON youfi.missing_person_police
    FOR EACH ROW
    EXECUTE FUNCTION youfi.update_updated_at_column();

-- ========================================
-- 제약 조건
-- ========================================

-- relationships 테이블: user와 member가 동일하지 않도록
ALTER TABLE youfi.relationships
    ADD CONSTRAINT chk_relationships_different_users
    CHECK (user_id != member_id);

-- invitations 테이블: inviter와 invitee가 동일하지 않도록
ALTER TABLE youfi.invitations
    ADD CONSTRAINT chk_invitations_different_users
    CHECK (inviter_user_id != invitee_user_id);

-- ========================================
-- 주석 (선택사항)
-- ========================================

COMMENT ON SCHEMA youfi IS 'Baro 프로젝트의 메인 스키마';

COMMENT ON TABLE youfi.users IS '사용자 계정 정보';
COMMENT ON TABLE youfi.devices IS '사용자의 모바일 기기 정보';
COMMENT ON TABLE youfi.gps_tracks IS 'GPS 위치 추적 히스토리';
COMMENT ON TABLE youfi.relationships IS '사용자 간 관계 정보';
COMMENT ON TABLE youfi.invitations IS '관계 초대 요청';
COMMENT ON TABLE youfi.missing_persons IS '실종자 신고 정보';
COMMENT ON TABLE youfi.missing_cases IS '실종 사건 정보';
COMMENT ON TABLE youfi.sightings IS '실종자 목격/발견 신고';
COMMENT ON TABLE youfi.missing_person_police IS '경찰청 실종자 데이터';
COMMENT ON TABLE youfi.ai_assets IS 'AI 생성 이미지 메타데이터';
COMMENT ON TABLE youfi.images IS '사용자 업로드 이미지';
COMMENT ON TABLE youfi.notifications IS '푸시 알림 정보';
COMMENT ON TABLE youfi.phone_verification IS '전화번호 인증 정보';
COMMENT ON TABLE youfi.blacklisted_tokens IS '로그아웃/무효화된 JWT 토큰';

COMMENT ON COLUMN youfi.users.uid IS '사용자 고유 문자열 ID (예: user001)';
COMMENT ON COLUMN youfi.users.password_hash IS 'BCrypt 해시된 비밀번호';
COMMENT ON COLUMN youfi.users.phone_e164 IS 'E.164 형식 전화번호';

COMMENT ON COLUMN youfi.gps_tracks.location IS 'PostGIS Point (경도, 위도) WGS84';
COMMENT ON COLUMN youfi.missing_persons.location IS 'PostGIS Point (경도, 위도) WGS84';
COMMENT ON COLUMN youfi.sightings.location IS 'PostGIS Point (경도, 위도) WGS84';
COMMENT ON COLUMN youfi.notifications.related_location IS 'PostGIS Point (경도, 위도) WGS84';

COMMENT ON COLUMN youfi.ai_assets.sequence_order IS 'AI 이미지 생성 순서 (0, 1, 2...)';