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

-- ============================================================
-- Analytics Service Database Schema
-- ============================================================
-- Database: analytics_db
-- DBMS: PostgreSQL 16.x
-- Description: 이벤트 분석 및 통계 데이터 관리
-- ============================================================

-- ============================================================
-- 1. 데이터베이스 생성 (필요 시)
-- ============================================================
-- CREATE DATABASE analytics_db
--     WITH
--     OWNER = postgres
--     ENCODING = 'UTF8'
--     LC_COLLATE = 'en_US.UTF-8'
--     LC_CTYPE = 'en_US.UTF-8'
--     TABLESPACE = pg_default
--     CONNECTION LIMIT = -1;

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

-- 2.1 event_stats (이벤트 통계)
DROP TABLE IF EXISTS event_stats CASCADE;

CREATE TABLE event_stats (
    id BIGSERIAL PRIMARY KEY,
    event_id VARCHAR(36) NOT NULL UNIQUE,
    event_title VARCHAR(255) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    total_participants INTEGER NOT NULL DEFAULT 0,
    total_views INTEGER NOT NULL DEFAULT 0,
    estimated_roi DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    target_roi DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    sales_growth_rate DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    total_investment DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    expected_revenue DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- 제약 조건
    CONSTRAINT chk_event_stats_participants CHECK (total_participants >= 0),
    CONSTRAINT chk_event_stats_views CHECK (total_views >= 0),
    CONSTRAINT chk_event_stats_estimated_roi CHECK (estimated_roi >= 0),
    CONSTRAINT chk_event_stats_target_roi CHECK (target_roi >= 0),
    CONSTRAINT chk_event_stats_investment CHECK (total_investment >= 0),
    CONSTRAINT chk_event_stats_revenue CHECK (expected_revenue >= 0),
    CONSTRAINT chk_event_stats_status CHECK (status IN ('ACTIVE', 'ENDED', 'ARCHIVED'))
);

-- event_stats 인덱스
CREATE INDEX idx_event_stats_user_id ON event_stats (user_id);
CREATE INDEX idx_event_stats_status ON event_stats (status);
CREATE INDEX idx_event_stats_created_at ON event_stats (created_at DESC);

-- event_stats 주석
COMMENT ON TABLE event_stats IS '이벤트별 통계 집계 데이터';
COMMENT ON COLUMN event_stats.event_id IS '이벤트 ID (UUID, Event Service 참조)';
COMMENT ON COLUMN event_stats.user_id IS '사용자 ID (UUID, User Service 참조)';
COMMENT ON COLUMN event_stats.total_participants IS '총 참여자 수';
COMMENT ON COLUMN event_stats.total_views IS '총 조회 수';
COMMENT ON COLUMN event_stats.estimated_roi IS '예상 ROI (%)';
COMMENT ON COLUMN event_stats.target_roi IS '목표 ROI (%)';
COMMENT ON COLUMN event_stats.sales_growth_rate IS '매출 성장률 (%)';
COMMENT ON COLUMN event_stats.total_investment IS '총 투자 금액 (원)';
COMMENT ON COLUMN event_stats.expected_revenue IS '예상 수익 (원)';
COMMENT ON COLUMN event_stats.status IS '이벤트 상태 (ACTIVE, ENDED, ARCHIVED)';

-- ============================================================

-- 2.2 channel_stats (채널 통계)
DROP TABLE IF EXISTS channel_stats CASCADE;

