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;
}