매출 대시보드 만들기

-- Supabase 결제 테이블 스키마
CREATE TABLE payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  amount NUMERIC NOT NULL,
  currency TEXT DEFAULT 'KRW',
  source TEXT NOT NULL,  -- 'stripe', 'toss', 'gumroad', 'inflearn'
  status TEXT NOT NULL,  -- 'paid', 'refunded', 'failed'
  billing_period TEXT,   -- 'monthly', 'yearly', 'one_time'
  paid_at TIMESTAMPTZ NOT NULL,
  metadata JSONB
);

CREATE INDEX idx_payments_paid_at ON payments(paid_at DESC);
CREATE INDEX idx_payments_user_id ON payments(user_id);

-- 월별 MRR 뷰
CREATE VIEW monthly_mrr AS
SELECT
  DATE_TRUNC('month', paid_at) AS month,
  SUM(CASE
    WHEN billing_period = 'yearly' THEN amount / 12
    ELSE amount
  END) AS mrr,
  COUNT(DISTINCT user_id) AS active_subscribers
FROM payments
WHERE status = 'paid'
GROUP BY 1
ORDER BY 1 DESC;

// app/api/admin/dashboard/route.ts
import { createClient } from '@/lib/supabase/server';
import { NextResponse } from 'next/server';

export async function GET() {
  const supabase = createClient();

  // 1. 월별 MRR (최근 12개월)
  const { data: mrr } = await supabase
    .from('monthly_mrr')
    .select('*')
    .limit(12);

  // 2. 수익원별 분포 (이번 달)
  const { data: bySource } = await supabase
    .from('payments')
    .select('source, amount.sum()')
    .gte('paid_at', new Date(new Date().setDate(1)).toISOString())
    .eq('status', 'paid');

  // 3. 해지율 (Churn)
  const { data: churn } = await supabase.rpc('calculate_churn_rate', {
    period: 'last_30_days',
  });

  // 4. LTV (평균 사용자 평생 가치)
  const { data: ltv } = await supabase.rpc('calculate_avg_ltv');

  return NextResponse.json({ mrr, bySource, churn, ltv });
}

'use client';
import { LineChart, Line, BarChart, Bar, XAxis, YAxis, Tooltip,
         ResponsiveContainer, PieChart, Pie, Cell } from 'recharts';
import { useEffect, useState } from 'react';

interface DashboardData {
  mrr: { month: string; mrr: number; active_subscribers: number }[];
  bySource: { source: string; sum: number }[];
  churn: number;
  ltv: number;
}

export default function RevenueDashboard() {
  const [data, setData] = useState<DashboardData | null>(null);

  useEffect(() => {
    fetch('/api/admin/dashboard')
      .then(r => r.json())
      .then(setData);
  }, []);

  if (!data) return <div>Loading...</div>;

  const colors = ['#6366f1', '#10b981', '#f59e0b', '#a78bfa', '#60a5fa'];

  return (
    <div style={{ display: 'grid', gridTemplateColumns: 'repeat(auto-fit, minmax(400px, 1fr))', gap: 24 }}>
      {/* KPI 카드 */}
      <div className="kpi-card">
        <div className="kpi-label">월 MRR</div>
        <div className="kpi-value">${data.mrr[0]?.mrr.toLocaleString()}</div>
        <div className="kpi-change">전월 대비 +12%</div>
      </div>
      <div className="kpi-card">
        <div className="kpi-label">활성 구독자</div>
        <div className="kpi-value">{data.mrr[0]?.active_subscribers}</div>
      </div>
      <div className="kpi-card">
        <div className="kpi-label">월 해지율</div>
        <div className="kpi-value">{(data.churn * 100).toFixed(1)}%</div>
        <div className={data.churn > 0.05 ? 'warning' : 'good'}>
          {data.churn > 0.05 ? '⚠️ 위험' : '✅ 건강'}
        </div>
      </div>
      <div className="kpi-card">
        <div className="kpi-label">평균 LTV</div>
        <div className="kpi-value">${data.ltv.toFixed(0)}</div>
      </div>

      {/* MRR 추이 */}
      <div className="chart-card" style={{ gridColumn: 'span 2' }}>
        <h3>MRR 추이 (12개월)</h3>
        <ResponsiveContainer width="100%" height={300}>
          <LineChart data={data.mrr.slice().reverse()}>
            <XAxis dataKey="month" />
            <YAxis />
            <Tooltip />
            <Line type="monotone" dataKey="mrr" stroke="#6366f1" strokeWidth={2} />
          </LineChart>
        </ResponsiveContainer>
      </div>

      {/* 수익원 분포 */}
      <div className="chart-card">
        <h3>수익원 분포 (이번 달)</h3>
        <ResponsiveContainer width="100%" height={250}>
          <PieChart>
            <Pie data={data.bySource} dataKey="sum" nameKey="source" cx="50%" cy="50%" outerRadius={80} label>
              {data.bySource.map((_, i) => (
                <Cell key={i} fill={colors[i % colors.length]} />
              ))}
            </Pie>
            <Tooltip />
          </PieChart>
        </ResponsiveContainer>
      </div>
    </div>
  );
}

// app/admin/layout.tsx
import { redirect } from 'next/navigation';
import { createClient } from '@/lib/supabase/server';

export default async function AdminLayout({ children }: { children: React.ReactNode }) {
  const supabase = createClient();
  const { data: { user } } = await supabase.auth.getUser();

  if (!user) redirect('/login');

  const { data: profile } = await supabase
    .from('profiles')
    .select('role')
    .eq('id', user.id)
    .single();

  if (profile?.role !== 'admin') redirect('/');

  return <div>{children}</div>;
}

// 매일 오전 9시 — 어제 매출 슬랙으로 보내기
async function dailyRevenueAlert() {
  const yesterday = await fetchYesterdayRevenue();
  await fetch(process.env.SLACK_WEBHOOK!, {
    method: 'POST',
    body: JSON.stringify({
      text: `📊 어제 매출: $${yesterday.total}
` +
            `신규 구독: ${yesterday.newSubs}
` +
            `해지: ${yesterday.churn}
` +
            `MRR: $${yesterday.mrr}`,
    }),
  });
}