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

-- ============================================================
-- Participation Service Database Schema
-- ============================================================
-- Description: 이벤트 참여자 관리 및 당첨자 추첨 시스템
-- Database: PostgreSQL 15+
-- Author: Backend Developer (최수연 "아키텍처")
-- Created: 2025-10-29
-- Version: v1.0
-- ============================================================

-- 데이터베이스 생성 (필요시)
-- CREATE DATABASE participation_db
--     WITH ENCODING 'UTF8'
--     LC_COLLATE = 'ko_KR.UTF-8'
--     LC_CTYPE = 'ko_KR.UTF-8'
--     TEMPLATE = template0;

-- 스키마 설정
\c participation_db;
SET client_encoding = 'UTF8';
SET timezone = 'Asia/Seoul';

-- ============================================================
-- 1. 테이블 생성
-- ============================================================

-- 1.1 참여자 테이블
CREATE TABLE IF NOT EXISTS participants (
    -- 기본 키
    id BIGSERIAL PRIMARY KEY,

    -- 비즈니스 키
    participant_id VARCHAR(50) NOT NULL,
    event_id VARCHAR(50) NOT NULL,

    -- 참여자 정보
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    email VARCHAR(100),

    -- 참여 정보
    channel VARCHAR(50) NOT NULL,
    store_visited BOOLEAN NOT NULL DEFAULT false,
    bonus_entries INTEGER NOT NULL DEFAULT 1,

    -- 동의 정보
    agree_marketing BOOLEAN NOT NULL DEFAULT false,
    agree_privacy BOOLEAN NOT NULL DEFAULT true,

    -- 당첨 정보
    is_winner BOOLEAN NOT NULL DEFAULT false,
    winner_rank INTEGER,
    won_at TIMESTAMP,

    -- 감사 정보
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.2 추첨 이력 테이블
CREATE TABLE IF NOT EXISTS draw_logs (
    -- 기본 키
    id BIGSERIAL PRIMARY KEY,

    -- 이벤트 정보
    event_id VARCHAR(50) NOT NULL,

    -- 추첨 정보
    total_participants INTEGER NOT NULL,
    winner_count INTEGER NOT NULL,
    apply_store_visit_bonus BOOLEAN NOT NULL DEFAULT false,
    algorithm VARCHAR(50) NOT NULL DEFAULT 'RANDOM',

    -- 추첨 실행 정보
    drawn_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    drawn_by VARCHAR(100) NOT NULL DEFAULT 'SYSTEM',

    -- 감사 정보
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- 2. 제약 조건 생성
-- ============================================================

-- 2.1 participants 테이블 제약 조건

-- Unique Constraints
ALTER TABLE participants
    ADD CONSTRAINT uk_participant_id UNIQUE (participant_id),
    ADD CONSTRAINT uk_event_phone UNIQUE (event_id, phone_number);

-- Check Constraints
ALTER TABLE participants
    ADD CONSTRAINT chk_bonus_entries CHECK (bonus_entries >= 1 AND bonus_entries <= 3),
    ADD CONSTRAINT chk_channel CHECK (channel IN ('WEB', 'MOBILE', 'INSTORE')),
    ADD CONSTRAINT chk_winner_rank CHECK (winner_rank IS NULL OR winner_rank > 0);

-- 2.2 draw_logs 테이블 제약 조건

-- Unique Constraints
ALTER TABLE draw_logs
    ADD CONSTRAINT uk_draw_event UNIQUE (event_id);

-- Check Constraints
ALTER TABLE draw_logs
    ADD CONSTRAINT chk_winner_count CHECK (winner_count > 0),
    ADD CONSTRAINT chk_total_participants CHECK (total_participants >= winner_count),
    ADD CONSTRAINT chk_algorithm CHECK (algorithm IN ('RANDOM', 'WEIGHTED'));

-- ============================================================
-- 3. 인덱스 생성
-- ============================================================

-- 3.1 participants 테이블 인덱스

-- 이벤트별 참여자 조회 (최신순)
CREATE INDEX IF NOT EXISTS idx_participants_event_created
    ON participants(event_id, created_at DESC);

-- 이벤트별 당첨자 조회
CREATE INDEX IF NOT EXISTS idx_participants_event_winner
    ON participants(event_id, is_winner, winner_rank);

-- 매장 방문자 필터링
CREATE INDEX IF NOT EXISTS idx_participants_event_store
    ON participants(event_id, store_visited);

-- 3.2 draw_logs 테이블 인덱스

-- 이벤트별 추첨 이력 조회
CREATE INDEX IF NOT EXISTS idx_draw_logs_event
    ON draw_logs(event_id);

-- 추첨 일시별 조회
CREATE INDEX IF NOT EXISTS idx_draw_logs_drawn_at
    ON draw_logs(drawn_at DESC);

-- ============================================================
-- 4. 트리거 함수 생성
-- ============================================================

-- 4.1 updated_at 자동 갱신 트리거 함수
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 4.2 participants 테이블에 트리거 적용
DROP TRIGGER IF EXISTS trg_participants_updated_at ON participants;
CREATE TRIGGER trg_participants_updated_at
    BEFORE UPDATE ON participants
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- 4.3 draw_logs 테이블에 트리거 적용
DROP TRIGGER IF EXISTS trg_draw_logs_updated_at ON draw_logs;
CREATE TRIGGER trg_draw_logs_updated_at
    BEFORE UPDATE ON draw_logs
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- 5. 샘플 데이터 삽입 (테스트용)
-- ============================================================

-- 5.1 샘플 참여자 데이터
INSERT INTO participants (
    participant_id, event_id, name, phone_number, email,
    channel, store_visited, bonus_entries,
    agree_marketing, agree_privacy
) VALUES
    ('EVT001-20251029-001', 'EVT001', '홍길동', '010-1234-5678', 'hong@example.com', 'WEB', false, 1, true, true),
    ('EVT001-20251029-002', 'EVT001', '김철수', '010-2345-6789', 'kim@example.com', 'MOBILE', false, 1, false, true),
    ('EVT001-20251029-003', 'EVT001', '이영희', '010-3456-7890', 'lee@example.com', 'INSTORE', true, 3, true, true),
    ('EVT001-20251029-004', 'EVT001', '박민수', '010-4567-8901', 'park@example.com', 'WEB', false, 1, true, true),
    ('EVT001-20251029-005', 'EVT001', '정수연', '010-5678-9012', 'jung@example.com', 'INSTORE', true, 3, true, true)
ON CONFLICT (participant_id) DO NOTHING;

-- 5.2 샘플 추첨 이력 데이터
INSERT INTO draw_logs (
    event_id, total_participants, winner_count,
    apply_store_visit_bonus, algorithm, drawn_by
) VALUES
    ('EVT001', 5, 2, true, 'WEIGHTED', 'admin@example.com')
ON CONFLICT (event_id) DO NOTHING;

-- 5.3 당첨자 업데이트 (샘플)
UPDATE participants
SET is_winner = true, winner_rank = 1, won_at = CURRENT_TIMESTAMP
WHERE participant_id = 'EVT001-20251029-003';

UPDATE participants
SET is_winner = true, winner_rank = 2, won_at = CURRENT_TIMESTAMP
WHERE participant_id = 'EVT001-20251029-005';

-- ============================================================
-- 6. 데이터 정합성 검증 쿼리
-- ============================================================

-- 6.1 중복 참여자 확인
SELECT
    event_id,
    phone_number,
    COUNT(*) as duplicate_count
FROM participants
GROUP BY event_id, phone_number
HAVING COUNT(*) > 1;

-- 6.2 당첨자 순위 중복 확인
SELECT
    event_id,
    winner_rank,
    COUNT(*) as duplicate_rank_count
FROM participants
WHERE is_winner = true
GROUP BY event_id, winner_rank
HAVING COUNT(*) > 1;

-- 6.3 추첨 이력 정합성 확인
SELECT
    d.event_id,
    d.winner_count as expected_winners,
    COUNT(p.id) as actual_winners,
    CASE
        WHEN d.winner_count = COUNT(p.id) THEN '정합성 OK'
        ELSE '정합성 오류'
    END as status
FROM draw_logs d
LEFT JOIN participants p ON d.event_id = p.event_id AND p.is_winner = true
GROUP BY d.event_id, d.winner_count;

-- ============================================================
-- 7. 유용한 조회 쿼리
-- ============================================================

-- 7.1 이벤트별 참여 현황 조회
SELECT
    event_id,
    COUNT(*) as total_participants,
    SUM(CASE WHEN store_visited THEN 1 ELSE 0 END) as store_visited_count,
    SUM(bonus_entries) as total_entries,
    COUNT(CASE WHEN is_winner THEN 1 END) as winner_count,
    ROUND(AVG(bonus_entries), 2) as avg_bonus_entries
FROM participants
GROUP BY event_id
ORDER BY event_id;

-- 7.2 채널별 참여 현황 조회
SELECT
    event_id,
    channel,
    COUNT(*) as participant_count,
    SUM(CASE WHEN is_winner THEN 1 ELSE 0 END) as winner_count,
    ROUND(100.0 * SUM(CASE WHEN is_winner THEN 1 ELSE 0 END) / COUNT(*), 2) as win_rate_percent
FROM participants
GROUP BY event_id, channel
ORDER BY event_id, channel;

-- 7.3 당첨자 목록 조회
SELECT
    p.event_id,
    p.participant_id,
    p.name,
    p.phone_number,
    p.winner_rank,
    p.store_visited,
    p.bonus_entries,
    p.won_at,
    d.algorithm
FROM participants p
LEFT JOIN draw_logs d ON p.event_id = d.event_id
WHERE p.is_winner = true
ORDER BY p.event_id, p.winner_rank;

-- 7.4 매장 방문 보너스 효과 분석
SELECT
    event_id,
    store_visited,
    COUNT(*) as participant_count,
    COUNT(CASE WHEN is_winner THEN 1 END) as winner_count,
    ROUND(100.0 * COUNT(CASE WHEN is_winner THEN 1 END) / COUNT(*), 2) as win_rate_percent,
    AVG(bonus_entries) as avg_bonus_entries
FROM participants
GROUP BY event_id, store_visited
ORDER BY event_id, store_visited DESC;

-- ============================================================
-- 8. 권한 설정 (필요시)
-- ============================================================

-- 애플리케이션 사용자 생성 및 권한 부여
-- CREATE USER participation_user WITH PASSWORD 'your_secure_password';
-- GRANT CONNECT ON DATABASE participation_db TO participation_user;
-- GRANT USAGE ON SCHEMA public TO participation_user;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO participation_user;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO participation_user;

-- ============================================================
-- 9. 성능 모니터링 쿼리
-- ============================================================

-- 9.1 테이블 크기 조회
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- 9.2 인덱스 사용률 조회
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- 9.3 느린 쿼리 분석 (pg_stat_statements 확장 필요)
-- SELECT
--     query,
--     calls,
--     total_exec_time,
--     mean_exec_time,
--     min_exec_time,
--     max_exec_time
-- FROM pg_stat_statements
-- WHERE query LIKE '%participants%' OR query LIKE '%draw_logs%'
-- ORDER BY mean_exec_time DESC
-- LIMIT 10;

-- ============================================================
-- 10. 마이그레이션 및 롤백
-- ============================================================

-- 롤백 스크립트 (필요시 실행)
/*
-- 트리거 삭제
DROP TRIGGER IF EXISTS trg_participants_updated_at ON participants;
DROP TRIGGER IF EXISTS trg_draw_logs_updated_at ON draw_logs;

-- 트리거 함수 삭제
DROP FUNCTION IF EXISTS update_updated_at_column();

-- 인덱스 삭제
DROP INDEX IF EXISTS idx_participants_event_created;
DROP INDEX IF EXISTS idx_participants_event_winner;
DROP INDEX IF EXISTS idx_participants_event_store;
DROP INDEX IF EXISTS idx_draw_logs_event;
DROP INDEX IF EXISTS idx_draw_logs_drawn_at;

-- 테이블 삭제
DROP TABLE IF EXISTS draw_logs CASCADE;
DROP TABLE IF EXISTS participants CASCADE;

-- 데이터베이스 삭제 (주의!)
-- DROP DATABASE IF EXISTS participation_db;
*/

-- ============================================================
-- 스키마 생성 완료
-- ============================================================

\echo '=========================================='
\echo 'Participation Service Schema Created Successfully!'
\echo '=========================================='
\echo ''
\echo 'Tables created:'
\echo '  - participants (참여자)'
\echo '  - draw_logs (추첨 이력)'
\echo ''
\echo 'Sample data inserted for testing'
\echo '=========================================='