CREATE TABLE channel_stats (
    id BIGSERIAL PRIMARY KEY,
    event_id VARCHAR(36) NOT NULL,
    channel_name VARCHAR(50) NOT NULL,
    channel_type VARCHAR(20) NOT NULL,
    impressions INTEGER NOT NULL DEFAULT 0,
    views INTEGER NOT NULL DEFAULT 0,
    clicks INTEGER NOT NULL DEFAULT 0,
    participants INTEGER NOT NULL DEFAULT 0,
    conversions INTEGER NOT NULL DEFAULT 0,
    distribution_cost DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    likes INTEGER NOT NULL DEFAULT 0,
    comments INTEGER NOT NULL DEFAULT 0,
    shares INTEGER NOT NULL DEFAULT 0,
    total_calls INTEGER NOT NULL DEFAULT 0,
    completed_calls INTEGER NOT NULL DEFAULT 0,
    average_duration INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- 제약 조건
    CONSTRAINT uk_channel_stats_event_channel UNIQUE (event_id, channel_name),
    CONSTRAINT chk_channel_stats_impressions CHECK (impressions >= 0),
    CONSTRAINT chk_channel_stats_views CHECK (views >= 0),
    CONSTRAINT chk_channel_stats_clicks CHECK (clicks >= 0),
    CONSTRAINT chk_channel_stats_participants CHECK (participants >= 0),
    CONSTRAINT chk_channel_stats_conversions CHECK (conversions >= 0),
    CONSTRAINT chk_channel_stats_cost CHECK (distribution_cost >= 0),
    CONSTRAINT chk_channel_stats_calls CHECK (total_calls >= 0),
    CONSTRAINT chk_channel_stats_completed CHECK (completed_calls >= 0 AND completed_calls <= total_calls),
    CONSTRAINT chk_channel_stats_duration CHECK (average_duration >= 0),
    CONSTRAINT chk_channel_stats_type CHECK (channel_type IN ('TV', 'SNS', 'VOICE'))
);

-- channel_stats 인덱스
CREATE INDEX idx_channel_stats_event_id ON channel_stats (event_id);
CREATE INDEX idx_channel_stats_channel_type ON channel_stats (channel_type);
CREATE INDEX idx_channel_stats_participants ON channel_stats (participants DESC);

-- channel_stats 주석
COMMENT ON TABLE channel_stats IS '채널별 성과 데이터 (외부 API 연동)';
COMMENT ON COLUMN channel_stats.event_id IS '이벤트 ID (UUID)';
COMMENT ON COLUMN channel_stats.channel_name IS '채널명 (WooriTV, GenieTV, RingoBiz, SNS 등)';
COMMENT ON COLUMN channel_stats.channel_type IS '채널 타입 (TV, SNS, VOICE)';
COMMENT ON COLUMN channel_stats.impressions IS '노출 수';
COMMENT ON COLUMN channel_stats.views IS '조회 수';
COMMENT ON COLUMN channel_stats.clicks IS '클릭 수';
COMMENT ON COLUMN channel_stats.participants IS '참여자 수';
COMMENT ON COLUMN channel_stats.conversions IS '전환 수';
COMMENT ON COLUMN channel_stats.distribution_cost IS '배포 비용 (원)';
COMMENT ON COLUMN channel_stats.likes IS '좋아요 수 (SNS)';
COMMENT ON COLUMN channel_stats.comments IS '댓글 수 (SNS)';
COMMENT ON COLUMN channel_stats.shares IS '공유 수 (SNS)';
COMMENT ON COLUMN channel_stats.total_calls IS '총 통화 수 (VOICE)';
COMMENT ON COLUMN channel_stats.completed_calls IS '완료 통화 수 (VOICE)';
COMMENT ON COLUMN channel_stats.average_duration IS '평균 통화 시간 (초, VOICE)';

-- ============================================================

-- 2.3 timeline_data (시계열 데이터)
DROP TABLE IF EXISTS timeline_data CASCADE;

CREATE TABLE timeline_data (
    id BIGSERIAL PRIMARY KEY,
    event_id VARCHAR(36) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    participants INTEGER NOT NULL DEFAULT 0,
    views INTEGER NOT NULL DEFAULT 0,
    engagement INTEGER NOT NULL DEFAULT 0,
    conversions INTEGER NOT NULL DEFAULT 0,
    cumulative_participants INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- 제약 조건
    CONSTRAINT chk_timeline_participants CHECK (participants >= 0),
    CONSTRAINT chk_timeline_views CHECK (views >= 0),
    CONSTRAINT chk_timeline_engagement CHECK (engagement >= 0),
    CONSTRAINT chk_timeline_conversions CHECK (conversions >= 0),
    CONSTRAINT chk_timeline_cumulative CHECK (cumulative_participants >= 0)
);

