import { AnalyticsDashboard } from "@/components/reports/analytics-dashboard";
import { PageHeader } from "@/components/dashboard/page-header";
import { orphanDocumentLabels } from "@/lib/constants";
import { currentPeriod } from "@/lib/dates";
import {
  type ReportDataset,
  type ReportFilters,
  type ReportOrphan,
  type ReportStatusFilter
} from "@/lib/report-analytics";
import { createAdminSupabaseClient } from "@/lib/supabase/admin";

const DATABASE_PAGE_SIZE = 1_000;
const UUID_PATTERN = /^[0-9a-f]{8}-[0-9a-f]{4}-[1-5][0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$/i;
const REPORT_STATUSES = new Set<ReportStatusFilter>([
  "all",
  "payments_paid",
  "payments_unpaid",
  "transfers_completed",
  "missing_receipt"
]);

type SearchParams = {
  year?: string;
  month?: string;
  sponsor?: string;
  orphan?: string;
  status?: string;
};

type Relation<T> = T | T[] | null;

type RawProfile = {
  full_name: string;
  phone?: string | null;
};

type RawOrphanRelation = {
  case_number: string;
  full_name: string | null;
};

type RawPayment = {
  id: string;
  sponsorship_id: string;
  sponsor_id: string;
  orphan_id: string;
  payment_year: number;
  payment_month: number;
  expected_amount_snapshot: number | string;
  received_amount: number | string;
  payment_status: "unpaid" | "partial" | "paid";
  payment_date: string | null;
  profiles: Relation<RawProfile>;
  orphans: Relation<RawOrphanRelation>;
};

type RawTransferSponsor = {
  sponsor_id: string;
  profiles: Relation<RawProfile>;
};

type RawTransfer = {
  id: string;
  orphan_id: string;
  transfer_year: number;
  transfer_month: number;
  amount: number | string;
  transfer_date: string;
  orphans: Relation<RawOrphanRelation>;
  orphan_transfer_sponsors: Relation<RawTransferSponsor>;
  transfer_receipts: Relation<{ id: string }>;
};

type RawSponsorship = {
  id: string;
  donor_id: string;
  orphan_id: string;
  monthly_amount: number | string;
  status: "pending" | "active" | "paused" | "ended" | "rejected";
  start_date: string;
  end_date: string | null;
  profiles: Relation<RawProfile>;
  orphans: Relation<RawOrphanRelation>;
};

type RawOrphan = {
  id: string;
  case_number: string;
  full_name: string | null;
  date_of_birth: string | null;
  gender: "male" | "female" | null;
  guardian_name: string | null;
  guardian_phone: string | null;
  guardian_relationship: string | null;
  address: string | null;
  bank_name: string | null;
  bank_account_name: string | null;
  bank_account_number: string | null;
  monthly_need: number | string | null;
  covered_amount: number | string;
  status: "available" | "partially_covered" | "fully_covered" | "paused" | "archived";
  orphan_documents: Relation<{ document_type: string }>;
};

type RawSponsor = {
  id: string;
  full_name: string;
};

type RawReportingPeriod = {
  report_year: number;
  report_month: number;
};

type RawOrphanMonthlySnapshot = {
  report_year: number;
  report_month: number;
  orphan_id: string;
  case_number: string;
  full_name_snapshot: string | null;
  monthly_need_snapshot: number | string | null;
  status_snapshot: RawOrphan["status"];
  orphan_created_at: string;
};

type RawSponsorshipMonthlySnapshot = {
  report_year: number;
  report_month: number;
  sponsorship_id: string;
  sponsor_id: string;
  orphan_id: string;
  sponsor_name_snapshot: string;
  orphan_name_snapshot: string | null;
  case_number_snapshot: string;
  monthly_amount_snapshot: number | string;
  sponsorship_start_date: string;
  sponsorship_end_date: string | null;
};

type PageResult<T> = {
  data: T[] | null;
  error: { message: string } | null;
};

function one<T>(value: Relation<T>): T | null {
  return Array.isArray(value) ? value[0] ?? null : value;
}

function many<T>(value: Relation<T>): T[] {
  if (!value) return [];
  return Array.isArray(value) ? value : [value];
}

function amount(value: number | string | null): number {
  if (value === null) return 0;
  const parsed = Number(value);
  return Number.isFinite(parsed) ? Math.max(0, parsed) : 0;
}

function validUuid(value: string | undefined): string | null {
  return value && UUID_PATTERN.test(value) ? value : null;
}

function displayOrphan(orphan: RawOrphanRelation | null, caseNumber: string): string {
  return orphan?.full_name?.trim() || `حالة ${orphan?.case_number || caseNumber}`;
}

async function loadAllRows<T>(
  label: string,
  loadPage: (from: number, to: number) => PromiseLike<PageResult<T>>
): Promise<T[]> {
  const rows: T[] = [];

  for (let from = 0; ; from += DATABASE_PAGE_SIZE) {
    const result = await loadPage(from, from + DATABASE_PAGE_SIZE - 1);
    if (result.error) throw new Error(`تعذر تحميل ${label}: ${result.error.message}`);
    const page = result.data ?? [];
    rows.push(...page);
    if (page.length < DATABASE_PAGE_SIZE) break;
  }

  return rows;
}

function buildOrphanQuality(row: RawOrphan): ReportOrphan {
  const fields = [
    { label: "الاسم الكامل", complete: Boolean(row.full_name?.trim()) },
    { label: "تاريخ الميلاد", complete: Boolean(row.date_of_birth) },
    { label: "النوع", complete: Boolean(row.gender) },
    { label: "اسم ولي الأمر", complete: Boolean(row.guardian_name?.trim()) },
    { label: "هاتف ولي الأمر", complete: Boolean(row.guardian_phone?.trim()) },
    { label: "صلة ولي الأمر", complete: Boolean(row.guardian_relationship?.trim()) },
    { label: "العنوان", complete: Boolean(row.address?.trim()) },
    { label: "اسم البنك", complete: Boolean(row.bank_name?.trim()) },
    { label: "اسم صاحب الحساب", complete: Boolean(row.bank_account_name?.trim()) },
    { label: "رقم الحساب البنكي", complete: Boolean(row.bank_account_number?.trim()) },
    { label: "الاحتياج الشهري", complete: row.monthly_need !== null }
  ];
  const uploadedTypes = new Set(many(row.orphan_documents).map((item) => item.document_type));
  const documentChecks = Object.entries(orphanDocumentLabels).map(([type, label]) => ({
    label,
    complete: uploadedTypes.has(type)
  }));
  const checks = [...fields, ...documentChecks];
  const missingItems = checks.filter((item) => !item.complete).map((item) => item.label);
  const missingDocumentItems = documentChecks.filter((item) => !item.complete).map((item) => item.label);

  return {
    id: row.id,
    name: row.full_name?.trim() || `حالة ${row.case_number}`,
    caseNumber: row.case_number,
    status: row.status,
    monthlyNeed: row.monthly_need === null ? null : amount(row.monthly_need),
    coveredAmount: amount(row.covered_amount),
    missingItems,
    missingDocumentItems,
    completionPercentage: Math.round(((checks.length - missingItems.length) / checks.length) * 100)
  };
}

export default async function ReportsPage({ searchParams }: { searchParams: Promise<SearchParams> }) {
  const params = await searchParams;
  const current = currentPeriod();
  const parsedYear = Number(params.year);
  const year = Number.isInteger(parsedYear) && parsedYear >= 2020 && parsedYear <= 2200 ? parsedYear : current.year;
  const parsedMonth = Number(params.month);
  const month = Number.isInteger(parsedMonth) && parsedMonth >= 1 && parsedMonth <= 12 ? parsedMonth : null;
  const status = REPORT_STATUSES.has(params.status as ReportStatusFilter)
    ? (params.status as ReportStatusFilter)
    : "all";
  const initialFilters: ReportFilters = {
    month,
    sponsorId: validUuid(params.sponsor),
    orphanId: validUuid(params.orphan),
    status
  };
  const supabase = createAdminSupabaseClient();
  const snapshotResult = await supabase.rpc("ensure_reporting_year", {
    target_year: year
  });
  if (snapshotResult.error) {
    throw new Error(`تعذر تجهيز السجل التاريخي: ${snapshotResult.error.message}`);
  }

  const [
    paymentRows,
    transferRows,
    sponsorshipRows,
    orphanRows,
    sponsorRows,
    reportingPeriods,
    orphanSnapshotRows,
    sponsorshipSnapshotRows
  ] = await Promise.all([
    loadAllRows<RawPayment>(
      "دفعات المتبرعين",
      (from, to) =>
        supabase
          .from("sponsor_monthly_payments")
          .select(
            "id,sponsorship_id,sponsor_id,orphan_id,payment_year,payment_month,expected_amount_snapshot,received_amount,payment_status,payment_date,profiles!sponsor_monthly_payments_sponsor_id_fkey(full_name,phone),orphans(case_number,full_name)"
          )
          .order("payment_year")
          .order("payment_month")
          .order("id")
          .range(from, to) as unknown as PromiseLike<PageResult<RawPayment>>
    ),
    loadAllRows<RawTransfer>(
      "تحويلات الأيتام",
      (from, to) =>
        supabase
          .from("orphan_transfers")
          .select(
            "id,orphan_id,transfer_year,transfer_month,amount,transfer_date,orphans(case_number,full_name),orphan_transfer_sponsors(sponsor_id,profiles!orphan_transfer_sponsors_sponsor_id_fkey(full_name)),transfer_receipts(id)"
          )
          .order("transfer_year")
          .order("transfer_month")
          .order("id")
          .range(from, to) as unknown as PromiseLike<PageResult<RawTransfer>>
    ),
    loadAllRows<RawSponsorship>(
      "علاقات الكفالة",
      (from, to) =>
        supabase
          .from("sponsorships")
          .select(
            "id,donor_id,orphan_id,monthly_amount,status,start_date,end_date,profiles!sponsorships_donor_id_fkey(full_name),orphans(case_number,full_name)"
          )
          .order("id")
          .range(from, to) as unknown as PromiseLike<PageResult<RawSponsorship>>
    ),
    loadAllRows<RawOrphan>(
      "بيانات الأيتام",
      (from, to) =>
        supabase
          .from("orphans")
          .select(
            "id,case_number,full_name,date_of_birth,gender,guardian_name,guardian_phone,guardian_relationship,address,bank_name,bank_account_name,bank_account_number,monthly_need,covered_amount,status,orphan_documents(document_type)"
          )
          .order("id")
          .range(from, to) as unknown as PromiseLike<PageResult<RawOrphan>>
    ),
    loadAllRows<RawSponsor>(
      "بيانات المتبرعين",
      (from, to) =>
        supabase
          .from("profiles")
          .select("id,full_name")
          .eq("role", "donor")
          .order("full_name")
          .range(from, to) as unknown as PromiseLike<PageResult<RawSponsor>>
    ),
    loadAllRows<RawReportingPeriod>(
      "فترات التقارير التاريخية",
      (from, to) =>
        supabase
          .from("reporting_monthly_periods")
          .select("report_year,report_month")
          .order("report_year")
          .order("report_month")
          .range(from, to) as unknown as PromiseLike<PageResult<RawReportingPeriod>>
    ),
    loadAllRows<RawOrphanMonthlySnapshot>(
      "لقطات الأيتام الشهرية",
      (from, to) =>
        supabase
          .from("reporting_orphan_monthly_snapshots")
          .select(
            "report_year,report_month,orphan_id,case_number,full_name_snapshot,monthly_need_snapshot,status_snapshot,orphan_created_at"
          )
          .order("report_year")
          .order("report_month")
          .order("orphan_id")
          .range(from, to) as unknown as PromiseLike<PageResult<RawOrphanMonthlySnapshot>>
    ),
    loadAllRows<RawSponsorshipMonthlySnapshot>(
      "لقطات الكفالات الشهرية",
      (from, to) =>
        supabase
          .from("reporting_sponsorship_monthly_snapshots")
          .select(
            "report_year,report_month,sponsorship_id,sponsor_id,orphan_id,sponsor_name_snapshot,orphan_name_snapshot,case_number_snapshot,monthly_amount_snapshot,sponsorship_start_date,sponsorship_end_date"
          )
          .order("report_year")
          .order("report_month")
          .order("sponsorship_id")
          .range(from, to) as unknown as PromiseLike<PageResult<RawSponsorshipMonthlySnapshot>>
    )
  ]);

  const dataset: ReportDataset = {
    year,
    defaultComparisonMonth:
      year < current.year ? 12 : year === current.year ? current.month : 1,
    sponsors: sponsorRows.map((item) => ({ id: item.id, name: item.full_name })),
    payments: paymentRows.map((item) => {
      const sponsor = one(item.profiles);
      const orphan = one(item.orphans);
      return {
        id: item.id,
        sponsorshipId: item.sponsorship_id,
        sponsorId: item.sponsor_id,
        sponsorName: sponsor?.full_name || "متبرع غير معروف",
        sponsorPhone: sponsor?.phone ?? null,
        orphanId: item.orphan_id,
        orphanName: displayOrphan(orphan, item.orphan_id),
        caseNumber: orphan?.case_number || "غير متوفر",
        year: item.payment_year,
        month: item.payment_month,
        expectedAmount: amount(item.expected_amount_snapshot),
        receivedAmount: amount(item.received_amount),
        paymentStatus: item.payment_status === "paid" ? ("paid" as const) : ("unpaid" as const),
        paymentDate: item.payment_date
      };
    }),
    transfers: transferRows.map((item) => {
      const orphan = one(item.orphans);
      const sponsorLinks = many(item.orphan_transfer_sponsors);
      return {
        id: item.id,
        orphanId: item.orphan_id,
        orphanName: displayOrphan(orphan, item.orphan_id),
        caseNumber: orphan?.case_number || "غير متوفر",
        sponsorIds: sponsorLinks.map((link) => link.sponsor_id),
        sponsorNames: sponsorLinks.map((link) => one(link.profiles)?.full_name || "متبرع غير معروف"),
        year: item.transfer_year,
        month: item.transfer_month,
        amount: amount(item.amount),
        transferDate: item.transfer_date,
        hasReceipt: many(item.transfer_receipts).length > 0
      };
    }),
    sponsorships: sponsorshipRows.map((item) => {
      const sponsor = one(item.profiles);
      const orphan = one(item.orphans);
      return {
        id: item.id,
        sponsorId: item.donor_id,
        sponsorName: sponsor?.full_name || "متبرع غير معروف",
        orphanId: item.orphan_id,
        orphanName: displayOrphan(orphan, item.orphan_id),
        caseNumber: orphan?.case_number || "غير متوفر",
        monthlyAmount: amount(item.monthly_amount),
        status: item.status,
        startDate: item.start_date,
        endDate: item.end_date
      };
    }),
    orphans: orphanRows.map(buildOrphanQuality),
    history: {
      periods: reportingPeriods.map((item) => ({
        year: item.report_year,
        month: item.report_month
      })),
      orphanSnapshots: orphanSnapshotRows.map((item) => ({
        year: item.report_year,
        month: item.report_month,
        orphanId: item.orphan_id,
        name: item.full_name_snapshot?.trim() || `حالة ${item.case_number}`,
        caseNumber: item.case_number,
        monthlyNeed:
          item.monthly_need_snapshot === null
            ? null
            : amount(item.monthly_need_snapshot),
        status: item.status_snapshot,
        createdAt: item.orphan_created_at
      })),
      sponsorshipSnapshots: sponsorshipSnapshotRows.map((item) => ({
        year: item.report_year,
        month: item.report_month,
        sponsorshipId: item.sponsorship_id,
        sponsorId: item.sponsor_id,
        sponsorName: item.sponsor_name_snapshot,
        orphanId: item.orphan_id,
        orphanName:
          item.orphan_name_snapshot?.trim() || `حالة ${item.case_number_snapshot}`,
        caseNumber: item.case_number_snapshot,
        monthlyAmount: amount(item.monthly_amount_snapshot),
        startDate: item.sponsorship_start_date,
        endDate: item.sponsorship_end_date
      }))
    }
  };
  const latestYear = Math.max(current.year + 1, year);
  const yearOptions = Array.from({ length: latestYear - 2019 }, (_, index) => latestYear - index);

  return (
    <>
      <PageHeader
        title="التقارير والتحليلات"
        description="لوحة مالية وتشغيلية تفاعلية مبنية مباشرة على دفعات المتبرعين وتحويلات الأيتام وسجلات الكفالات."
      />
      <AnalyticsDashboard
        dataset={dataset}
        initialFilters={initialFilters}
        yearOptions={yearOptions}
      />
    </>
  );
}
