Spaces:

Esketch
/

CarboAny

Sleeping

App Files Files Community

CarboAny / src /lib /admin.ts

Esketch

Claude Fable 5

deploy: Campaign Control Plane + Security Hardening Release (Orphan Clean Build v3)

5c98911 4 months ago

Raw History Blame Contribute Delete

7.73 kB

import { supabase } from './supabase';
import type { CampaignStatus } from './database.types';
import type { Program } from '../types';
import { mockPrograms, resolveMockProgramId } from '../data/mockPrograms';

export interface AdminProgramSummary {
  id: string;
  title: string;
  sponsorName: string;
  status: string;
  startDate: string;
  endDate?: string;
  budgetKrw: number;
  targetCo2Kg: number;
  currentCo2Kg: number;
  totalSubmissions: number;
  pendingSubmissions: number;
  participantCount: number;
  achievementRate: number;
  source: 'db' | 'demo';
}

export interface AdminSettlementStat {
  asset: 'KRW' | 'POINT' | 'TON';
  category: string;
  totalAmount: number;
  txCount: number;
  lastTxAt: string;
}

function toSettlementAsset(value: unknown): AdminSettlementStat['asset'] {
  return value === 'POINT' || value === 'TON' ? value : 'KRW';
}

function isMissingSchemaError(error: { code?: string } | null) {
  return error?.code === 'PGRST205' || error?.code === '42P01';
}

function normalizeStatus(value: unknown): CampaignStatus {
  if (
    value === 'draft' ||
    value === 'pending_review' ||
    value === 'active' ||
    value === 'paused' ||
    value === 'completed'
  ) {
    return value;
  }
  return 'draft';
}

function inferOrganizationType(
  sponsorName: string
): Program['organizationType'] {
  if (/군청|시청|도청|구청|특별시|광역시/.test(sponsorName)) return 'municipality';
  if (/공사|공단|재단|공공|정부|부처|청$/.test(sponsorName)) return 'public_corp';
  return 'enterprise';
}

function dbSummaryToProgram(row: AdminProgramSummary): Program {
  const approvedVerifications = Math.max(
    0,
    row.totalSubmissions - row.pendingSubmissions
  );

  return {
    id: row.id,
    title: row.title,
    programType: inferOrganizationType(row.sponsorName) === 'enterprise'
      ? 'corporate_event'
      : 'public_caas',
    organizationName: row.sponsorName || '미등록 스폰서',
    organizationType: inferOrganizationType(row.sponsorName || ''),
    status: normalizeStatus(row.status),
    startDate: row.startDate,
    endDate: row.endDate || '진행 중',
    targetParticipants: row.participantCount,
    currentParticipants: row.participantCount,
    totalVerifications: row.totalSubmissions,
    approvedVerifications,
    rejectedVerifications: 0,
    pendingVerifications: row.pendingSubmissions,
    co2ReductionKg: row.currentCo2Kg,
    budgetKRW: row.budgetKrw,
    pointsDistributed: 0,
    methodology: 'DB 캠페인 — 상세 방법론 정보 미등록',
    methodologyAutoAssigned: false,
    apiCallsTotal: row.totalSubmissions,
    apiCallsToday: 0,
    fraudDetected: 0,
    verifications: [],
  };
}

function simulatedAdminPrograms(): AdminProgramSummary[] {
  return mockPrograms.map(p => ({
    id: p.id,
    title: p.title,
    sponsorName: p.organizationName,
    status: p.status,
    startDate: p.startDate,
    endDate: p.endDate,
    budgetKrw: p.budgetKRW || 0,
    targetCo2Kg: p.co2ReductionKg || 0,
    currentCo2Kg: p.co2ReductionKg || 0,
    totalSubmissions: p.totalVerifications || 0,
    pendingSubmissions: p.pendingVerifications || 0,
    participantCount: p.currentParticipants || 0,
    achievementRate: 0,
    source: 'demo',
  }));
}