-- timeline_data 인덱스
CREATE INDEX idx_timeline_event_timestamp ON timeline_data (event_id, timestamp ASC);
CREATE INDEX idx_timeline_timestamp ON timeline_data (timestamp ASC);

-- timeline_data BRIN 인덱스 (시계열 최적화)
CREATE INDEX idx_timeline_brin_timestamp ON timeline_data USING BRIN (timestamp);

-- timeline_data 주석
COMMENT ON TABLE timeline_data IS '시간별 참여 추이 데이터 (시계열 분석)';
COMMENT ON COLUMN timeline_data.event_id IS '이벤트 ID (UUID)';
COMMENT ON COLUMN timeline_data.timestamp IS '기록 시간';
COMMENT ON COLUMN timeline_data.participants IS '해당 시간 참여자 수';
COMMENT ON COLUMN timeline_data.views IS '해당 시간 조회 수';
COMMENT ON COLUMN timeline_data.engagement IS '참여도 (상호작용 수)';
COMMENT ON COLUMN timeline_data.conversions IS '해당 시간 전환 수';
COMMENT ON COLUMN timeline_data.cumulative_participants IS '누적 참여자 수';

-- ============================================================
-- 3. 트리거 생성 (updated_at 자동 업데이트)
-- ============================================================

-- updated_at 자동 업데이트 함수
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- event_stats 트리거
CREATE TRIGGER trigger_event_stats_updated_at
    BEFORE UPDATE ON event_stats
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- channel_stats 트리거
CREATE TRIGGER trigger_channel_stats_updated_at
    BEFORE UPDATE ON channel_stats
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- timeline_data 트리거
CREATE TRIGGER trigger_timeline_data_updated_at
    BEFORE UPDATE ON timeline_data
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- ============================================================
-- 4. 샘플 데이터 (개발 및 테스트용)
-- ============================================================

-- 4.1 event_stats 샘플 데이터
INSERT INTO event_stats (
    event_id, event_title, user_id,
    total_participants, total_views, estimated_roi, target_roi,
    sales_growth_rate, total_investment, expected_revenue, status
) VALUES
    (
        '123e4567-e89b-12d3-a456-426614174001',
        '신메뉴 출시 기념 이벤트',
        '987fcdeb-51a2-43f9-8765-fedcba987654',
        150, 1200, 18.50, 20.00,
        12.30, 5000000.00, 5925000.00, 'ACTIVE'
    ),
    (
        '223e4567-e89b-12d3-a456-426614174002',
        '여름 시즌 특가 이벤트',
        '987fcdeb-51a2-43f9-8765-fedcba987654',
        320, 2500, 22.00, 25.00,
        15.80, 8000000.00, 9760000.00, 'ACTIVE'
    );

-- 4.2 channel_stats 샘플 데이터
INSERT INTO channel_stats (
    event_id, channel_name, channel_type,
    impressions, views, clicks, participants, conversions,
    distribution_cost, likes, comments, shares
) VALUES
    (
        '123e4567-e89b-12d3-a456-426614174001',
        'WooriTV', 'TV',
        50000, 8000, 1500, 80, 60,
        2000000.00, 0, 0, 0
    ),
    (
        '123e4567-e89b-12d3-a456-426614174001',
        'GenieTV', 'TV',
        40000, 6000, 1200, 50, 40,
        1500000.00, 0, 0, 0
    ),
    (
        '123e4567-e89b-12d3-a456-426614174001',
        'Instagram', 'SNS',
        30000, 5000, 800, 20, 15,
        1000000.00, 1500, 200, 300
    );

