-- =============================================================
-- SNP Connection Monitoring - Health Log 파티션 마이그레이션
-- snp_service_health_log → 일별 Range 파티션 전환
-- 스키마: common
-- =============================================================

-- 1. 백업
CREATE TABLE common.snp_service_health_log_backup AS
SELECT * FROM common.snp_service_health_log;

-- 2. 기존 테이블 삭제
DROP TABLE common.snp_service_health_log;

-- 3. 파티션 테이블 생성
CREATE TABLE common.snp_service_health_log (
    log_id          BIGSERIAL,
    service_id      BIGINT          NOT NULL,
    previous_status VARCHAR(10),
    current_status  VARCHAR(10)     NOT NULL,
    response_time   INTEGER,
    error_message   TEXT,
    checked_at      TIMESTAMP       NOT NULL DEFAULT NOW(),
    PRIMARY KEY (log_id, checked_at)
) PARTITION BY RANGE (checked_at);

-- 4. 인덱스 (파티션에 자동 상속)
CREATE INDEX idx_snp_health_log_service ON common.snp_service_health_log (service_id);
CREATE INDEX idx_snp_health_log_checked ON common.snp_service_health_log (checked_at);
CREATE INDEX idx_health_log_svc_checked ON common.snp_service_health_log (service_id, checked_at DESC);
CREATE INDEX idx_health_log_daily_uptime ON common.snp_service_health_log (service_id, checked_at, current_status);

-- 5. 오늘 + 미래 7일 파티션 생성 (실행 시점에 맞게 날짜 수정)
-- 예시: 2026-04-13 실행 기준
CREATE TABLE common.snp_service_health_log_20260413
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-13') TO ('2026-04-14');
CREATE TABLE common.snp_service_health_log_20260414
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-14') TO ('2026-04-15');
CREATE TABLE common.snp_service_health_log_20260415
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-15') TO ('2026-04-16');
CREATE TABLE common.snp_service_health_log_20260416
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-16') TO ('2026-04-17');
CREATE TABLE common.snp_service_health_log_20260417
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-17') TO ('2026-04-18');
CREATE TABLE common.snp_service_health_log_20260418
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-18') TO ('2026-04-19');
CREATE TABLE common.snp_service_health_log_20260419
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-19') TO ('2026-04-20');
CREATE TABLE common.snp_service_health_log_20260420
    PARTITION OF common.snp_service_health_log
    FOR VALUES FROM ('2026-04-20') TO ('2026-04-21');

-- 6. 데이터 복원 (컬럼 명시)
INSERT INTO common.snp_service_health_log (
    log_id, service_id, previous_status, current_status, response_time, error_message, checked_at
) SELECT
    log_id, service_id, previous_status, current_status, response_time, error_message, checked_at
FROM common.snp_service_health_log_backup
WHERE checked_at >= '2026-04-13';

-- 7. 시퀀스 리셋
SELECT setval(pg_get_serial_sequence('common.snp_service_health_log', 'log_id'),
    (SELECT COALESCE(MAX(log_id), 0) FROM common.snp_service_health_log));

-- 8. 백업 삭제 (확인 후)
-- DROP TABLE common.snp_service_health_log_backup;