kt-event-marketing/design/backend/database/content-service-schema.psql

-- ============================================
-- Content Service Database Schema
-- ============================================
-- Database: content_service_db
-- Schema: content
-- RDBMS: PostgreSQL 16+
-- Created: 2025-10-29
-- Description: 이미지 생성 및 콘텐츠 관리 서비스 스키마
-- ============================================

-- ============================================
-- 데이터베이스 및 스키마 생성
-- ============================================

-- 데이터베이스 생성 (최초 1회만 실행)
-- CREATE DATABASE content_service_db
--   WITH ENCODING = 'UTF8'
--   LC_COLLATE = 'en_US.UTF-8'
--   LC_CTYPE = 'en_US.UTF-8'
--   TEMPLATE = template0;

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

-- 기본 스키마 설정
SET search_path TO content, public;

-- ============================================
-- 확장 기능 활성화
-- ============================================

-- UUID 생성 함수 (필요시)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 암호화 함수 (필요시)
CREATE EXTENSION IF NOT EXISTS pgcrypto;

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

-- --------------------------------------------
-- 1. content 테이블 (콘텐츠 집합)
-- --------------------------------------------

CREATE TABLE IF NOT EXISTS content.content (
  id                BIGSERIAL PRIMARY KEY,
  event_id          VARCHAR(100) NOT NULL,
  event_title       VARCHAR(200) NOT NULL,
  event_description TEXT,
  created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- 제약 조건
  CONSTRAINT uk_content_event_id UNIQUE (event_id)
);

-- 테이블 코멘트
COMMENT ON TABLE content.content IS '이벤트별 콘텐츠 집합 정보';
COMMENT ON COLUMN content.content.id IS '콘텐츠 ID (PK)';
COMMENT ON COLUMN content.content.event_id IS '이벤트 초안 ID (Event Service 참조)';
COMMENT ON COLUMN content.content.event_title IS '이벤트 제목';
COMMENT ON COLUMN content.content.event_description IS '이벤트 설명';
COMMENT ON COLUMN content.content.created_at IS '생성 시각';
COMMENT ON COLUMN content.content.updated_at IS '수정 시각';

-- 인덱스
CREATE INDEX IF NOT EXISTS idx_content_created_at
  ON content.content(created_at DESC);

-- --------------------------------------------
-- 2. generated_image 테이블 (생성된 이미지)
-- --------------------------------------------

CREATE TABLE IF NOT EXISTS content.generated_image (
  id            BIGSERIAL PRIMARY KEY,
  event_id      VARCHAR(100) NOT NULL,
  style         VARCHAR(20) NOT NULL,
  platform      VARCHAR(30) NOT NULL,
  cdn_url       VARCHAR(500) NOT NULL,
  prompt        TEXT NOT NULL,
  selected      BOOLEAN NOT NULL DEFAULT false,
  width         INT NOT NULL,
  height        INT NOT NULL,
  file_size     BIGINT,
  content_type  VARCHAR(50) NOT NULL DEFAULT 'image/png',
  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- 제약 조건
  CONSTRAINT chk_generated_image_style
    CHECK (style IN ('FANCY', 'SIMPLE', 'TRENDY')),
  CONSTRAINT chk_generated_image_platform
    CHECK (platform IN ('INSTAGRAM', 'FACEBOOK', 'KAKAO', 'BLOG')),
  CONSTRAINT chk_generated_image_dimensions
    CHECK (width > 0 AND height > 0),
  CONSTRAINT chk_generated_image_file_size
    CHECK (file_size IS NULL OR file_size > 0)
);