-- 4.3 timeline_data 샘플 데이터
INSERT INTO timeline_data (
    event_id, timestamp,
    participants, views, engagement, conversions, cumulative_participants
) VALUES
    (
        '123e4567-e89b-12d3-a456-426614174001',
        '2025-01-29 10:00:00',
        10, 100, 50, 5, 10
    ),
    (
        '123e4567-e89b-12d3-a456-426614174001',
        '2025-01-29 11:00:00',
        15, 150, 70, 8, 25
    ),
    (
        '123e4567-e89b-12d3-a456-426614174001',
        '2025-01-29 12:00:00',
        20, 200, 90, 10, 45
    ),
    (
        '123e4567-e89b-12d3-a456-426614174001',
        '2025-01-29 13:00:00',
        25, 250, 110, 12, 70
    );

-- ============================================================
-- 5. 데이터베이스 사용자 권한 설정
-- ============================================================

-- 5.1 읽기 전용 사용자 (선택 사항)
-- CREATE USER analytics_readonly WITH PASSWORD 'secure_password_readonly';
-- GRANT CONNECT ON DATABASE analytics_db TO analytics_readonly;
-- GRANT USAGE ON SCHEMA public TO analytics_readonly;
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_readonly;

-- 5.2 읽기/쓰기 사용자 (애플리케이션용)
-- CREATE USER analytics_readwrite WITH PASSWORD 'secure_password_readwrite';
-- GRANT CONNECT ON DATABASE analytics_db TO analytics_readwrite;
-- GRANT USAGE ON SCHEMA public TO analytics_readwrite;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO analytics_readwrite;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO analytics_readwrite;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO analytics_readwrite;
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO analytics_readwrite;

-- ============================================================
-- 6. 데이터베이스 통계 수집 (성능 최적화)
-- ============================================================

ANALYZE event_stats;
ANALYZE channel_stats;
ANALYZE timeline_data;

-- ============================================================
-- 7. 파티셔닝 설정 (선택 사항 - 대용량 시계열 데이터)
-- ============================================================

-- 월별 파티셔닝 예시 (timeline_data 테이블)
--
-- DROP TABLE IF EXISTS timeline_data CASCADE;
--
-- CREATE TABLE timeline_data (
--     id BIGSERIAL,
--     event_id VARCHAR(36) NOT NULL,
--     timestamp TIMESTAMP NOT NULL,
--     participants INTEGER NOT NULL DEFAULT 0,
--     views INTEGER NOT NULL DEFAULT 0,
--     engagement INTEGER NOT NULL DEFAULT 0,
--     conversions INTEGER NOT NULL DEFAULT 0,
--     cumulative_participants INTEGER NOT NULL DEFAULT 0,
--     created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
--     updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
--     PRIMARY KEY (id, timestamp)
-- ) PARTITION BY RANGE (timestamp);
--
-- CREATE TABLE timeline_data_2025_01 PARTITION OF timeline_data
--     FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
--
-- CREATE TABLE timeline_data_2025_02 PARTITION OF timeline_data
--     FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
--
-- -- 자동 파티션 생성은 pg_cron 또는 별도 스크립트 활용

-- ============================================================
-- 8. 백업 및 복구 명령어 (참조용)
-- ============================================================

-- 백업
-- pg_dump -U postgres -F c -b -v -f /backup/analytics_$(date +%Y%m%d).dump analytics_db

-- 복구
-- pg_restore -U postgres -d analytics_db -v /backup/analytics_20250129.dump

-- ============================================================
-- 9. 데이터베이스 설정 확인
-- ============================================================

-- 테이블 목록 확인
-- \dt

-- 테이블 구조 확인
-- \d event_stats
-- \d channel_stats
-- \d timeline_data

-- 인덱스 확인
-- \di

-- 제약 조건 확인
-- SELECT conname, contype, pg_get_constraintdef(oid)
-- FROM pg_constraint
-- WHERE conrelid = 'event_stats'::regclass;

-- ============================================================
-- END OF SCRIPT
-- ============================================================