function mapAdminProgramRow(row: Record<string, unknown>): AdminProgramSummary {
  const campaignId = String(row.campaign_id ?? '');

  return {
    id: resolveMockProgramId(campaignId) ?? campaignId,
    title: String(row.title ?? '제목 미등록 캠페인'),
    sponsorName: String(row.sponsor_name ?? '미등록 스폰서'),
    status: String(row.status ?? 'draft'),
    startDate: String(row.start_date ?? ''),
    endDate: row.end_date ? String(row.end_date) : undefined,
    budgetKrw: Number(row.budget_krw ?? 0),
    targetCo2Kg: Number(row.target_co2_kg ?? 0),
    currentCo2Kg: Number(row.current_co2_kg ?? 0),
    totalSubmissions: Number(row.total_submissions ?? 0),
    pendingSubmissions: Number(row.pending_submissions ?? 0),
    participantCount: Number(row.participant_count ?? 0),
    achievementRate: Number(row.achievement_rate ?? 0),
    source: 'db',
  };
}

/**
 * 전체 프로그램(캠페인) 목록 및 요약 정보 조회
 */
export async function getAdminPrograms(): Promise<AdminProgramSummary[]> {
  const { data, error } = await supabase
    .from('admin_program_summary')
    .select('*')
    .order('start_date', { ascending: false });

  if (error) {
    if (!isMissingSchemaError(error)) console.error('getAdminPrograms error:', error);
    return simulatedAdminPrograms();
  }

  const dbPrograms = (data || []).map(mapAdminProgramRow);

  // Mock 데이터와 병합 (완도군 프로젝트 강제 노출)
  const dbProgramIds = new Set(dbPrograms.map((program) => program.id));
  return [
    ...dbPrograms,
    ...simulatedAdminPrograms().filter((program) => !dbProgramIds.has(program.id)),
  ];
}

export async function getAdminProgram(id: string): Promise<Program | null> {
  const canonicalId = resolveMockProgramId(id) ?? id;
  const mockProgram = mockPrograms.find((program) => program.id === canonicalId);
  if (mockProgram) return mockProgram;

  const { data, error } = await supabase
    .from('admin_program_summary')
    .select('*')
    .eq('campaign_id', id)
    .maybeSingle();

  if (error) {
    if (!isMissingSchemaError(error)) console.error('getAdminProgram error:', error);
    return null;
  }

  return data ? dbSummaryToProgram(mapAdminProgramRow(data)) : null;
}

/**
 * 전역 정산 현황 데이터 조회
 */
export async function getGlobalSettlementStats(): Promise<AdminSettlementStat[]> {
  const { data, error } = await supabase
    .from('admin_global_settlement_stats')
    .select('*');

  if (error) {
    if (!isMissingSchemaError(error)) console.error('getGlobalSettlementStats error:', error);
    return [];
  }

  return (data || []).map((row) => ({
    asset: toSettlementAsset(row.asset),
    category: row.category,
    totalAmount: Number(row.total_amount),
    txCount: row.tx_count,
    lastTxAt: row.last_tx_at,
  }));
}

export interface AdminVerificationProject {
  id: string;
  title: string;
  sponsorName: string;
  registry: string;
  methodology: string;
  reductionTons: number;
  stage: 'pending' | 'submitted' | 'reviewing' | 'approved' | 'rejected';
  verifier?: string;
  slaRemainingDays?: number;
  lastUpdatedAt: string;
}

/**
 * 외부 레지스트리(VVB) 검증 프로젝트 목록 조회
 */
export async function getVerificationProjects(): Promise<AdminVerificationProject[]> {
  // 실제로는 external_registry_mapping 테이블 등과 조인 필요
  // 우선은 캠페인 목록을 기반으로 검증 상태를 시뮬레이션하여 반환 (추후 테이블 확정 시 수정)
  const { data, error } = await supabase
    .from('admin_program_summary')
    .select('*');

  if (error) return [];

  return (data || []).map((row) => ({
    id: row.campaign_id,
    title: row.title,
    sponsorName: row.sponsor_name,
    registry: 'VCS', // 임시 매핑
    methodology: 'VM0042', // 임시 매핑
    reductionTons: Number(row.current_co2_kg) / 1000,
    stage: row.status === 'completed' ? 'approved' : 'reviewing',
    verifier: 'KQA (한국품질재단)',
    slaRemainingDays: 14,
    lastUpdatedAt: new Date().toISOString(),
  }));
}

/**
 * 특정 캠페인의 검증 통계 조회
 */