-- 테이블 코멘트
COMMENT ON TABLE content.generated_image IS 'AI 생성 이미지 메타데이터';
COMMENT ON COLUMN content.generated_image.id IS '이미지 ID (PK)';
COMMENT ON COLUMN content.generated_image.event_id IS '이벤트 초안 ID';
COMMENT ON COLUMN content.generated_image.style IS '이미지 스타일 (FANCY, SIMPLE, TRENDY)';
COMMENT ON COLUMN content.generated_image.platform IS '플랫폼 (INSTAGRAM, FACEBOOK, KAKAO, BLOG)';
COMMENT ON COLUMN content.generated_image.cdn_url IS 'CDN 이미지 URL (Azure Blob Storage)';
COMMENT ON COLUMN content.generated_image.prompt IS '이미지 생성에 사용된 프롬프트';
COMMENT ON COLUMN content.generated_image.selected IS '사용자 선택 여부 (최종 선택 이미지)';
COMMENT ON COLUMN content.generated_image.width IS '이미지 너비 (픽셀)';
COMMENT ON COLUMN content.generated_image.height IS '이미지 높이 (픽셀)';
COMMENT ON COLUMN content.generated_image.file_size IS '파일 크기 (bytes)';
COMMENT ON COLUMN content.generated_image.content_type IS 'MIME 타입 (image/png, image/jpeg 등)';
COMMENT ON COLUMN content.generated_image.created_at IS '생성 시각';
COMMENT ON COLUMN content.generated_image.updated_at IS '수정 시각';

-- 인덱스
CREATE INDEX IF NOT EXISTS idx_generated_image_event_id
  ON content.generated_image(event_id);

CREATE INDEX IF NOT EXISTS idx_generated_image_filter
  ON content.generated_image(event_id, style, platform);

CREATE INDEX IF NOT EXISTS idx_generated_image_selected
  ON content.generated_image(event_id, selected)
  WHERE selected = true;

CREATE INDEX IF NOT EXISTS idx_generated_image_created_at
  ON content.generated_image(created_at DESC);

-- --------------------------------------------
-- 3. job 테이블 (비동기 작업 추적)
-- --------------------------------------------

CREATE TABLE IF NOT EXISTS content.job (
  id              VARCHAR(100) PRIMARY KEY,
  event_id        VARCHAR(100) NOT NULL,
  job_type        VARCHAR(50) NOT NULL,
  status          VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  progress        INT NOT NULL DEFAULT 0,
  result_message  TEXT,
  error_message   TEXT,
  created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at    TIMESTAMP,

  -- 제약 조건
  CONSTRAINT chk_job_status
    CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED')),
  CONSTRAINT chk_job_type
    CHECK (job_type IN ('IMAGE_GENERATION', 'IMAGE_REGENERATION')),
  CONSTRAINT chk_job_progress
    CHECK (progress >= 0 AND progress <= 100)
);

-- 테이블 코멘트
COMMENT ON TABLE content.job IS '비동기 이미지 생성 작업 추적';
COMMENT ON COLUMN content.job.id IS 'Job ID (job-img-{uuid} 형식)';
COMMENT ON COLUMN content.job.event_id IS '이벤트 초안 ID';
COMMENT ON COLUMN content.job.job_type IS '작업 타입 (IMAGE_GENERATION, IMAGE_REGENERATION)';
COMMENT ON COLUMN content.job.status IS '작업 상태 (PENDING, PROCESSING, COMPLETED, FAILED)';
COMMENT ON COLUMN content.job.progress IS '진행률 (0-100)';
COMMENT ON COLUMN content.job.result_message IS '완료 메시지';
COMMENT ON COLUMN content.job.error_message IS '에러 메시지';
COMMENT ON COLUMN content.job.created_at IS '생성 시각';
COMMENT ON COLUMN content.job.updated_at IS '수정 시각';
COMMENT ON COLUMN content.job.completed_at IS '완료 시각 (COMPLETED/FAILED 상태에서 설정)';

-- 인덱스
CREATE INDEX IF NOT EXISTS idx_job_event_id
  ON content.job(event_id);

CREATE INDEX IF NOT EXISTS idx_job_status
  ON content.job(status, created_at DESC);

-- ============================================
-- 트리거 함수 (updated_at 자동 갱신)
-- ============================================

