haneulai
a716dbef96
Some checks are pending
CI / Crawler (Python ${{ matrix.python-version }}) (3.10) (push) Waiting to run
CI / Crawler (Python ${{ matrix.python-version }}) (3.11) (push) Waiting to run
CI / API (Python 3.11) (push) Waiting to run
CI / Database migration (push) Waiting to run
CI / App web build (Node 20) (push) Waiting to run
304 lines
9.8 KiB
PL/PgSQL
304 lines
9.8 KiB
PL/PgSQL
-- Centralize daily-stat write policy and keep monthly aggregates in sync.
|
|
|
|
ALTER TABLE public.daily_stats
|
|
ADD COLUMN IF NOT EXISTS updated_at timestamp with time zone NOT NULL DEFAULT now(),
|
|
ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT 'legacy';
|
|
|
|
UPDATE public.daily_stats
|
|
SET updated_at = COALESCE(created_at, now())
|
|
WHERE updated_at IS NULL;
|
|
|
|
ALTER TABLE public.daily_stats
|
|
DROP CONSTRAINT IF EXISTS daily_stats_source_check;
|
|
|
|
ALTER TABLE public.daily_stats
|
|
ADD CONSTRAINT daily_stats_source_check CHECK (
|
|
source IN ('legacy', 'realtime', 'daily_summary', 'history', 'excel_daily')
|
|
);
|
|
|
|
ALTER TABLE public.monthly_stats
|
|
RENAME COLUMN currnet_last_date TO last_date;
|
|
|
|
ALTER TABLE public.monthly_stats
|
|
ALTER COLUMN last_date TYPE date
|
|
USING CASE
|
|
WHEN last_date IS NULL OR btrim(last_date) = '' THEN NULL
|
|
WHEN last_date ~ '^\d{4}-\d{2}-\d{2}$' THEN last_date::date
|
|
ELSE NULL
|
|
END;
|
|
|
|
ALTER TABLE public.monthly_stats
|
|
ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT 'legacy';
|
|
|
|
ALTER TABLE public.monthly_stats
|
|
DROP CONSTRAINT IF EXISTS monthly_stats_source_check;
|
|
|
|
ALTER TABLE public.monthly_stats
|
|
ADD CONSTRAINT monthly_stats_source_check CHECK (
|
|
source IN ('legacy', 'derived_daily', 'excel_monthly', 'history_monthly')
|
|
);
|
|
|
|
COMMENT ON COLUMN public.daily_stats.updated_at IS '통계 값이 실제로 변경된 시각';
|
|
COMMENT ON COLUMN public.daily_stats.source IS '현재 일 발전량 값을 결정한 저장 경로';
|
|
COMMENT ON COLUMN public.monthly_stats.last_date IS '월간 합계에 포함된 마지막 일자';
|
|
COMMENT ON COLUMN public.monthly_stats.source IS '월간 값의 생성 경로';
|
|
|
|
CREATE OR REPLACE FUNCTION public.refresh_monthly_stat(
|
|
p_plant_id text,
|
|
p_month text
|
|
)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = ''
|
|
AS $$
|
|
DECLARE
|
|
v_month_start date;
|
|
v_next_month date;
|
|
v_total double precision;
|
|
v_last_date date;
|
|
v_count integer;
|
|
BEGIN
|
|
IF p_month !~ '^\d{4}-(0[1-9]|1[0-2])$' THEN
|
|
RAISE EXCEPTION 'invalid month: %', p_month;
|
|
END IF;
|
|
|
|
v_month_start := (p_month || '-01')::date;
|
|
v_next_month := (v_month_start + interval '1 month')::date;
|
|
|
|
SELECT
|
|
COALESCE(sum(ds.total_generation), 0)::double precision,
|
|
max(ds.date),
|
|
count(*)::integer
|
|
INTO v_total, v_last_date, v_count
|
|
FROM public.daily_stats AS ds
|
|
WHERE ds.plant_id = p_plant_id
|
|
AND ds.date >= v_month_start
|
|
AND ds.date < v_next_month;
|
|
|
|
IF v_count = 0 THEN
|
|
DELETE FROM public.monthly_stats AS ms
|
|
WHERE ms.plant_id = p_plant_id
|
|
AND ms.month = p_month
|
|
AND ms.source NOT IN ('excel_monthly', 'history_monthly');
|
|
RETURN;
|
|
END IF;
|
|
|
|
INSERT INTO public.monthly_stats AS ms (
|
|
plant_id,
|
|
month,
|
|
total_generation,
|
|
last_date,
|
|
updated_at,
|
|
source
|
|
) VALUES (
|
|
p_plant_id,
|
|
p_month,
|
|
round(v_total::numeric, 2)::double precision,
|
|
v_last_date,
|
|
now(),
|
|
'derived_daily'
|
|
)
|
|
ON CONFLICT (plant_id, month) DO UPDATE
|
|
SET total_generation = EXCLUDED.total_generation,
|
|
last_date = EXCLUDED.last_date,
|
|
updated_at = CASE
|
|
WHEN ms.total_generation IS DISTINCT FROM EXCLUDED.total_generation
|
|
OR ms.last_date IS DISTINCT FROM EXCLUDED.last_date
|
|
OR ms.source IS DISTINCT FROM EXCLUDED.source
|
|
THEN now()
|
|
ELSE ms.updated_at
|
|
END,
|
|
source = EXCLUDED.source
|
|
WHERE ms.source NOT IN ('excel_monthly', 'history_monthly');
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.sync_monthly_stat_from_daily()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = ''
|
|
AS $$
|
|
BEGIN
|
|
IF TG_OP = 'DELETE' THEN
|
|
PERFORM public.refresh_monthly_stat(OLD.plant_id, to_char(OLD.date, 'YYYY-MM'));
|
|
RETURN OLD;
|
|
END IF;
|
|
|
|
IF TG_OP = 'UPDATE'
|
|
AND OLD.plant_id IS NOT DISTINCT FROM NEW.plant_id
|
|
AND OLD.date IS NOT DISTINCT FROM NEW.date
|
|
AND OLD.total_generation IS NOT DISTINCT FROM NEW.total_generation THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
IF TG_OP = 'UPDATE'
|
|
AND (OLD.plant_id IS DISTINCT FROM NEW.plant_id OR OLD.date IS DISTINCT FROM NEW.date) THEN
|
|
PERFORM public.refresh_monthly_stat(OLD.plant_id, to_char(OLD.date, 'YYYY-MM'));
|
|
END IF;
|
|
|
|
PERFORM public.refresh_monthly_stat(NEW.plant_id, to_char(NEW.date, 'YYYY-MM'));
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS daily_stats_sync_monthly ON public.daily_stats;
|
|
|
|
CREATE TRIGGER daily_stats_sync_monthly
|
|
AFTER INSERT OR UPDATE OR DELETE ON public.daily_stats
|
|
FOR EACH ROW EXECUTE FUNCTION public.sync_monthly_stat_from_daily();
|
|
|
|
CREATE OR REPLACE FUNCTION public.upsert_daily_stats(
|
|
p_records jsonb,
|
|
p_source text,
|
|
p_allow_decrease boolean DEFAULT false
|
|
)
|
|
RETURNS SETOF public.daily_stats
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = ''
|
|
AS $$
|
|
DECLARE
|
|
v_record jsonb;
|
|
v_plant_id text;
|
|
v_date date;
|
|
v_total double precision;
|
|
v_peak double precision;
|
|
v_capacity double precision;
|
|
v_result public.daily_stats%ROWTYPE;
|
|
BEGIN
|
|
IF jsonb_typeof(p_records) <> 'array' THEN
|
|
RAISE EXCEPTION 'p_records must be a JSON array';
|
|
END IF;
|
|
|
|
IF p_source NOT IN ('realtime', 'daily_summary', 'history', 'excel_daily') THEN
|
|
RAISE EXCEPTION 'invalid daily stats source: %', p_source;
|
|
END IF;
|
|
|
|
FOR v_record IN SELECT value FROM jsonb_array_elements(p_records)
|
|
LOOP
|
|
v_plant_id := NULLIF(btrim(v_record ->> 'plant_id'), '');
|
|
v_date := (v_record ->> 'date')::date;
|
|
v_total := (v_record ->> 'total_generation')::double precision;
|
|
v_peak := COALESCE((v_record ->> 'peak_kw')::double precision, 0);
|
|
|
|
IF v_plant_id IS NULL THEN
|
|
RAISE EXCEPTION 'plant_id is required';
|
|
END IF;
|
|
IF v_total < 0 OR v_peak < 0 THEN
|
|
RAISE EXCEPTION 'generation values must be non-negative: % %', v_plant_id, v_date;
|
|
END IF;
|
|
|
|
SELECT COALESCE(p.capacity, 0)
|
|
INTO v_capacity
|
|
FROM public.plants AS p
|
|
WHERE p.id = v_plant_id;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'unknown plant_id: %', v_plant_id;
|
|
END IF;
|
|
|
|
INSERT INTO public.daily_stats AS ds (
|
|
plant_id,
|
|
date,
|
|
total_generation,
|
|
peak_kw,
|
|
generation_hours,
|
|
created_at,
|
|
updated_at,
|
|
source
|
|
) VALUES (
|
|
v_plant_id,
|
|
v_date,
|
|
round(v_total::numeric, 2)::double precision,
|
|
round(v_peak::numeric, 2)::double precision,
|
|
CASE
|
|
WHEN v_capacity > 0
|
|
THEN round((v_total / v_capacity)::numeric, 2)::double precision
|
|
ELSE 0
|
|
END,
|
|
now(),
|
|
now(),
|
|
p_source
|
|
)
|
|
ON CONFLICT (plant_id, date) DO UPDATE
|
|
SET total_generation = CASE
|
|
WHEN p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation
|
|
THEN EXCLUDED.total_generation
|
|
ELSE ds.total_generation
|
|
END,
|
|
peak_kw = GREATEST(COALESCE(ds.peak_kw, 0), EXCLUDED.peak_kw),
|
|
generation_hours = CASE
|
|
WHEN v_capacity > 0 THEN round((
|
|
(CASE
|
|
WHEN p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation
|
|
THEN EXCLUDED.total_generation
|
|
ELSE ds.total_generation
|
|
END) / v_capacity
|
|
)::numeric, 2)::double precision
|
|
ELSE 0
|
|
END,
|
|
updated_at = CASE
|
|
WHEN ds.total_generation IS DISTINCT FROM (
|
|
CASE
|
|
WHEN p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation
|
|
THEN EXCLUDED.total_generation
|
|
ELSE ds.total_generation
|
|
END
|
|
) OR ds.peak_kw IS DISTINCT FROM GREATEST(COALESCE(ds.peak_kw, 0), EXCLUDED.peak_kw)
|
|
THEN now()
|
|
ELSE ds.updated_at
|
|
END,
|
|
source = CASE
|
|
WHEN ds.total_generation IS DISTINCT FROM EXCLUDED.total_generation
|
|
AND (p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation)
|
|
THEN p_source
|
|
ELSE ds.source
|
|
END
|
|
RETURNING ds.* INTO v_result;
|
|
|
|
RETURN NEXT v_result;
|
|
END LOOP;
|
|
|
|
RETURN;
|
|
END;
|
|
$$;
|
|
|
|
REVOKE ALL ON FUNCTION public.upsert_daily_stats(jsonb, text, boolean) FROM PUBLIC;
|
|
GRANT EXECUTE ON FUNCTION public.upsert_daily_stats(jsonb, text, boolean)
|
|
TO anon, authenticated, service_role;
|
|
|
|
REVOKE ALL ON FUNCTION public.refresh_monthly_stat(text, text) FROM PUBLIC;
|
|
GRANT EXECUTE ON FUNCTION public.refresh_monthly_stat(text, text)
|
|
TO authenticated, service_role;
|
|
|
|
-- Preserve legacy monthly totals, but populate their last included daily date.
|
|
UPDATE public.monthly_stats AS ms
|
|
SET last_date = daily.last_date
|
|
FROM (
|
|
SELECT plant_id, to_char(date, 'YYYY-MM') AS month, max(date) AS last_date
|
|
FROM public.daily_stats
|
|
GROUP BY plant_id, to_char(date, 'YYYY-MM')
|
|
) AS daily
|
|
WHERE ms.plant_id = daily.plant_id
|
|
AND ms.month = daily.month
|
|
AND ms.last_date IS DISTINCT FROM daily.last_date;
|
|
|
|
-- Create or refresh only the current KST month. Historical totals remain untouched.
|
|
DO $$
|
|
DECLARE
|
|
v_pair record;
|
|
v_current_month text := to_char(timezone('Asia/Seoul', now()), 'YYYY-MM');
|
|
BEGIN
|
|
FOR v_pair IN
|
|
SELECT DISTINCT plant_id
|
|
FROM public.daily_stats
|
|
WHERE date >= (v_current_month || '-01')::date
|
|
AND date < ((v_current_month || '-01')::date + interval '1 month')::date
|
|
LOOP
|
|
PERFORM public.refresh_monthly_stat(v_pair.plant_id, v_current_month);
|
|
END LOOP;
|
|
END;
|
|
$$;
|