export async function getVerificationStats(campaignId?: string) {
  let query = supabase.from('admin_verification_stats').select('*');
  if (campaignId) query = query.eq('campaign_id', campaignId);

  const { data, error } = await query;
  if (error) {
    console.error('getVerificationStats error:', error);
    return [];
  }
  return data;
}

	import { supabase } from './supabase';
	import type { CampaignStatus } from './database.types';
	import type { Program } from '../types';
	import { mockPrograms, resolveMockProgramId } from '../data/mockPrograms';

	export interface AdminProgramSummary {
	id: string;
	title: string;
	sponsorName: string;
	status: string;
	startDate: string;
	endDate?: string;
	budgetKrw: number;
	targetCo2Kg: number;
	currentCo2Kg: number;
	totalSubmissions: number;
	pendingSubmissions: number;
	participantCount: number;
	achievementRate: number;
	source: 'db' \| 'demo';
	}

	export interface AdminSettlementStat {
	asset: 'KRW' \| 'POINT' \| 'TON';
	category: string;
	totalAmount: number;
	txCount: number;
	lastTxAt: string;
	}

	function toSettlementAsset(value: unknown): AdminSettlementStat['asset'] {
	return value === 'POINT' \|\| value === 'TON' ? value : 'KRW';
	}

	function isMissingSchemaError(error: { code?: string } \| null) {
	return error?.code === 'PGRST205' \|\| error?.code === '42P01';
	}

	function normalizeStatus(value: unknown): CampaignStatus {
	if (
	value === 'draft' \|\|
	value === 'pending_review' \|\|
	value === 'active' \|\|
	value === 'paused' \|\|
	value === 'completed'
	) {
	return value;
	}
	return 'draft';
	}

	function inferOrganizationType(
	sponsorName: string
	): Program['organizationType'] {
	if (/군청\|시청\|도청\|구청\|특별시\|광역시/.test(sponsorName)) return 'municipality';
	if (/공사\|공단\|재단\|공공\|정부\|부처\|청$/.test(sponsorName)) return 'public_corp';
	return 'enterprise';
	}

	function dbSummaryToProgram(row: AdminProgramSummary): Program {
	const approvedVerifications = Math.max(
	0,
	row.totalSubmissions - row.pendingSubmissions
	);

	return {
	id: row.id,
	title: row.title,
	programType: inferOrganizationType(row.sponsorName) === 'enterprise'
	? 'corporate_event'
	: 'public_caas',
	organizationName: row.sponsorName \|\| '미등록 스폰서',
	organizationType: inferOrganizationType(row.sponsorName \|\| ''),
	status: normalizeStatus(row.status),
	startDate: row.startDate,
	endDate: row.endDate \|\| '진행 중',
	targetParticipants: row.participantCount,
	currentParticipants: row.participantCount,
	totalVerifications: row.totalSubmissions,
	approvedVerifications,
	rejectedVerifications: 0,
	pendingVerifications: row.pendingSubmissions,
	co2ReductionKg: row.currentCo2Kg,
	budgetKRW: row.budgetKrw,
	pointsDistributed: 0,
	methodology: 'DB 캠페인 — 상세 방법론 정보 미등록',
	methodologyAutoAssigned: false,
	apiCallsTotal: row.totalSubmissions,
	apiCallsToday: 0,
	fraudDetected: 0,
	verifications: [],
	};
	}

	function simulatedAdminPrograms(): AdminProgramSummary[] {
	return mockPrograms.map(p => ({
	id: p.id,
	title: p.title,
	sponsorName: p.organizationName,
	status: p.status,
	startDate: p.startDate,
	endDate: p.endDate,
	budgetKrw: p.budgetKRW \|\| 0,
	targetCo2Kg: p.co2ReductionKg \|\| 0,
	currentCo2Kg: p.co2ReductionKg \|\| 0,
	totalSubmissions: p.totalVerifications \|\| 0,
	pendingSubmissions: p.pendingVerifications \|\| 0,
	participantCount: p.currentParticipants \|\| 0,
	achievementRate: 0,
	source: 'demo',
	}));
	}

	function mapAdminProgramRow(row: Record<string, unknown>): AdminProgramSummary {
	const campaignId = String(row.campaign_id ?? '');

	return {
	id: resolveMockProgramId(campaignId) ?? campaignId,
	title: String(row.title ?? '제목 미등록 캠페인'),
	sponsorName: String(row.sponsor_name ?? '미등록 스폰서'),
	status: String(row.status ?? 'draft'),
	startDate: String(row.start_date ?? ''),
	endDate: row.end_date ? String(row.end_date) : undefined,
	budgetKrw: Number(row.budget_krw ?? 0),
	targetCo2Kg: Number(row.target_co2_kg ?? 0),
	currentCo2Kg: Number(row.current_co2_kg ?? 0),
	totalSubmissions: Number(row.total_submissions ?? 0),
	pendingSubmissions: Number(row.pending_submissions ?? 0),
	participantCount: Number(row.participant_count ?? 0),
	achievementRate: Number(row.achievement_rate ?? 0),
	source: 'db',
	};
	}

	/**
	* 전체 프로그램(캠페인) 목록 및 요약 정보 조회
	*/
	export async function getAdminPrograms(): Promise<AdminProgramSummary[]> {
	const { data, error } = await supabase
	.from('admin_program_summary')
	.select('*')
	.order('start_date', { ascending: false });

	if (error) {
	if (!isMissingSchemaError(error)) console.error('getAdminPrograms error:', error);
	return simulatedAdminPrograms();
	}

	const dbPrograms = (data \|\| []).map(mapAdminProgramRow);

	// Mock 데이터와 병합 (완도군 프로젝트 강제 노출)
	const dbProgramIds = new Set(dbPrograms.map((program) => program.id));
	return [
	...dbPrograms,
	...simulatedAdminPrograms().filter((program) => !dbProgramIds.has(program.id)),
	];
	}

	export async function getAdminProgram(id: string): Promise<Program \| null> {
	const canonicalId = resolveMockProgramId(id) ?? id;
	const mockProgram = mockPrograms.find((program) => program.id === canonicalId);
	if (mockProgram) return mockProgram;

	const { data, error } = await supabase
	.from('admin_program_summary')
	.select('*')
	.eq('campaign_id', id)
	.maybeSingle();

	if (error) {
	if (!isMissingSchemaError(error)) console.error('getAdminProgram error:', error);
	return null;
	}

	return data ? dbSummaryToProgram(mapAdminProgramRow(data)) : null;
	}

	/**
	* 전역 정산 현황 데이터 조회
	*/
	export async function getGlobalSettlementStats(): Promise<AdminSettlementStat[]> {
	const { data, error } = await supabase
	.from('admin_global_settlement_stats')
	.select('*');

	if (error) {
	if (!isMissingSchemaError(error)) console.error('getGlobalSettlementStats error:', error);
	return [];
	}

	return (data \|\| []).map((row) => ({
	asset: toSettlementAsset(row.asset),
	category: row.category,
	totalAmount: Number(row.total_amount),
	txCount: row.tx_count,
	lastTxAt: row.last_tx_at,
	}));
	}

	export interface AdminVerificationProject {
	id: string;
	title: string;
	sponsorName: string;
	registry: string;
	methodology: string;
	reductionTons: number;
	stage: 'pending' \| 'submitted' \| 'reviewing' \| 'approved' \| 'rejected';
	verifier?: string;
	slaRemainingDays?: number;
	lastUpdatedAt: string;
	}

	/**
	* 외부 레지스트리(VVB) 검증 프로젝트 목록 조회
	*/
	export async function getVerificationProjects(): Promise<AdminVerificationProject[]> {
	// 실제로는 external_registry_mapping 테이블 등과 조인 필요
	// 우선은 캠페인 목록을 기반으로 검증 상태를 시뮬레이션하여 반환 (추후 테이블 확정 시 수정)
	const { data, error } = await supabase
	.from('admin_program_summary')
	.select('*');

	if (error) return [];

	return (data \|\| []).map((row) => ({
	id: row.campaign_id,
	title: row.title,
	sponsorName: row.sponsor_name,
	registry: 'VCS', // 임시 매핑
	methodology: 'VM0042', // 임시 매핑
	reductionTons: Number(row.current_co2_kg) / 1000,
	stage: row.status === 'completed' ? 'approved' : 'reviewing',
	verifier: 'KQA (한국품질재단)',
	slaRemainingDays: 14,
	lastUpdatedAt: new Date().toISOString(),
	}));
	}

	/**
	* 특정 캠페인의 검증 통계 조회
	*/
	export async function getVerificationStats(campaignId?: string) {
	let query = supabase.from('admin_verification_stats').select('*');
	if (campaignId) query = query.eq('campaign_id', campaignId);

	const { data, error } = await query;
	if (error) {
	console.error('getVerificationStats error:', error);
	return [];
	}
	return data;
	}