CREATE OR REPLACE FUNCTION content.update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

-- generated_image 테이블 트리거
CREATE TRIGGER trg_generated_image_updated_at
  BEFORE UPDATE ON content.generated_image
  FOR EACH ROW
  EXECUTE FUNCTION content.update_updated_at_column();

-- job 테이블 트리거
CREATE TRIGGER trg_job_updated_at
  BEFORE UPDATE ON content.job
  FOR EACH ROW
  EXECUTE FUNCTION content.update_updated_at_column();

-- ============================================
-- 트리거 함수 (job completed_at 자동 설정)
-- ============================================

CREATE OR REPLACE FUNCTION content.set_job_completed_at()
RETURNS TRIGGER AS $$
BEGIN
  -- COMPLETED 또는 FAILED 상태로 변경 시 completed_at 설정
  IF NEW.status IN ('COMPLETED', 'FAILED') AND OLD.status NOT IN ('COMPLETED', 'FAILED') THEN
    NEW.completed_at = CURRENT_TIMESTAMP;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_job_completed_at
  BEFORE UPDATE ON content.job
  FOR EACH ROW
  EXECUTE FUNCTION content.set_job_completed_at();

-- ============================================
-- 샘플 데이터 (개발/테스트용)
-- ============================================

-- content 샘플 데이터
INSERT INTO content.content (event_id, event_title, event_description)
VALUES
  ('evt-draft-12345', '봄맞이 커피 할인 이벤트', '신메뉴 아메리카노 1+1 이벤트'),
  ('evt-draft-67890', '신메뉴 출시 기념 경품 추첨', '스타벅스 기프티콘 5000원권 추첨')
ON CONFLICT (event_id) DO NOTHING;

-- generated_image 샘플 데이터
INSERT INTO content.generated_image (
  event_id, style, platform, cdn_url, prompt, width, height, selected
)
VALUES
  (
    'evt-draft-12345', 'SIMPLE', 'INSTAGRAM',
    'https://cdn.kt-event.com/images/evt-draft-12345-simple.png',
    'Clean and simple coffee event poster with spring theme',
    1080, 1080, true
  ),
  (
    'evt-draft-12345', 'FANCY', 'INSTAGRAM',
    'https://cdn.kt-event.com/images/evt-draft-12345-fancy.png',
    'Vibrant and colorful coffee event poster with eye-catching design',
    1080, 1080, false
  ),
  (
    'evt-draft-12345', 'TRENDY', 'INSTAGRAM',
    'https://cdn.kt-event.com/images/evt-draft-12345-trendy.png',
    'Trendy MZ-generation style coffee event poster',
    1080, 1080, false
  )
ON CONFLICT DO NOTHING;

-- job 샘플 데이터
INSERT INTO content.job (id, event_id, job_type, status, progress)
VALUES
  ('job-img-abc123', 'evt-draft-12345', 'IMAGE_GENERATION', 'COMPLETED', 100),
  ('job-img-def456', 'evt-draft-67890', 'IMAGE_GENERATION', 'PROCESSING', 50)
ON CONFLICT (id) DO NOTHING;

-- ============================================
-- 데이터 정리 함수 (배치 작업용)
-- ============================================

-- 90일 이상 된 이미지 삭제
CREATE OR REPLACE FUNCTION content.cleanup_old_images(days_to_keep INT DEFAULT 90)
RETURNS INT AS $$
DECLARE
  deleted_count INT;
BEGIN
  DELETE FROM content.generated_image
  WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '1 day' * days_to_keep;

  GET DIAGNOSTICS deleted_count = ROW_COUNT;
  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION content.cleanup_old_images IS '90일 이상 된 이미지 데이터 정리';

-- 30일 이상 된 Job 데이터 삭제
CREATE OR REPLACE FUNCTION content.cleanup_old_jobs(days_to_keep INT DEFAULT 30)
RETURNS INT AS $$
DECLARE
  deleted_count INT;
