-- ==============================================================================
-- Initial Schema Migration Dump
-- Generated: 2026-04-14
-- Description: 현재 Supabase DB에 존재하는 전체 테이블 구조 요약 (의존성 순서)
-- ==============================================================================

-- ------------------------------------------------------------------------------
-- 1. COMPANIES (업체 및 고객사 정보)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.companies (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ------------------------------------------------------------------------------
-- 2. PLANTS (발전소 기본 정보)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.plants (
    id TEXT PRIMARY KEY,
    company_id BIGINT REFERENCES public.companies(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    type TEXT,
    capacity DOUBLE PRECISION DEFAULT 100.0,
    initial_cumulative_kwh DOUBLE PRECISION DEFAULT 0.0,
    constructed_at DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

COMMENT ON COLUMN public.plants.initial_cumulative_kwh IS '시스템 도입 이전의 누적 발전량 (기초 자산)';

-- ------------------------------------------------------------------------------
-- 3. SOLAR_LOGS (실시간/시간 단위 발전 로그)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.solar_logs (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    plant_id TEXT REFERENCES public.plants(id) ON DELETE CASCADE,
    current_kw DOUBLE PRECISION,
    today_kwh DOUBLE PRECISION,
    status TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ------------------------------------------------------------------------------
-- 4. DAILY_STATS (일일 발전 통계 요약)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.daily_stats (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    plant_id TEXT REFERENCES public.plants(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    total_generation DOUBLE PRECISION DEFAULT 0.0,
    peak_kw DOUBLE PRECISION DEFAULT 0.0,
    generation_hours DOUBLE PRECISION DEFAULT 0.0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    UNIQUE(plant_id, date) -- 크롤러에서 UPSERT시 사용되는 제약조건
);

COMMENT ON TABLE public.daily_stats IS '일일 발전 통계 요약 테이블';
COMMENT ON COLUMN public.daily_stats.total_generation IS '당일 총 발전량 (kWh)';
COMMENT ON COLUMN public.daily_stats.peak_kw IS '당일 최고 출력 (kW)';
COMMENT ON COLUMN public.daily_stats.generation_hours IS '발전 시간 = 발전량 / 설비용량';

CREATE INDEX IF NOT EXISTS idx_daily_stats_plant_id ON public.daily_stats(plant_id);
CREATE INDEX IF NOT EXISTS idx_daily_stats_date ON public.daily_stats(date);
CREATE INDEX IF NOT EXISTS idx_daily_stats_plant_date ON public.daily_stats(plant_id, date);

-- ------------------------------------------------------------------------------
-- 5. MONTHLY_STATS (월간 발전 통계 요약)
-- ------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS public.monthly_stats (
    id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    plant_id TEXT REFERENCES public.plants(id) ON DELETE CASCADE,
    month TEXT NOT NULL,
    total_generation DOUBLE PRECISION DEFAULT 0.0,
    currnet_last_date TEXT,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW()),
    
    UNIQUE(plant_id, month) -- 크롤러에서 UPSERT시 사용되는 제약조건
);