BEGIN
  DELETE FROM content.job
  WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '1 day' * days_to_keep;

  GET DIAGNOSTICS deleted_count = ROW_COUNT;
  RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION content.cleanup_old_jobs IS '30일 이상 된 Job 데이터 정리';

-- ============================================
-- 통계 정보 함수
-- ============================================

-- 이벤트별 이미지 생성 통계
CREATE OR REPLACE FUNCTION content.get_image_stats(p_event_id VARCHAR DEFAULT NULL)
RETURNS TABLE (
  event_id VARCHAR,
  total_images BIGINT,
  simple_count BIGINT,
  fancy_count BIGINT,
  trendy_count BIGINT,
  selected_count BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    gi.event_id,
    COUNT(*)::BIGINT as total_images,
    COUNT(CASE WHEN gi.style = 'SIMPLE' THEN 1 END)::BIGINT as simple_count,
    COUNT(CASE WHEN gi.style = 'FANCY' THEN 1 END)::BIGINT as fancy_count,
    COUNT(CASE WHEN gi.style = 'TRENDY' THEN 1 END)::BIGINT as trendy_count,
    COUNT(CASE WHEN gi.selected = true THEN 1 END)::BIGINT as selected_count
  FROM content.generated_image gi
  WHERE p_event_id IS NULL OR gi.event_id = p_event_id
  GROUP BY gi.event_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION content.get_image_stats IS '이벤트별 이미지 생성 통계 조회';

-- ============================================
-- 권한 설정 (운영 환경)
-- ============================================

-- 서비스 계정 생성 (최초 1회만 실행, 필요시 주석 해제)
-- CREATE USER content_service_user WITH PASSWORD 'change_this_password';

-- 스키마 사용 권한
-- GRANT USAGE ON SCHEMA content TO content_service_user;

-- 테이블 권한
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA content TO content_service_user;

-- 시퀀스 권한
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA content TO content_service_user;

-- 함수 실행 권한
-- GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA content TO content_service_user;

-- 기본 권한 설정 (향후 생성되는 객체)
-- ALTER DEFAULT PRIVILEGES IN SCHEMA content
--   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO content_service_user;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA content
--   GRANT USAGE, SELECT ON SEQUENCES TO content_service_user;

-- ============================================
-- 스키마 검증 쿼리
-- ============================================

-- 테이블 목록 확인
-- SELECT table_name, table_type
-- FROM information_schema.tables
-- WHERE table_schema = 'content'
-- ORDER BY table_name;

-- 인덱스 목록 확인
-- SELECT
--   schemaname, tablename, indexname, indexdef
-- FROM pg_indexes
-- WHERE schemaname = 'content'
-- ORDER BY tablename, indexname;

-- 제약 조건 확인
-- SELECT
--   tc.constraint_name, tc.table_name, tc.constraint_type,
--   cc.check_clause
-- FROM information_schema.table_constraints tc
-- LEFT JOIN information_schema.check_constraints cc
--   ON tc.constraint_name = cc.constraint_name
-- WHERE tc.table_schema = 'content'
-- ORDER BY tc.table_name, tc.constraint_type, tc.constraint_name;

-- ============================================
-- 완료 메시지
-- ============================================

DO $$
BEGIN
  RAISE NOTICE '============================================';
  RAISE NOTICE 'Content Service Database Schema Created Successfully!';
  RAISE NOTICE '============================================';
  RAISE NOTICE 'Schema: content';
  RAISE NOTICE 'Tables: content, generated_image, job';
  RAISE NOTICE 'Functions: update_updated_at_column, set_job_completed_at';
  RAISE NOTICE 'Cleanup: cleanup_old_images, cleanup_old_jobs';
  RAISE NOTICE 'Statistics: get_image_stats';
  RAISE NOTICE '============================================';
  RAISE NOTICE 'Sample data inserted for testing';
  RAISE NOTICE '============================================';
END $$;