import bcrypt from "bcryptjs";
import { asc, eq, and, inArray, ne, sql, sum } from "drizzle-orm";
import { db, pool } from "./index.js";
import {
  organizationsTable,
  organizationMembersTable,
  organizationIntakesTable,
  usersTable,
  membershipPlansTable,
  membershipSubscriptionsTable,
  organizationSubscriptionsTable,
  invoicesTable,
  paymentsTable,
  academyCategoriesTable,
  academyCoursesTable,
  courseInterestsTable,
  savingsCirclesTable,
  circleMembershipsTable,
  savingsContributionsTable,
  circlePayoutsTable,
  wwnCreditScoresTable,
  creditScoreFactorsTable,
  helpingHandApplicationsTable,
  helpingHandRepaymentsTable,
  investmentOpportunitiesTable,
  investmentInterestsTable,
  investmentTransactionsTable,
  lendingTemplatesTable,
  lendingOpportunitiesTable,
  lendingCommitmentsTable,
  walletsTable,
  walletTransactionsTable,
  loanApplicationsTable,
  loanRepaymentsTable,
  lendingOpportunityReturnEventsTable,
  lendingOpportunityReturnAllocationsTable,
  resourceCategoriesTable,
  resourcesTable,
  notificationsTable,
  announcementsTable,
  communityEventsTable,
  discussionsTable,
  memberSpotlightsTable,
} from "./schema/index.js";

// ─── Helpers ──────────────────────────────────────────────────

const d = (offsetDays: number) => {
  const dt = new Date();
  dt.setDate(dt.getDate() + offsetDays);
  return dt;
};

const dateAt = (year: number, month: number, day: number) =>
  new Date(year, month - 1, day);

const parseMoney = (value: string | number | null | undefined) => {
  const amount = Number(value ?? 0);
  return Number.isFinite(amount) ? amount : 0;
};

const formatMoney = (value: string | number | null | undefined) =>
  parseMoney(value).toFixed(2);

const ORG_STARTER_ENTITLEMENTS = {
  tenant_access: true,
  member_management: true,
  seat_limit: 5,
  asca_access: true,
  programs_access: true,
  resources_access: true,
  workshops_access: false,
  white_label_resources_access: false,
  org_susu_operate: false,
  org_susu_education: true,
  investment_club_access: false,
  p2p_capital_access: false,
  quote_managed_only: false,
};

const ORG_PROFESSIONAL_ENTITLEMENTS = {
  tenant_access: true,
  member_management: true,
  seat_limit: 25,
  asca_access: true,
  programs_access: true,
  resources_access: true,
  workshops_access: true,
  white_label_resources_access: true,
  org_susu_operate: false,
  org_susu_education: true,
  investment_club_access: false,
  p2p_capital_access: true,
  quote_managed_only: false,
};

const ORG_ELITE_ENTITLEMENTS = {
  tenant_access: true,
  member_management: true,
  seat_limit: null,
  asca_access: true,
  programs_access: true,
  resources_access: true,
  workshops_access: true,
  white_label_resources_access: true,
  org_susu_operate: true,
  org_susu_education: true,
  investment_club_access: true,
  p2p_capital_access: true,
  quote_managed_only: false,
};

const INDIVIDUAL_DEFAULT_ENTITLEMENTS = {
  member_susu_access: true,
  susunomics_access: true,
  community_lending_access: true,
  p2p_lending_access: false,
};

const getCirclePayoutDate = (
  startDate: Date | null | undefined,
  frequency: "WEEKLY" | "BIWEEKLY" | "MONTHLY",
  position: number,
) => {
  const baseDate = startDate ?? new Date();
  switch (frequency) {
    case "WEEKLY":
      return new Date(
        baseDate.getFullYear(),
        baseDate.getMonth(),
        baseDate.getDate() + (position - 2) * 7,
      );
    case "BIWEEKLY":
      return new Date(
        baseDate.getFullYear(),
        baseDate.getMonth(),
        baseDate.getDate() + (position - 2) * 14,
      );
    case "MONTHLY":
    default:
      return dateAt(
        baseDate.getFullYear(),
        baseDate.getMonth() + position - 1,
        baseDate.getDate(),
      );
  }
};

const getRepaymentInstallmentCount = (
  termMonths: number,
  repaymentFrequency: "WEEKLY" | "BIWEEKLY" | "MONTHLY",
) => {
  switch (repaymentFrequency) {
    case "WEEKLY":
      return Math.max(1, Math.round((termMonths * 52) / 12));
    case "BIWEEKLY":
      return Math.max(1, Math.round((termMonths * 26) / 12));
    case "MONTHLY":
    default:
      return Math.max(1, termMonths);
  }
};

const buildSeedLoanPricing = (params: {
  principalAmount: string | number;
  borrowerAppreciationAmount: string | number;
  termMonths: number;
  repaymentFrequency: "WEEKLY" | "BIWEEKLY" | "MONTHLY";
  processingFeePct?: string | number | null;
}) => {
  const principalAmount = parseMoney(params.principalAmount);
  const borrowerAppreciationAmount = parseMoney(
    params.borrowerAppreciationAmount,
  );
  const processingFeePct = parseMoney(params.processingFeePct);
  const interestAmount = borrowerAppreciationAmount;
  const processingFeeAmount = principalAmount * (processingFeePct / 100);
  const totalRepayableAmount =
    principalAmount + interestAmount + processingFeeAmount;
  const installmentCount = getRepaymentInstallmentCount(
    params.termMonths,
    params.repaymentFrequency,
  );

  return {
    term: `${params.termMonths} month${params.termMonths === 1 ? "" : "s"}`,
    repaymentSchedule:
      params.repaymentFrequency === "WEEKLY"
        ? "Weekly"
        : params.repaymentFrequency === "BIWEEKLY"
          ? "Biweekly"
          : "Monthly",
    totalRepayableAmount: formatMoney(totalRepayableAmount),
    estimatedInstallmentAmount: formatMoney(
      totalRepayableAmount / installmentCount,
    ),
  };
};

async function first<T>(query: Promise<T[]>): Promise<T | undefined> {
  return (await query)[0];
}

// ─── Main ─────────────────────────────────────────────────────

async function main() {
  console.log("🌱  Starting WWN seed...\n");

  // ── 1. Membership Plans ──────────────────────────────────────
  console.log("📋  Seeding membership plans...");

  const plans = [
    // INDIVIDUAL
    {
      membershipType: "INDIVIDUAL" as const,
      tier: "PROFESSIONAL" as const,
      name: "Professional",
      description: "Full platform access including SUSU savings and lending.",
      priceMonthly: "24.99",
      priceAnnual: "249.00",
      sortOrder: 1,
      features: [
        "Member portal access",
        "SUSU Savings Circle participation",
        "Matching Fund eligibility",
        "SUSUNOMICS investment access",
        "Priority support",
        "Exclusive member events",
      ],
      entitlements: INDIVIDUAL_DEFAULT_ENTITLEMENTS,
    },
    {
      membershipType: "INDIVIDUAL" as const,
      tier: "ENTREPRENEUR" as const,
      name: "Entrepreneur",
      description:
        "Growth-focused membership for founders and business owners building with WWN.",
      priceMonthly: "99.99",
      priceAnnual: "999.00",
      sortOrder: 2,
      features: [
        "Everything in Professional",
        "Founder growth circles and business clinics",
        "Priority access to MSME lending opportunities",
        "Business visibility and marketplace spotlight options",
        "Quarterly strategy advisory session",
        "Expanded partner and funding introductions",
      ],
      entitlements: INDIVIDUAL_DEFAULT_ENTITLEMENTS,
    },
    {
      membershipType: "INDIVIDUAL" as const,
      tier: "YOUTH" as const,
      name: "Youth",
      description: "Essential access to WWN resources and community.",
      priceMonthly: "9.99",
      priceAnnual: "99.00",
      sortOrder: 3,
      features: [
        "Member portal access",
        "WWN Academy directory (browse only)",
        "Community discussions",
        "Monthly newsletter",
        "Basic resource library",
      ],
      entitlements: INDIVIDUAL_DEFAULT_ENTITLEMENTS,
    },
    {
      membershipType: "INDIVIDUAL" as const,
      tier: "ELITE" as const,
      name: "Elite",
      description:
        "Premium membership with mentorship and first-access benefits.",
      priceMonthly: "49.99",
      priceAnnual: "499.00",
      sortOrder: 4,
      features: [
        "Everything in Professional",
        "1:1 financial coaching session (quarterly)",
        "Early access to new investment opportunities",
        "WWN Ambassador badge",
        "Reduced platform fees",
        "Dedicated member success manager",
      ],
      entitlements: INDIVIDUAL_DEFAULT_ENTITLEMENTS,
    },
    // ORGANIZATION
    {
      membershipType: "ORGANIZATION" as const,
      tier: "COMMUNITY" as const,
      name: "Community",
      description: "Ideal for small non-profits and community groups.",
      priceMonthly: "49.99",
      priceAnnual: "499.00",
      sortOrder: 1,
      features: [
        "Up to 5 team members",
        "Organization profile page",
        "WWN Academy access for team",
        "Community event participation",
        "Quarterly impact report",
      ],
      entitlements: ORG_STARTER_ENTITLEMENTS,
    },
    {
      membershipType: "ORGANIZATION" as const,
      tier: "EDUCATION" as const,
      name: "Education",
      description:
        "For schools and educational institutions serving learners and educators.",
      priceMonthly: "149.99",
      priceAnnual: "1499.00",
      sortOrder: 2,
      features: [
        "Up to 25 team members",
        "Everything in Community",
        "Program co-design support",
        "Financial literacy delivery support",
        "Dedicated account manager",
        "Annual strategy session",
      ],
      entitlements: ORG_PROFESSIONAL_ENTITLEMENTS,
    },
    {
      membershipType: "ORGANIZATION" as const,
      tier: "GRASSROOT" as const,
      name: "Grassroot",
      description:
        "For grassroots collectives and ROSCAs building community-led capital systems.",
      priceMonthly: "499.99",
      priceAnnual: "4999.00",
      sortOrder: 3,
      features: [
        "Unlimited team members",
        "Everything in Partner",
        "Co-branded program delivery",
        "Speaking opportunities at WWN events",
        "Joint grant applications",
        "Custom reporting & analytics",
      ],
      entitlements: ORG_ELITE_ENTITLEMENTS,
    },
    // INVESTOR
    {
      membershipType: "INVESTOR" as const,
      tier: "WORKER" as const,
      name: "Worker Owner",
      description: "Support WWN through cooperative ownership participation.",
      priceMonthly: "29.99",
      priceAnnual: "299.00",
      sortOrder: 1,
      features: [
        "Worker Owner designation",
        "Investor portal access",
        "Voting rights at annual meetings",
        "SUSUNOMICS investment access",
        "Quarterly returns reporting",
      ],
      entitlements: {},
    },
    {
      membershipType: "INVESTOR" as const,
      tier: "IMPACT" as const,
      name: "Impact Investor",
      description: "Lead capital partner driving WWN's mission at scale.",
      priceMonthly: "299.99",
      priceAnnual: "2999.00",
      sortOrder: 2,
      features: [
        "Impact Investor designation",
        "Everything in Worker Owner",
        "Seat on WWN Investment Advisory Committee",
        "Priority deal flow on all new opportunities",
        "Custom impact measurement report",
        "Annual gala invitation",
        "Branded lending pool naming rights",
      ],
      entitlements: {},
    },
    {
      membershipType: "INVESTOR" as const,
      tier: "SPONSOR" as const,
      name: "Sponsor",
      description: "Amplify community impact through strategic sponsorship.",
      priceMonthly: "99.99",
      priceAnnual: "999.00",
      sortOrder: 3,
      features: [
        "Sponsor designation",
        "Everything in Worker Owner",
        "Named sponsorship of a program or event",
        "Bi-annual investor briefing",
        "Early access to new opportunities",
        "WWN website listing",
      ],
      entitlements: {},
    },
  ];

  for (const plan of plans) {
    const existing = await first(
      db
        .select()
        .from(membershipPlansTable)
        .where(
          and(
            eq(membershipPlansTable.membershipType, plan.membershipType),
            eq(membershipPlansTable.name, plan.name),
            sql`${membershipPlansTable.organizationType} is null`,
          ),
        ),
    );
    if (!existing) {
      await db.insert(membershipPlansTable).values({
        ...plan,
        organizationType: null,
        features: plan.features,
        entitlements: plan.entitlements,
      });
    } else {
      await db
        .update(membershipPlansTable)
        .set({
          name: plan.name,
          description: plan.description,
          priceMonthly: plan.priceMonthly,
          priceAnnual: plan.priceAnnual,
          currency: "CAD",
          features: plan.features,
          entitlements: plan.entitlements,
          isActive: true,
          sortOrder: plan.sortOrder,
          organizationType: null,
          updatedAt: new Date(),
        })
        .where(eq(membershipPlansTable.id, existing.id));
    }
  }
  console.log(`   ✅  ${plans.length} plans seeded`);

  // ── 2. Academy Categories & Courses ─────────────────────────
  console.log("🎓  Seeding academy categories and courses...");

  const categories = [
    { name: "Financial Literacy", slug: "financial-literacy", sortOrder: 1 },
    { name: "Leadership", slug: "leadership", sortOrder: 2 },
    { name: "Entrepreneurship", slug: "entrepreneurship", sortOrder: 3 },
    { name: "Business", slug: "business", sortOrder: 4 },
    { name: "Savings & Credit", slug: "savings-credit", sortOrder: 5 },
    { name: "Investment", slug: "investment", sortOrder: 6 },
    { name: "Technology", slug: "technology", sortOrder: 7 },
    { name: "Health & Wellness", slug: "health-wellness", sortOrder: 8 },
  ];

  const catMap: Record<string, number> = {};
  for (const cat of categories) {
    let row = await first(
      db
        .select()
        .from(academyCategoriesTable)
        .where(eq(academyCategoriesTable.slug, cat.slug)),
    );
    if (!row) {
      const [created] = await db
        .insert(academyCategoriesTable)
        .values(cat)
        .returning();
      row = created;
    }
    catMap[cat.slug] = row!.id;
  }

  const courses = [
    {
      title: "Financial Foundations for Women",
      slug: "financial-foundations-for-women",
      description:
        "Master the core principles of personal finance: budgeting, saving, and building an emergency fund on your own terms.",
      categorySlug: "financial-literacy",
      instructor: "Dr. Amara Johnson",
      duration: "4 weeks",
      level: "Beginner",
      rating: "4.8",
      enrolledCount: 312,
      externalUrl: "https://academy.wwncanada.ca/courses/financial-foundations",
      tags: ["Budgeting", "Savings", "Credit"],
    },
    {
      title: "SUSU & Cooperative Economics",
      slug: "susu-cooperative-economics",
      description:
        "Understand the history and mechanics of SUSU savings circles and how cooperative economics empower communities.",
      categorySlug: "savings-credit",
      instructor: "Prof. Nana Asante",
      duration: "3 weeks",
      level: "Beginner",
      rating: "4.9",
      enrolledCount: 481,
      externalUrl: "https://academy.wwncanada.ca/courses/susu-cooperative",
      tags: ["SUSU", "Cooperatives", "Community"],
    },
    {
      title: "Women in Leadership",
      slug: "women-in-leadership",
      description:
        "Develop executive presence, negotiation skills, and strategic thinking to advance your leadership journey.",
      categorySlug: "leadership",
      instructor: "Kezia Williams, MBA",
      duration: "6 weeks",
      level: "Intermediate",
      rating: "4.7",
      enrolledCount: 228,
      externalUrl: "https://academy.wwncanada.ca/courses/women-in-leadership",
      tags: ["Leadership", "Career", "Executive"],
    },
    {
      title: "Starting Your Business — Zero to Launch",
      slug: "starting-your-business-zero-to-launch",
      description:
        "A practical step-by-step guide to validating your business idea, writing your first business plan, and finding your first customer.",
      categorySlug: "entrepreneurship",
      instructor: "Temi Okafor",
      duration: "8 weeks",
      level: "Beginner",
      rating: "4.6",
      enrolledCount: 394,
      externalUrl: "https://academy.wwncanada.ca/courses/zero-to-launch",
      tags: ["Startup", "Business Plan", "Funding"],
    },
    {
      title: "Introduction to Impact Investing",
      slug: "introduction-to-impact-investing",
      description:
        "Learn how to invest with purpose: real estate, community bonds, and ESG portfolios that generate social good alongside returns.",
      categorySlug: "investment",
      instructor: "Fatima Al-Hassan, CFA",
      duration: "5 weeks",
      level: "Intermediate",
      rating: "4.7",
      enrolledCount: 157,
      externalUrl: "https://academy.wwncanada.ca/courses/impact-investing",
      tags: ["Investing", "ESG", "Real Estate"],
    },
    {
      title: "Credit Mastery: Build, Repair & Leverage",
      slug: "credit-mastery-build-repair-leverage",
      description:
        "Understand credit scoring, dispute errors, and use credit strategically to achieve your financial goals.",
      categorySlug: "savings-credit",
      instructor: "Dr. Monique Clarke",
      duration: "4 weeks",
      level: "Beginner",
      rating: "4.8",
      enrolledCount: 567,
      externalUrl: "https://academy.wwncanada.ca/courses/credit-mastery",
      tags: ["Credit", "Debt", "Score"],
    },
    {
      title: "Digital Marketing for Entrepreneurs",
      slug: "digital-marketing-for-entrepreneurs",
      description:
        "Learn SEO, social media strategy, email marketing, and content creation to grow your business online.",
      categorySlug: "business",
      instructor: "Jade Thompson",
      duration: "6 weeks",
      level: "Intermediate",
      rating: "4.5",
      enrolledCount: 289,
      externalUrl: "https://academy.wwncanada.ca/courses/digital-marketing",
      tags: ["Marketing", "Social Media", "SEO"],
    },
    {
      title: "Tech Skills for the Modern Professional",
      slug: "tech-skills-modern-professional",
      description:
        "Master essential tools: spreadsheets, project management platforms, basic data analysis, and AI productivity tools.",
      categorySlug: "technology",
      instructor: "Priya Nair",
      duration: "4 weeks",
      level: "Beginner",
      rating: "4.6",
      enrolledCount: 342,
      externalUrl: "https://academy.wwncanada.ca/courses/tech-skills",
      tags: ["Tech", "AI", "Productivity"],
    },
  ];

  for (const { categorySlug, ...data } of courses) {
    const existing = await first(
      db
        .select()
        .from(academyCoursesTable)
        .where(eq(academyCoursesTable.slug, data.slug)),
    );
    if (!existing) {
      await db
        .insert(academyCoursesTable)
        .values({ ...data, categoryId: catMap[categorySlug] });
    }
  }
  console.log(
    `   ✅  ${categories.length} categories, ${courses.length} courses seeded`,
  );

  // ── 3. Savings Circles ───────────────────────────────────────
  console.log("🐖  Seeding savings circles...");

  let circle7 = await first(
    db
      .select()
      .from(savingsCirclesTable)
      .where(eq(savingsCirclesTable.name, "Circle #7 — Sunrise Group")),
  );
  if (!circle7) {
    [circle7] = await db
      .insert(savingsCirclesTable)
      .values({
        name: "Circle #7 — Sunrise Group",
        description:
          "A monthly savings circle for professional women in the GTA.",
        contributionAmount: "200.00",
        frequency: "MONTHLY",
        maxMembers: 12,
        status: "ACTIVE",
        startDate: dateAt(2026, 1, 1),
        endDate: dateAt(2026, 12, 31),
      })
      .returning();
  }

  let circle12 = await first(
    db
      .select()
      .from(savingsCirclesTable)
      .where(eq(savingsCirclesTable.name, "Circle #12 — Prosperity Group")),
  );
  if (!circle12) {
    [circle12] = await db
      .insert(savingsCirclesTable)
      .values({
        name: "Circle #12 — Prosperity Group",
        description: "Open to new members — starts August 2026.",
        contributionAmount: "150.00",
        frequency: "MONTHLY",
        maxMembers: 10,
        status: "FORMING",
        startDate: dateAt(2026, 8, 1),
        endDate: dateAt(2027, 5, 31),
      })
      .returning();
  }

  let circle15 = await first(
    db
      .select()
      .from(savingsCirclesTable)
      .where(eq(savingsCirclesTable.name, "Circle #15 — Growth Circle")),
  );
  if (!circle15) {
    [circle15] = await db
      .insert(savingsCirclesTable)
      .values({
        name: "Circle #15 — Growth Circle",
        description: "Bi-weekly savings for entrepreneurs.",
        contributionAmount: "300.00",
        frequency: "BIWEEKLY",
        maxMembers: 8,
        status: "FORMING",
        startDate: dateAt(2026, 7, 15),
        endDate: dateAt(2027, 1, 31),
      })
      .returning();
  }
  console.log("   ✅  3 circles seeded");

  // ── 4. Investment Opportunities ──────────────────────────────
  console.log("📈  Seeding investment opportunities...");

  const opportunities = [
    {
      title: "Community Housing Development — Scarborough",
      slug: "community-housing-scarborough",
      description:
        "Co-investment in a mixed-use affordable housing development serving BIPOC families in Scarborough, ON.",
      category: "REAL_ESTATE" as const,
      expectedReturnPct: 8,
      minInvestment: "500.00",
      targetAmount: "500000.00",
      raisedAmount: "312000.00",
      deadline: dateAt(2026, 8, 31),
      riskLevel: "LOW" as const,
      status: "OPEN" as const,
      featured: true,
      academyLink: "https://academy.wwncanada.ca/courses/impact-investing",
    },
    {
      title: "Black Women Entrepreneurs Micro-Loan Pool",
      slug: "black-women-entrepreneurs-microloan-pool",
      description:
        "Fund a pool of micro-loans for Black women-owned small businesses in the GTA.",
      category: "SMALL_BUSINESS" as const,
      expectedReturnPct: 7,
      minInvestment: "250.00",
      targetAmount: "150000.00",
      raisedAmount: "98000.00",
      deadline: dateAt(2026, 7, 31),
      riskLevel: "MEDIUM" as const,
      status: "OPEN" as const,
      featured: false,
      academyLink: "https://academy.wwncanada.ca/courses/zero-to-launch",
    },
    {
      title: "Cooperative Farm — Ontario Food Hub",
      slug: "cooperative-farm-ontario-food-hub",
      description:
        "A cooperative agricultural project producing organic vegetables for CSA boxes and local restaurants.",
      category: "AGRICULTURE" as const,
      expectedReturnPct: 6,
      minInvestment: "300.00",
      targetAmount: "200000.00",
      raisedAmount: "45000.00",
      deadline: dateAt(2026, 9, 15),
      riskLevel: "MEDIUM" as const,
      status: "OPEN" as const,
      featured: false,
    },
    {
      title: "WWN Community Development Bond",
      slug: "wwn-community-development-bond",
      description:
        "A fixed-return community bond issued by WWN to fund platform infrastructure and program expansion.",
      category: "BONDS" as const,
      expectedReturnPct: 5,
      minInvestment: "1000.00",
      targetAmount: "750000.00",
      raisedAmount: "540000.00",
      deadline: dateAt(2026, 12, 31),
      riskLevel: "LOW" as const,
      status: "OPEN" as const,
      featured: true,
      academyLink: "https://academy.wwncanada.ca/courses/impact-investing",
    },
  ];

  const oppMap: Record<string, number> = {};
  for (const opp of opportunities) {
    let row = await first(
      db
        .select()
        .from(investmentOpportunitiesTable)
        .where(eq(investmentOpportunitiesTable.slug, opp.slug)),
    );
    if (!row) {
      [row] = await db
        .insert(investmentOpportunitiesTable)
        .values(opp)
        .returning();
    } else if (!row.expectedReturnPct) {
      [row] = await db
        .update(investmentOpportunitiesTable)
        .set({
          expectedReturnPct: opp.expectedReturnPct,
          updatedAt: new Date(),
        })
        .where(eq(investmentOpportunitiesTable.id, row.id))
        .returning();
    }
    oppMap[opp.slug] = row!.id;
  }
  console.log(`   ✅  ${opportunities.length} opportunities seeded`);

  // ── 5. Lending Templates + Sample Opportunities ───────────────
  console.log("🏦  Seeding lending templates...");

  let microLoanTemplate = await first(
    db
      .select()
      .from(lendingTemplatesTable)
      .where(
        eq(
          lendingTemplatesTable.title,
          "Small Business Micro-Loan Template — Q3 2026",
        ),
      ),
  );
  if (!microLoanTemplate) {
    [microLoanTemplate] = await db
      .insert(lendingTemplatesTable)
      .values({
        title: "Small Business Micro-Loan Template — Q3 2026",
        description:
          "Borrower-facing terms for vetted WWN members launching or expanding small businesses.",
        status: "OPEN",
        minCommitment: "500.00",
        minRequestAmount: "1000.00",
        maxRequestAmount: "5000.00",
        borrowerTermMonths: 12,
        repaymentFrequency: "MONTHLY",
        processingFeePct: "1.50",
        platformAppreciationSharePct: "0.00",
        minAppreciationType: "TERM_TIERED",
        minAppreciationValue: "0.00",
        minAppreciationTiers: null,
        expectedReturnPct: "8.00",
        termMonths: 12,
        returnModel: "FIXED_SIMPLE",
        expectedReturn: "8.00% p.a.",
        term: "12 months",
        minBorrowerPlanTier: "STARTER",
        borrowerType: "Individual Members — Business",
        economicsUpdatedAt: null,
        economicsUpdatedByAdminId: null,
        economicsUpdateReason: null,
      })
      .returning();
  }

  let reliefTemplate = await first(
    db
      .select()
      .from(lendingTemplatesTable)
      .where(eq(lendingTemplatesTable.title, "Emergency Relief Template")),
  );
  if (!reliefTemplate) {
    [reliefTemplate] = await db
      .insert(lendingTemplatesTable)
      .values({
        title: "Emergency Relief Template",
        description:
          "Borrower-facing terms for short-term emergency loans for members facing unexpected hardship.",
        status: "OPEN",
        minCommitment: "200.00",
        minRequestAmount: "1000.00",
        maxRequestAmount: "5000.00",
        borrowerTermMonths: 6,
        repaymentFrequency: "MONTHLY",
        processingFeePct: null,
        platformAppreciationSharePct: "0.00",
        minAppreciationType: "NONE",
        minAppreciationValue: "0.00",
        minAppreciationTiers: null,
        expectedReturnPct: "0.00",
        termMonths: 6,
        returnModel: "FIXED_SIMPLE",
        expectedReturn: "0.00% p.a.",
        term: "6 months",
        minBorrowerPlanTier: "STARTER",
        borrowerType: "Individual Members — Emergency",
        economicsUpdatedAt: null,
        economicsUpdatedByAdminId: null,
        economicsUpdateReason: null,
      })
      .returning();
  }
  console.log("   ✅  2 lending templates seeded");

  console.log("🏦  Seeding sample lending opportunities...");
  let microLoanOpportunity = await first(
    db
      .select()
      .from(lendingOpportunitiesTable)
      .where(
        eq(
          lendingOpportunitiesTable.title,
          "Small Business Micro-Loan Opportunity — Q3 2026",
        ),
      ),
  );
  if (!microLoanOpportunity) {
    [microLoanOpportunity] = await db
      .insert(lendingOpportunitiesTable)
      .values({
        templateId: microLoanTemplate!.id,
        applicationId: null,
        title: "Small Business Micro-Loan Opportunity — Q3 2026",
        description:
          "Sample opportunity seeded for investor commitments in local development.",
        status: "OPEN",
        targetAmount: "50000.00",
        committedAmount: "28500.00",
        minCommitment: "500.00",
        borrowerAppreciationAmount: "14.00",
        borrowerTermMonths: 12,
        repaymentFrequency: "MONTHLY",
        processingFeePct: "1.50",
        platformAppreciationSharePct: "0.00",
        minAppreciationType: "TERM_TIERED",
        minAppreciationValue: "0.00",
        minAppreciationTiers: null,
        expectedReturnPct: "8.00",
        termMonths: 12,
        returnModel: "FIXED_SIMPLE",
        expectedReturn: "8.00% p.a.",
        term: "12 months",
        minBorrowerPlanTier: "STARTER",
        borrowerType: "Individual Members — Business",
      })
      .returning();
  }

  let reliefOpportunity = await first(
    db
      .select()
      .from(lendingOpportunitiesTable)
      .where(
        eq(lendingOpportunitiesTable.title, "Emergency Relief Opportunity"),
      ),
  );
  if (!reliefOpportunity) {
    [reliefOpportunity] = await db
      .insert(lendingOpportunitiesTable)
      .values({
        templateId: reliefTemplate!.id,
        applicationId: null,
        title: "Emergency Relief Opportunity",
        description:
          "Sample opportunity seeded for investor commitments in local development.",
        status: "OPEN",
        targetAmount: "20000.00",
        committedAmount: "14200.00",
        minCommitment: "200.00",
        borrowerAppreciationAmount: "0.00",
        borrowerTermMonths: 6,
        repaymentFrequency: "MONTHLY",
        processingFeePct: null,
        platformAppreciationSharePct: "0.00",
        minAppreciationType: "NONE",
        minAppreciationValue: "0.00",
        minAppreciationTiers: null,
        expectedReturnPct: "0.00",
        termMonths: 6,
        returnModel: "FIXED_SIMPLE",
        expectedReturn: "0.00% p.a.",
        term: "6 months",
        minBorrowerPlanTier: "STARTER",
        borrowerType: "Individual Members — Emergency",
      })
      .returning();
  }
  console.log("   ✅  2 lending opportunities seeded");

  // ── 6. Resource Categories & Resources ──────────────────────
  console.log("📚  Seeding resources...");

  const resCats = [
    { name: "Financial Tools", slug: "financial-tools", sortOrder: 1 },
    { name: "Policy & Governance", slug: "policy-governance", sortOrder: 2 },
    { name: "Educational Guides", slug: "educational-guides", sortOrder: 3 },
    { name: "Templates", slug: "templates", sortOrder: 4 },
    { name: "Research & Reports", slug: "research-reports", sortOrder: 5 },
  ];

  const resCatMap: Record<string, number> = {};
  for (const rc of resCats) {
    let row = await first(
      db
        .select()
        .from(resourceCategoriesTable)
        .where(eq(resourceCategoriesTable.slug, rc.slug)),
    );
    if (!row) {
      [row] = await db.insert(resourceCategoriesTable).values(rc).returning();
    }
    resCatMap[rc.slug] = row!.id;
  }

  const resources = [
    {
      title: "Personal Budget Planner 2026",
      description:
        "A comprehensive spreadsheet to track monthly income, expenses, savings goals, and debt repayment.",
      categorySlug: "financial-tools",
      fileUrl: "/resources/budget-planner-2026.xlsx",
      fileType: "SPREADSHEET" as const,
      isPublished: true,
    },
    {
      title: "SUSU Savings Circle Guide",
      description:
        "Everything you need to know about participating in a WWN SUSU Savings Circle.",
      categorySlug: "educational-guides",
      fileUrl: "/resources/susu-circle-guide.pdf",
      fileType: "PDF" as const,
      isPublished: true,
    },
    {
      title: "WWN Membership Policy 2026",
      description:
        "The official WWN membership terms, conditions, code of conduct, and dispute resolution process.",
      categorySlug: "policy-governance",
      fileUrl: "/resources/wwn-membership-policy-2026.pdf",
      fileType: "PDF" as const,
      isPublished: true,
    },
    {
      title: "Business Plan Template",
      description:
        "A structured business plan template designed for women entrepreneurs applying for funding.",
      categorySlug: "templates",
      fileUrl: "/resources/business-plan-template.docx",
      fileType: "DOCUMENT" as const,
      isPublished: true,
    },
    {
      title: "State of BIPOC Women in Finance 2025 — Annual Report",
      description:
        "WWN's annual research report on financial inclusion, wealth gaps, and emerging opportunities for BIPOC women.",
      categorySlug: "research-reports",
      fileUrl: "/resources/bipoc-finance-report-2025.pdf",
      fileType: "PDF" as const,
      isPublished: true,
    },
  ];

  for (const { categorySlug, ...data } of resources) {
    const existing = await first(
      db
        .select()
        .from(resourcesTable)
        .where(eq(resourcesTable.title, data.title)),
    );
    if (!existing) {
      await db.insert(resourcesTable).values({
        ...data,
        categoryId: resCatMap[categorySlug],
        publishedAt: data.isPublished ? new Date() : null,
      });
    }
  }
  console.log(
    `   ✅  ${resCats.length} categories, ${resources.length} resources seeded`,
  );

  // ── 7. Community Data ────────────────────────────────────────
  console.log("🤝  Seeding community data...");

  const announcements = [
    {
      title: "Welcome to the WWN Community Hub!",
      body: "We are thrilled to launch the WWN Community Hub — your space to connect, share, and grow alongside fellow members across Canada.",
      category: "Platform",
      pinned: true,
    },
    {
      title: "SUSU Savings Circle #12 is now open for registration",
      body: "Circle #12 — Prosperity Group — has 2 remaining spots. Monthly contribution: $150. Starting August 1, 2026.",
      category: "Savings",
      pinned: false,
    },
    {
      title: "New investment opportunity: Community Housing Development",
      body: "WWN has launched a new SUSUNOMICS investment opportunity in Scarborough, ON. Expected returns of 7–9% p.a. over 5 years.",
      category: "Investments",
      pinned: false,
    },
  ];

  for (const ann of announcements) {
    const existing = await first(
      db
        .select()
        .from(announcementsTable)
        .where(eq(announcementsTable.title, ann.title)),
    );
    if (!existing) await db.insert(announcementsTable).values(ann);
  }

  const events = [
    {
      title: "Financial Literacy Workshop — Budgeting Basics",
      description:
        "A hands-on workshop walking through the essentials of building and maintaining a personal budget.",
      date: dateAt(2026, 7, 10),
      startTime: "6:00 PM",
      endTime: "8:00 PM EST",
      location: "Zoom (link sent after RSVP)",
      type: "VIRTUAL" as const,
      maxAttendees: 100,
    },
    {
      title: "SUSU Group Meeting — Circles 7 & 12",
      description:
        "Quarterly in-person circle meeting for members of Circles 7 and 12.",
      date: dateAt(2026, 7, 12),
      startTime: "2:00 PM",
      endTime: "4:00 PM EST",
      location: "Community Hub, 245 Finch Ave W, Toronto",
      type: "IN_PERSON" as const,
      maxAttendees: 30,
    },
    {
      title: "WWN Investor Summit 2026",
      description:
        "The annual gathering of WWN investor members. Keynotes, investment reviews, networking, and the launch of new SUSUNOMICS opportunities.",
      date: dateAt(2026, 9, 20),
      startTime: "9:00 AM",
      endTime: "5:00 PM EST",
      location: "Metro Toronto Convention Centre + Virtual",
      type: "HYBRID" as const,
    },
  ];

  for (const ev of events) {
    const existing = await first(
      db
        .select()
        .from(communityEventsTable)
        .where(eq(communityEventsTable.title, ev.title)),
    );
    if (!existing) await db.insert(communityEventsTable).values(ev);
  }
  console.log("   ✅  Community data seeded");

  // ── 8. Demo Users ────────────────────────────────────────────
  console.log("\n👤  Creating demo users...\n");

  const DEMO_PASS = await bcrypt.hash("Demo2026!", 12);
  const ADMIN_PASS = await bcrypt.hash("Admin2026!", 12);

  // ── USER 1: Sarah Thompson — Individual / Professional ────────
  console.log("   Creating Sarah Thompson (Individual)...");

  let sarah = await first(
    db
      .select()
      .from(usersTable)
      .where(eq(usersTable.email, "sarah@demo.wwn.ca")),
  );
  if (!sarah) {
    [sarah] = await db
      .insert(usersTable)
      .values({
        email: "sarah@demo.wwn.ca",
        username: "sarah_demo",
        firstName: "Sarah",
        lastName: "Thompson",
        phone: "+1-416-555-0101",
        passwordHash: DEMO_PASS,
        isEmailVerified: true,
        membershipType: "INDIVIDUAL",
        membershipStatus: "ACTIVE",
        segment: "PROFESSIONAL",
        bio: "Financial educator and community advocate passionate about economic empowerment for BIPOC women.",
        city: "Toronto",
        province: "ON",
        ageRange: "35–44",
        occupation: "Financial Educator",
        howHeard: "Friend/Referral",
        goals:
          "Build my savings, invest collectively, and eventually apply for a business loan through WWN.",
      })
      .returning();
  }

  const professionalPlan = await first(
    db
      .select()
      .from(membershipPlansTable)
      .where(
        and(
          eq(membershipPlansTable.membershipType, "INDIVIDUAL"),
          eq(membershipPlansTable.tier, "PROFESSIONAL"),
        ),
      ),
  );

  let sarahSub = await first(
    db
      .select()
      .from(membershipSubscriptionsTable)
      .where(eq(membershipSubscriptionsTable.userId, sarah!.id)),
  );
  if (!sarahSub && professionalPlan) {
    [sarahSub] = await db
      .insert(membershipSubscriptionsTable)
      .values({
        userId: sarah!.id,
        planId: professionalPlan.id,
        status: "ACTIVE",
        startDate: dateAt(2026, 1, 15),
        endDate: dateAt(2027, 1, 15),
        renewsAt: dateAt(2027, 1, 15),
      })
      .returning();

    const [inv] = await db
      .insert(invoicesTable)
      .values({
        userId: sarah!.id,
        subscriptionId: sarahSub!.id,
        invoiceNumber: "INV-2026-0001",
        amount: "249.00",
        status: "PAID",
        dueDate: dateAt(2026, 1, 15),
        paidAt: dateAt(2026, 1, 15),
        lineItems: [
          {
            description: "Individual Professional Membership (Annual)",
            quantity: 1,
            unitPrice: 249.0,
            total: 249.0,
          },
        ],
      })
      .returning();

    await db.insert(paymentsTable).values({
      userId: sarah!.id,
      amount: "249.00",
      method: "STRIPE",
      status: "COMPLETED",
      reference: "pi_test_sarah_membership",
      description: "Annual Individual Professional Membership",
      invoiceId: inv.id,
      processedAt: dateAt(2026, 1, 15),
    });
  }

  // Circle membership (position 4)
  const sarahMembership = await first(
    db
      .select()
      .from(circleMembershipsTable)
      .where(
        and(
          eq(circleMembershipsTable.circleId, circle7!.id),
          eq(circleMembershipsTable.userId, sarah!.id),
        ),
      ),
  );
  if (!sarahMembership) {
    await db.insert(circleMembershipsTable).values({
      circleId: circle7!.id,
      userId: sarah!.id,
      position: 4,
      joinedAt: dateAt(2026, 1, 5),
    });
  }

  // Contributions (Jan–Jun 2026)
  const contribData = [
    {
      month: 1,
      status: "PAID" as const,
      method: "STRIPE" as const,
      paidAt: dateAt(2026, 1, 1),
    },
    { month: 2, status: "MISSED" as const, method: null, paidAt: null },
    {
      month: 3,
      status: "PAID" as const,
      method: "EFT" as const,
      paidAt: dateAt(2026, 3, 1),
    },
    {
      month: 4,
      status: "PAID" as const,
      method: "STRIPE" as const,
      paidAt: dateAt(2026, 4, 1),
    },
    {
      month: 5,
      status: "PAID" as const,
      method: "INTERAC" as const,
      paidAt: dateAt(2026, 5, 1),
    },
    {
      month: 6,
      status: "PAID" as const,
      method: "STRIPE" as const,
      paidAt: dateAt(2026, 6, 1),
    },
  ];

  for (const c of contribData) {
    const due = dateAt(2026, c.month, 1);
    const existing = await first(
      db
        .select()
        .from(savingsContributionsTable)
        .where(
          and(
            eq(savingsContributionsTable.circleId, circle7!.id),
            eq(savingsContributionsTable.userId, sarah!.id),
            eq(savingsContributionsTable.dueDate, due),
          ),
        ),
    );
    if (!existing) {
      await db.insert(savingsContributionsTable).values({
        circleId: circle7!.id,
        userId: sarah!.id,
        dueDate: due,
        paidAt: c.paidAt,
        amount: "200.00",
        status: c.status,
        method: c.method ?? undefined,
      });
    }
  }

  // Payout schedule (all 12 positions)
  for (let pos = 1; pos <= 12; pos++) {
    const existing = await first(
      db
        .select()
        .from(circlePayoutsTable)
        .where(
          and(
            eq(circlePayoutsTable.circleId, circle7!.id),
            eq(circlePayoutsTable.position, pos),
          ),
        ),
    );
    if (!existing) {
      await db.insert(circlePayoutsTable).values({
        circleId: circle7!.id,
        userId: pos === 4 ? sarah!.id : 0,
        position: pos,
        scheduledDate: dateAt(2026, pos, 1),
        amount: "2400.00",
        status: pos <= 6 ? "PAID" : "PENDING",
        paidDate: pos <= 6 ? dateAt(2026, pos, 5) : null,
      });
    }
  }

  // WWN Credit Score
  const existingScore = await first(
    db
      .select()
      .from(wwnCreditScoresTable)
      .where(eq(wwnCreditScoresTable.userId, sarah!.id)),
  );
  if (!existingScore) {
    const [cs] = await db
      .insert(wwnCreditScoresTable)
      .values({ userId: sarah!.id, score: 72 })
      .returning();
    await db.insert(creditScoreFactorsTable).values([
      {
        creditScoreId: cs.id,
        factorName: "Payment Consistency",
        score: 80,
        weight: "High",
      },
      {
        creditScoreId: cs.id,
        factorName: "Missed Contributions",
        score: 60,
        weight: "High",
      },
      {
        creditScoreId: cs.id,
        factorName: "Membership Duration",
        score: 85,
        weight: "Medium",
      },
      {
        creditScoreId: cs.id,
        factorName: "Participation History",
        score: 70,
        weight: "Medium",
      },
    ]);
  }

  // Matching Fund application
  const existingHH = await first(
    db
      .select()
      .from(helpingHandApplicationsTable)
      .where(eq(helpingHandApplicationsTable.userId, sarah!.id)),
  );
  if (!existingHH) {
    await db.insert(helpingHandApplicationsTable).values({
      userId: sarah!.id,
      requestedAmount: "2500.00",
      purpose: "Business start-up costs",
      term: "12 months",
      repaymentSchedule: "Monthly",
      notes:
        "Funding to launch an online tutoring service for youth in the GTA.",
      status: "UNDER_REVIEW",
    });
  }

  // Investment interest (WWN Community Bond)
  const bondOppId = oppMap["wwn-community-development-bond"];
  if (bondOppId) {
    const existingInterest = await first(
      db
        .select()
        .from(investmentInterestsTable)
        .where(eq(investmentInterestsTable.userId, sarah!.id)),
    );
    if (!existingInterest) {
      await db.insert(investmentInterestsTable).values({
        userId: sarah!.id,
        opportunityId: bondOppId,
        amount: "1000.00",
        status: "CONFIRMED",
        confirmedAt: dateAt(2026, 1, 20),
      });
      await db.insert(investmentTransactionsTable).values({
        userId: sarah!.id,
        opportunityId: bondOppId,
        amount: "1000.00",
        type: "CONTRIBUTION",
        description: "Initial investment — WWN Community Bond",
        processedAt: dateAt(2026, 1, 20),
      });
      await db.insert(investmentTransactionsTable).values({
        userId: sarah!.id,
        opportunityId: bondOppId,
        amount: "45.00",
        type: "RETURN",
        description: "Q1 2026 return — 4.5% p.a. pro-rated",
        processedAt: dateAt(2026, 4, 1),
      });
    }
  }

  // Course interest
  const foundationsCourse = await first(
    db
      .select()
      .from(academyCoursesTable)
      .where(eq(academyCoursesTable.slug, "financial-foundations-for-women")),
  );
  if (foundationsCourse) {
    const ei = await first(
      db
        .select()
        .from(courseInterestsTable)
        .where(
          and(
            eq(courseInterestsTable.userId, sarah!.id),
            eq(courseInterestsTable.courseId, foundationsCourse.id),
          ),
        ),
    );
    if (!ei)
      await db
        .insert(courseInterestsTable)
        .values({ userId: sarah!.id, courseId: foundationsCourse.id });
  }

  // Notifications
  const sarahNotifCount = await db
    .select()
    .from(notificationsTable)
    .where(eq(notificationsTable.userId, sarah!.id));
  if (sarahNotifCount.length === 0) {
    await db.insert(notificationsTable).values([
      {
        userId: sarah!.id,
        category: "SAVINGS",
        severity: "WARNING",
        read: false,
        title: "Contribution due in 3 days",
        body: "Your SUSU Circle #7 contribution of $200 is due on July 1, 2026.",
        actionLabel: "View Circle",
        actionHref: "/dashboard/savings",
      },
      {
        userId: sarah!.id,
        category: "INVESTMENT",
        severity: "INFO",
        read: false,
        title: "New investment opportunity available",
        body: 'A new SUSUNOMICS opportunity has been posted: "Community Housing Development — Scarborough". Expected return: 7–9% p.a.',
        actionLabel: "View Opportunity",
        actionHref: "/dashboard/investments",
      },
      {
        userId: sarah!.id,
        category: "MEMBERSHIP",
        severity: "SUCCESS",
        read: true,
        title: "Membership activated",
        body: "Your Individual Professional Membership has been activated. Welcome to WWN, Sarah!",
        actionLabel: "View Dashboard",
        actionHref: "/dashboard",
      },
      {
        userId: sarah!.id,
        category: "SAVINGS",
        severity: "ERROR",
        read: true,
        title: "Missed contribution — February 2026",
        body: "Your February 2026 SUSU Circle contribution of $200 was missed. This has affected your WWN Credit Score.",
        actionLabel: "View Circle",
        actionHref: "/dashboard/savings",
      },
      {
        userId: sarah!.id,
        category: "LENDING",
        severity: "INFO",
        read: true,
        title: "Matching Fund application received",
        body: "Your application for $2,500 has been received and is currently under review.",
        actionLabel: "View Application",
        actionHref: "/dashboard/lending",
      },
      {
        userId: sarah!.id,
        category: "PAYMENT",
        severity: "SUCCESS",
        read: true,
        title: "Payment confirmed — June 2026",
        body: "Your SUSU contribution of $200 for June 2026 has been received. Thank you!",
      },
    ]);
  }

  // Discussion post
  const existingDisc = await first(
    db
      .select()
      .from(discussionsTable)
      .where(eq(discussionsTable.authorId, sarah!.id)),
  );
  if (!existingDisc) {
    await db.insert(discussionsTable).values({
      authorId: sarah!.id,
      title: "Tips for staying consistent with SUSU contributions?",
      body: "Hey everyone! I missed my February contribution and it really affected my credit score. Would love to hear how others stay on top of it.",
      category: "Savings",
      pinned: true,
      viewCount: 412,
    });
  }

  // Member spotlight
  const existingSpot = await first(
    db
      .select()
      .from(memberSpotlightsTable)
      .where(eq(memberSpotlightsTable.userId, sarah!.id)),
  );
  if (!existingSpot) {
    await db.insert(memberSpotlightsTable).values({
      userId: sarah!.id,
      achievement:
        "Launched her tutoring service after completing the Financial Foundations course and applying for a Matching Fund loan. Now mentors other members!",
      featured: true,
      approvedAt: dateAt(2026, 5, 1),
    });
  }

  console.log("   ✅  Sarah Thompson created");

  // ── USER 2: Grace Okafor — Organization ──────────────────────
  console.log("   Creating Grace Okafor (Organization)...");

  let graceOrg = await first(
    db
      .select()
      .from(organizationsTable)
      .where(eq(organizationsTable.name, "Grace Innovations Inc.")),
  );
  if (!graceOrg) {
    [graceOrg] = await db
      .insert(organizationsTable)
      .values({
        name: "Grace Innovations Inc.",
        type: "Corporation",
        organizationType: "GRASSROOTS_ROSCA",
        operationalStatus: "ACTIVE",
        description:
          "A woman-led social enterprise delivering financial literacy training to underserved communities.",
        website: "https://graceinnovations.ca",
        contactName: "Grace Okafor",
        contactTitle: "Founder & CEO",
        businessType: "Corporation",
        yearsInBusiness: "3–5 years",
        annualRevenue: "$50K–$100K",
        province: "ON",
        city: "Mississauga",
      })
      .returning();
  } else {
    [graceOrg] = await db
      .update(organizationsTable)
      .set({
        organizationType: "GRASSROOTS_ROSCA",
        operationalStatus: "ACTIVE",
        updatedAt: new Date(),
      })
      .where(eq(organizationsTable.id, graceOrg.id))
      .returning();
  }

  let grace = await first(
    db
      .select()
      .from(usersTable)
      .where(eq(usersTable.email, "grace@demo.wwn.ca")),
  );
  if (!grace) {
    [grace] = await db
      .insert(usersTable)
      .values({
        email: "grace@demo.wwn.ca",
        username: "grace_demo",
        firstName: "Grace",
        lastName: "Okafor",
        phone: "+1-905-555-0202",
        passwordHash: DEMO_PASS,
        isEmailVerified: true,
        membershipType: "ORGANIZATION",
        membershipStatus: "PENDING",
        bio: "Founder of Grace Innovations — helping communities build financial resilience through education.",
        city: "Mississauga",
        province: "ON",
        ageRange: "35–44",
        occupation: "Entrepreneur",
        organizationId: graceOrg!.id,
      })
      .returning();
  }

  const graceOrgMember = await first(
    db
      .select()
      .from(organizationMembersTable)
      .where(
        and(
          eq(organizationMembersTable.organizationId, graceOrg!.id),
          eq(organizationMembersTable.userId, grace!.id),
        ),
      ),
  );
  if (!graceOrgMember) {
    await db.insert(organizationMembersTable).values({
      organizationId: graceOrg!.id,
      userId: grace!.id,
      role: "OWNER",
    });
  }

  const graceIntake = await first(
    db
      .select()
      .from(organizationIntakesTable)
      .where(eq(organizationIntakesTable.organizationId, graceOrg!.id)),
  );
  if (!graceIntake) {
    await db.insert(organizationIntakesTable).values({
      organizationId: graceOrg!.id,
      programInterests:
        "Financial Literacy Training, SUSU Savings for our clients, Academy access for staff",
      annualBudget: "$100K–$250K",
      staffCount: "6–10",
      goals:
        "We want to offer WWN's financial tools to our clients across the Peel Region and integrate SUSU savings circles into our program delivery.",
      additionalNotes: "We are actively seeking co-delivery partnerships.",
      status: "IN_REVIEW",
    });
  }

  const graceOrganizationPlan = await first(
    db
      .select()
      .from(membershipPlansTable)
      .where(
        and(
          eq(membershipPlansTable.membershipType, "ORGANIZATION"),
          eq(membershipPlansTable.tier, "PROFESSIONAL"),
          sql`${membershipPlansTable.organizationType} is null`,
        ),
      ),
  );

  const graceOrganizationSubscription =
    graceOrganizationPlan &&
    (await first(
      db
        .select()
        .from(organizationSubscriptionsTable)
        .where(eq(organizationSubscriptionsTable.organizationId, graceOrg!.id)),
    ));

  if (graceOrganizationPlan && !graceOrganizationSubscription) {
    await db.insert(organizationSubscriptionsTable).values({
      organizationId: graceOrg!.id,
      planId: graceOrganizationPlan.id,
      status: "ACTIVE",
      startDate: d(-45),
      renewsAt: d(320),
    });
  }

  const graceNotifCount = await db
    .select()
    .from(notificationsTable)
    .where(eq(notificationsTable.userId, grace!.id));
  if (graceNotifCount.length === 0) {
    await db.insert(notificationsTable).values([
      {
        userId: grace!.id,
        category: "MEMBERSHIP",
        severity: "INFO",
        read: false,
        title: "Intake questionnaire received",
        body: "Thank you for submitting your organization intake questionnaire. Our team is reviewing your application.",
        actionLabel: "View Status",
        actionHref: "/dashboard",
      },
      {
        userId: grace!.id,
        category: "SYSTEM",
        severity: "INFO",
        read: true,
        title: "Welcome to WWN, Grace Innovations!",
        body: "Your organization account has been created. Complete your intake questionnaire to begin the partnership process.",
        actionLabel: "View Dashboard",
        actionHref: "/dashboard",
      },
      {
        userId: grace!.id,
        category: "COMMUNITY",
        severity: "INFO",
        read: true,
        title: "WWN Investor Summit 2026 — Save the date",
        body: "The annual WWN Investor Summit is on September 20, 2026 in Toronto and virtually.",
        actionLabel: "RSVP",
        actionHref: "/dashboard/community",
      },
    ]);
  }
  console.log("   ✅  Grace Okafor created");

  // ── USER 3: Fatima Al-Hassan — Investor / Elite ───────────────
  console.log("   Creating Fatima Al-Hassan (Investor)...");

  let fatima = await first(
    db
      .select()
      .from(usersTable)
      .where(eq(usersTable.email, "fatima@demo.wwn.ca")),
  );
  if (!fatima) {
    [fatima] = await db
      .insert(usersTable)
      .values({
        email: "fatima@demo.wwn.ca",
        username: "fatima_demo",
        firstName: "Fatima",
        lastName: "Al-Hassan",
        phone: "+1-647-555-0303",
        passwordHash: DEMO_PASS,
        isEmailVerified: true,
        membershipType: "INVESTOR",
        membershipStatus: "ACTIVE",
        investorCategory: "IMPACT_INVESTOR",
        bio: "CFA charterholder passionate about directing capital to underinvested communities across Canada.",
        city: "Vancouver",
        province: "BC",
        ageRange: "45–54",
        occupation: "Investment Professional",
        howHeard: "Event",
      })
      .returning();
  }

  const elitePlan = await first(
    db
      .select()
      .from(membershipPlansTable)
      .where(
        and(
          eq(membershipPlansTable.membershipType, "INVESTOR"),
          eq(membershipPlansTable.tier, "ELITE"),
        ),
      ),
  );

  const fatimaSub = await first(
    db
      .select()
      .from(membershipSubscriptionsTable)
      .where(eq(membershipSubscriptionsTable.userId, fatima!.id)),
  );
  if (!fatimaSub && elitePlan) {
    await db.insert(membershipSubscriptionsTable).values({
      userId: fatima!.id,
      planId: elitePlan.id,
      status: "ACTIVE",
      startDate: dateAt(2025, 10, 1),
      endDate: dateAt(2026, 10, 1),
      renewsAt: dateAt(2026, 10, 1),
    });
  }

  let fatimaMicroCommitment = await first(
    db
      .select()
      .from(lendingCommitmentsTable)
      .where(
        and(
          eq(lendingCommitmentsTable.userId, fatima!.id),
          eq(lendingCommitmentsTable.opportunityId, microLoanOpportunity!.id),
        ),
      ),
  );
  if (!fatimaMicroCommitment) {
    [fatimaMicroCommitment] = await db
      .insert(lendingCommitmentsTable)
      .values({
        userId: fatima!.id,
        opportunityId: microLoanOpportunity!.id,
        amount: "22000.00",
        status: "CONFIRMED",
        confirmedAt: dateAt(2026, 3, 15),
      })
      .returning();
  } else {
    await db
      .update(lendingCommitmentsTable)
      .set({
        amount: "22000.00",
        status: "CONFIRMED",
        confirmedAt: fatimaMicroCommitment.confirmedAt ?? dateAt(2026, 3, 15),
        statusChangedAt:
          fatimaMicroCommitment.statusChangedAt ?? dateAt(2026, 3, 15),
        updatedAt: new Date(),
      })
      .where(eq(lendingCommitmentsTable.id, fatimaMicroCommitment.id));
  }

  let fatimaReliefCommitment = await first(
    db
      .select()
      .from(lendingCommitmentsTable)
      .where(
        and(
          eq(lendingCommitmentsTable.userId, fatima!.id),
          eq(lendingCommitmentsTable.opportunityId, reliefOpportunity!.id),
        ),
      ),
  );
  if (!fatimaReliefCommitment) {
    [fatimaReliefCommitment] = await db
      .insert(lendingCommitmentsTable)
      .values({
        userId: fatima!.id,
        opportunityId: reliefOpportunity!.id,
        amount: "500.00",
        status: "CONFIRMED",
        confirmedAt: dateAt(2026, 4, 1),
      })
      .returning();
  } else {
    await db
      .update(lendingCommitmentsTable)
      .set({
        amount: "500.00",
        status: "CONFIRMED",
        confirmedAt: fatimaReliefCommitment.confirmedAt ?? dateAt(2026, 4, 1),
        statusChangedAt:
          fatimaReliefCommitment.statusChangedAt ?? dateAt(2026, 4, 1),
        updatedAt: new Date(),
      })
      .where(eq(lendingCommitmentsTable.id, fatimaReliefCommitment.id));
  }

  const housingOppId = oppMap["community-housing-scarborough"];
  if (housingOppId) {
    const existingFI = await first(
      db
        .select()
        .from(investmentInterestsTable)
        .where(eq(investmentInterestsTable.userId, fatima!.id)),
    );
    if (!existingFI) {
      await db.insert(investmentInterestsTable).values({
        userId: fatima!.id,
        opportunityId: housingOppId,
        amount: "5000.00",
        status: "CONFIRMED",
        confirmedAt: dateAt(2026, 2, 10),
      });
      await db.insert(investmentTransactionsTable).values({
        userId: fatima!.id,
        opportunityId: housingOppId,
        amount: "5000.00",
        type: "CONTRIBUTION",
        description: "Impact Investor — Community Housing Development",
        processedAt: dateAt(2026, 2, 10),
      });
    }
  }

  const existingFatimaSpot = await first(
    db
      .select()
      .from(memberSpotlightsTable)
      .where(eq(memberSpotlightsTable.userId, fatima!.id)),
  );
  if (!existingFatimaSpot) {
    await db.insert(memberSpotlightsTable).values({
      userId: fatima!.id,
      achievement:
        "Committed $5,000 to the Community Housing Development fund, making her the first Impact Investor from our BC cohort.",
      featured: true,
      approvedAt: dateAt(2026, 2, 15),
    });
  }

  const fatimaNotifCount = await db
    .select()
    .from(notificationsTable)
    .where(eq(notificationsTable.userId, fatima!.id));
  if (fatimaNotifCount.length === 0) {
    await db.insert(notificationsTable).values([
      {
        userId: fatima!.id,
        category: "INVESTMENT",
        severity: "SUCCESS",
        read: false,
        title: "New opportunity: Cooperative Farm — Ontario Food Hub",
        body: "A new SUSUNOMICS opportunity is open. Agricultural co-op targeting 5–7% p.a. Minimum investment: $300.",
        actionLabel: "View Opportunity",
        actionHref: "/dashboard/investments",
      },
      {
        userId: fatima!.id,
        category: "LENDING",
        severity: "SUCCESS",
        read: false,
        title: "Lending commitment confirmed",
        body: "Your $22,000 commitment to the Small Business Micro-Loan Pool has been confirmed.",
        actionLabel: "View Portfolio",
        actionHref: "/dashboard/lending",
      },
      {
        userId: fatima!.id,
        category: "COMMUNITY",
        severity: "INFO",
        read: true,
        title: "WWN Investor Summit 2026 — Invitation",
        body: "As an Impact Investor, you are invited to attend the WWN Investor Summit on September 20, 2026.",
        actionLabel: "RSVP Now",
        actionHref: "/dashboard/community",
      },
      {
        userId: fatima!.id,
        category: "MEMBERSHIP",
        severity: "SUCCESS",
        read: true,
        title: "Impact Investor membership activated",
        body: "Welcome to the WWN Impact Investor circle, Fatima. Your membership includes Advisory Committee access.",
        actionLabel: "View Portal",
        actionHref: "/dashboard",
      },
    ]);
  }
  console.log("   ✅  Fatima Al-Hassan created");

  // ── USER 4: WWN Admin ─────────────────────────────────────────
  console.log("   Creating WWN Admin...");

  const adminExists = await first(
    db
      .select()
      .from(usersTable)
      .where(eq(usersTable.email, "admin@demo.wwn.ca")),
  );
  if (!adminExists) {
    await db.insert(usersTable).values({
      email: "admin@demo.wwn.ca",
      username: "admin_demo",
      firstName: "WWN",
      lastName: "Admin",
      passwordHash: ADMIN_PASS,
      isEmailVerified: true,
      role: "admin",
      userRole: "ADMIN",
      membershipStatus: "ACTIVE",
    });
  }
  console.log("   ✅  WWN Admin created");

  const adminUser = await first(
    db
      .select()
      .from(usersTable)
      .where(eq(usersTable.email, "admin@demo.wwn.ca")),
  );

  const ensureWallet = async (
    ownerType: "USER" | "SUSU" | "LENDING_POOL",
    ownerId: number,
  ) => {
    let wallet = await first(
      db
        .select()
        .from(walletsTable)
        .where(
          and(
            eq(walletsTable.ownerType, ownerType as any),
            eq(walletsTable.ownerId, ownerId),
          ),
        ),
    );

    if (!wallet) {
      [wallet] = await db
        .insert(walletsTable)
        .values({ ownerType: ownerType as any, ownerId })
        .returning();
    }

    return wallet!;
  };

  const upsertWalletTransaction = async (params: {
    idempotencyKey: string;
    type:
      | "TOP_UP"
      | "LOAN_DISBURSEMENT"
      | "LOAN_REPAYMENT"
      | "HELPING_HAND_DISBURSEMENT"
      | "HELPING_HAND_REPAYMENT";
    fromWalletId?: number | null;
    toWalletId?: number | null;
    amount: string;
    metadata?: Record<string, unknown>;
  }) => {
    let transaction = await first(
      db
        .select()
        .from(walletTransactionsTable)
        .where(
          eq(walletTransactionsTable.idempotencyKey, params.idempotencyKey),
        ),
    );

    if (!transaction) {
      [transaction] = await db
        .insert(walletTransactionsTable)
        .values({
          idempotencyKey: params.idempotencyKey,
          type: params.type as any,
          fromWalletId: params.fromWalletId ?? null,
          toWalletId: params.toWalletId ?? null,
          amount: params.amount,
          metadata: params.metadata ?? {},
        })
        .returning();
    } else {
      [transaction] = await db
        .update(walletTransactionsTable)
        .set({
          type: params.type as any,
          fromWalletId: params.fromWalletId ?? null,
          toWalletId: params.toWalletId ?? null,
          amount: params.amount,
          metadata: params.metadata ?? {},
        })
        .where(eq(walletTransactionsTable.id, transaction.id))
        .returning();
    }

    return transaction!;
  };

  const seedLendingReturnAllocation = async (params: {
    poolId: number;
    repaymentId: number;
    loanApplicationId: number;
    walletTransactionId: number | null;
    grossAmount: string;
    returnAmount?: string | null;
    principalAmount?: string | null;
    occurredAt?: Date | null;
  }) => {
    const existingEvents = await db
      .select({ id: lendingPoolReturnEventsTable.id })
      .from(lendingPoolReturnEventsTable)
      .where(eq(lendingPoolReturnEventsTable.repaymentId, params.repaymentId));

    if (existingEvents.length) {
      await db.delete(lendingCommitmentReturnAllocationsTable).where(
        inArray(
          lendingCommitmentReturnAllocationsTable.returnEventId,
          existingEvents.map((event) => event.id),
        ),
      );
      await db
        .delete(lendingPoolReturnEventsTable)
        .where(
          eq(lendingPoolReturnEventsTable.repaymentId, params.repaymentId),
        );
    }

    const grossAmount = parseMoney(params.grossAmount);
    const returnAmount = parseMoney(params.returnAmount);
    const principalAmount =
      params.principalAmount != null
        ? parseMoney(params.principalAmount)
        : Math.max(0, grossAmount - returnAmount);

    if (grossAmount <= 0) return;

    const [event] = await db
      .insert(lendingPoolReturnEventsTable)
      .values({
        poolId: params.poolId,
        repaymentId: params.repaymentId,
        loanApplicationId: params.loanApplicationId,
        walletTransactionId: params.walletTransactionId,
        grossAmount: formatMoney(grossAmount),
        principalAmount: formatMoney(principalAmount),
        returnAmount: formatMoney(returnAmount),
        allocatedReturnAmount: "0.00",
        occurredAt: params.occurredAt ?? new Date(),
      })
      .returning();

    if (returnAmount <= 0) return;

    const commitments = await db
      .select({
        id: lendingCommitmentsTable.id,
        userId: lendingCommitmentsTable.userId,
        amount: lendingCommitmentsTable.amount,
      })
      .from(lendingCommitmentsTable)
      .where(
        and(
          eq(lendingCommitmentsTable.poolId, params.poolId),
          eq(lendingCommitmentsTable.status, "CONFIRMED"),
        ),
      );

    const totalCommitted = commitments.reduce((sum, commitment) => {
      return sum + parseMoney(commitment.amount);
    }, 0);
    if (totalCommitted <= 0) return;

    const totalReturnCents = Math.round(returnAmount * 100);
    const normalized = commitments.map((commitment, index) => {
      const amount = parseMoney(commitment.amount);
      const rawCents = (totalReturnCents * amount) / totalCommitted;
      return {
        index,
        commitment,
        amount,
        cents: Math.floor(rawCents),
        remainder: rawCents - Math.floor(rawCents),
      };
    });
    let remainingCents =
      totalReturnCents - normalized.reduce((sum, item) => sum + item.cents, 0);
    normalized
      .slice()
      .sort((a, b) => b.remainder - a.remainder)
      .forEach((item) => {
        if (remainingCents <= 0) return;
        normalized[item.index]!.cents += 1;
        remainingCents -= 1;
      });

    await db.insert(lendingCommitmentReturnAllocationsTable).values(
      normalized.map((item) => ({
        returnEventId: event.id,
        commitmentId: item.commitment.id,
        poolId: params.poolId,
        userId: item.commitment.userId,
        commitmentAmountSnapshot: formatMoney(item.amount),
        ownershipPctSnapshot: (item.amount / totalCommitted).toFixed(6),
        allocatedReturnAmount: formatMoney(item.cents / 100),
        occurredAt: params.occurredAt ?? new Date(),
      })),
    );

    await db
      .update(lendingPoolReturnEventsTable)
      .set({
        allocatedReturnAmount: formatMoney(returnAmount),
      })
      .where(eq(lendingPoolReturnEventsTable.id, event.id));
  };

  const ensureLifecycleMember = async (params: {
    email: string;
    username: string;
    firstName: string;
    lastName: string;
    segment: "PROFESSIONAL" | "ENTREPRENEUR" | "YOUTH";
    city: string;
    province: string;
    occupation: string;
    creditScore: number;
    circlePosition: number;
  }) => {
    let user = await first(
      db.select().from(usersTable).where(eq(usersTable.email, params.email)),
    );

    if (!user) {
      [user] = await db
        .insert(usersTable)
        .values({
          email: params.email,
          username: params.username,
          firstName: params.firstName,
          lastName: params.lastName,
          phone: "+1-416-555-9999",
          passwordHash: DEMO_PASS,
          isEmailVerified: true,
          membershipType: "INDIVIDUAL",
          membershipStatus: "ACTIVE",
          segment: params.segment,
          city: params.city,
          province: params.province,
          occupation: params.occupation,
          howHeard: "WWN Demo",
          goals:
            "Lifecycle demo account for lending and Matching Fund journeys.",
        })
        .returning();
    }

    const existingScoreRow = await first(
      db
        .select()
        .from(wwnCreditScoresTable)
        .where(eq(wwnCreditScoresTable.userId, user!.id)),
    );
    if (!existingScoreRow) {
      const [score] = await db
        .insert(wwnCreditScoresTable)
        .values({ userId: user!.id, score: params.creditScore })
        .returning();
      await db.insert(creditScoreFactorsTable).values([
        {
          creditScoreId: score.id,
          factorName: "Payment Consistency",
          score: params.creditScore,
          weight: "High",
        },
        {
          creditScoreId: score.id,
          factorName: "Membership Duration",
          score: 78,
          weight: "Medium",
        },
        {
          creditScoreId: score.id,
          factorName: "Participation History",
          score: 82,
          weight: "Medium",
        },
      ]);
    }

    const membership = await first(
      db
        .select()
        .from(circleMembershipsTable)
        .where(
          and(
            eq(circleMembershipsTable.circleId, circle7!.id),
            eq(circleMembershipsTable.userId, user!.id),
          ),
        ),
    );
    if (!membership) {
      await db.insert(circleMembershipsTable).values({
        circleId: circle7!.id,
        userId: user!.id,
        position: params.circlePosition,
        joinedAt: dateAt(2026, 1, 10),
      });
    }

    for (const month of [1, 2, 3, 4, 5, 6]) {
      const dueDate = dateAt(2026, month, 1);
      const existingContribution = await first(
        db
          .select()
          .from(savingsContributionsTable)
          .where(
            and(
              eq(savingsContributionsTable.circleId, circle7!.id),
              eq(savingsContributionsTable.userId, user!.id),
              eq(savingsContributionsTable.dueDate, dueDate),
            ),
          ),
      );
      if (!existingContribution) {
        await db.insert(savingsContributionsTable).values({
          circleId: circle7!.id,
          userId: user!.id,
          dueDate,
          paidAt: dateAt(2026, month, 2),
          amount: "200.00",
          status: "PAID",
          method: "STRIPE",
        });
      }
    }

    const existingPayout = await first(
      db
        .select()
        .from(circlePayoutsTable)
        .where(
          and(
            eq(circlePayoutsTable.circleId, circle7!.id),
            eq(circlePayoutsTable.userId, user!.id),
            eq(circlePayoutsTable.position, params.circlePosition),
          ),
        ),
    );
    if (!existingPayout) {
      await db.insert(circlePayoutsTable).values({
        circleId: circle7!.id,
        userId: user!.id,
        position: params.circlePosition,
        scheduledDate: dateAt(2026, params.circlePosition, 1),
        paidDate:
          params.circlePosition <= 6
            ? dateAt(2026, params.circlePosition, 5)
            : null,
        amount: "2400.00",
        status: params.circlePosition <= 6 ? "PAID" : "PENDING",
      });
    }

    return user!;
  };

  const normalizeCircleMembershipOrder = async (circleId: number) => {
    const [circle] = await db
      .select()
      .from(savingsCirclesTable)
      .where(eq(savingsCirclesTable.id, circleId));
    if (!circle) return;

    const activeMemberships = await db
      .select()
      .from(circleMembershipsTable)
      .where(
        and(
          eq(circleMembershipsTable.circleId, circleId),
          ne(circleMembershipsTable.position, 1),
        ),
      )
      .orderBy(
        asc(circleMembershipsTable.joinedAt),
        asc(circleMembershipsTable.position),
        asc(circleMembershipsTable.userId),
      );

    for (const [index, membership] of activeMemberships.entries()) {
      const desiredPosition = index + 2;
      if (membership.position === desiredPosition) continue;

      await db
        .update(circleMembershipsTable)
        .set({
          position: desiredPosition,
        })
        .where(eq(circleMembershipsTable.id, membership.id));
    }

    const normalizedMemberships = await db
      .select()
      .from(circleMembershipsTable)
      .where(
        and(
          eq(circleMembershipsTable.circleId, circleId),
          ne(circleMembershipsTable.position, 1),
        ),
      )
      .orderBy(asc(circleMembershipsTable.position));

    const memberIds = normalizedMemberships.map(
      (membership) => membership.userId,
    );
    if (memberIds.length) {
      await db
        .delete(circlePayoutsTable)
        .where(
          and(
            eq(circlePayoutsTable.circleId, circleId),
            inArray(circlePayoutsTable.userId, memberIds),
          ),
        );
    }

    const payoutAmount = formatMoney(
      parseMoney(circle.contributionAmount) * circle.maxMembers,
    );

    for (const membership of normalizedMemberships) {
      const scheduledDate = getCirclePayoutDate(
        circle.startDate,
        circle.frequency,
        membership.position,
      );
      await db.insert(circlePayoutsTable).values({
        circleId,
        userId: membership.userId,
        position: membership.position,
        scheduledDate,
        paidDate:
          membership.position <= 6
            ? dateAt(2026, membership.position, 5)
            : null,
        amount: payoutAmount,
        status: membership.position <= 6 ? "PAID" : "PENDING",
      });
    }
  };

  await ensureWallet("SUSU", 1);

  const upsertLoanApplication = async (params: {
    borrowerId: number;
    poolId: number;
    purpose: string;
    status:
      | "WAITLISTED"
      | "SUBMITTED"
      | "UNDER_REVIEW"
      | "APPROVED"
      | "APPROVED_RESERVED"
      | "FUNDED"
      | "REPAYING"
      | "CLOSED"
      | "REJECTED"
      | "CANCELLED";
    requestedAmount: string;
    approvedAmount?: string;
    disbursedAmount?: string;
    reviewNote?: string;
    poolAssignedAt?: Date;
    poolReassignedAt?: Date;
    poolReassignmentNote?: string;
    reservedAt?: Date;
    reviewedAt?: Date;
    disbursedAt?: Date;
    repayments?: Array<{
      dueDate: Date;
      amount: string;
      status: "PENDING" | "PAID" | "LATE" | "WAIVED";
      paidAmount?: string | null;
      principalPaidAmount?: string | null;
      returnPaidAmount?: string | null;
      creditedToPoolAmount?: string | null;
      paidAt?: Date | null;
      method?: "STRIPE" | "EFT" | "INTERAC" | "WIRE" | "WALLET";
    }>;
  }) => {
    let application = await first(
      db
        .select()
        .from(loanApplicationsTable)
        .where(
          and(
            eq(loanApplicationsTable.borrowerId, params.borrowerId),
            eq(loanApplicationsTable.purpose, params.purpose),
          ),
        ),
    );

    const borrowerWallet = await ensureWallet("USER", params.borrowerId);
    const poolWallet = await ensureWallet("LENDING_POOL", params.poolId);
    const poolRecord = await first(
      db
        .select()
        .from(lendingPoolsTable)
        .where(eq(lendingPoolsTable.id, params.poolId)),
    );
    const borrowerAppreciationAmount =
      poolRecord?.borrowerAppreciationAmount ?? "0.00";
    const termMonths = poolRecord?.borrowerTermMonths ?? 12;
    const repaymentFrequency =
      (poolRecord?.repaymentFrequency as "WEEKLY" | "BIWEEKLY" | "MONTHLY") ??
      "MONTHLY";
    const processingFeePct = poolRecord?.processingFeePct ?? null;
    const pricing = buildSeedLoanPricing({
      principalAmount: params.approvedAmount ?? params.requestedAmount,
      borrowerAppreciationAmount,
      termMonths,
      repaymentFrequency,
      processingFeePct,
    });

    let disbursementTransactionId: number | null =
      application?.disbursementTransactionId ?? null;
    if (params.disbursedAmount && params.disbursedAt) {
      const disbursement = await upsertWalletTransaction({
        idempotencyKey: `seed:loan-disbursement:${params.borrowerId}:${params.purpose}`,
        type: "LOAN_DISBURSEMENT",
        fromWalletId: poolWallet.id,
        toWalletId: borrowerWallet.id,
        amount: params.disbursedAmount,
        metadata: {
          seeded: true,
          borrowerId: params.borrowerId,
          poolId: params.poolId,
          purpose: params.purpose,
        },
      });
      disbursementTransactionId = disbursement.id;
    }

    if (!application) {
      [application] = await db
        .insert(loanApplicationsTable)
        .values({
          borrowerId: params.borrowerId,
          poolId: params.poolId,
          requestedAmount: params.requestedAmount,
          purpose: params.purpose,
          term: pricing.term,
          repaymentSchedule: pricing.repaymentSchedule,
          termMonths,
          borrowerAppreciationAmount,
          repaymentFrequency,
          processingFeePct,
          pricingSourcePoolId: params.poolId,
          pricingLockedAt:
            params.reviewedAt ?? params.poolAssignedAt ?? new Date(),
          totalRepayableAmount: pricing.totalRepayableAmount,
          estimatedInstallmentAmount: pricing.estimatedInstallmentAmount,
          notes: `${params.status} Lending Pool demo case`,
          status: params.status,
          poolAssignedAt: params.poolAssignedAt ?? new Date(),
          poolAssignedByAdminId: adminUser?.id ?? null,
          poolReassignedAt: params.poolReassignedAt ?? null,
          poolReassignedByAdminId: params.poolReassignedAt
            ? (adminUser?.id ?? null)
            : null,
          poolReassignmentNote: params.poolReassignmentNote ?? null,
          approvedAmount: params.approvedAmount ?? null,
          reservedAt: params.reservedAt ?? null,
          reservedByAdminId: params.reservedAt ? (adminUser?.id ?? null) : null,
          disbursedAmount: params.disbursedAmount ?? null,
          disbursedAt: params.disbursedAt ?? null,
          disbursementTransactionId,
          reviewNote: params.reviewNote ?? null,
          reviewedAt: params.reviewedAt ?? null,
          reviewedByAdminId: adminUser?.id ?? null,
        })
        .returning();
    } else {
      [application] = await db
        .update(loanApplicationsTable)
        .set({
          poolId: params.poolId,
          requestedAmount: params.requestedAmount,
          purpose: params.purpose,
          term: pricing.term,
          repaymentSchedule: pricing.repaymentSchedule,
          termMonths,
          borrowerAppreciationAmount,
          repaymentFrequency,
          processingFeePct,
          pricingSourcePoolId: params.poolId,
          pricingLockedAt:
            params.reviewedAt ??
            application.pricingLockedAt ??
            params.poolAssignedAt ??
            new Date(),
          totalRepayableAmount: pricing.totalRepayableAmount,
          estimatedInstallmentAmount: pricing.estimatedInstallmentAmount,
          notes: `${params.status} Lending Pool demo case`,
          status: params.status,
          poolAssignedAt:
            params.poolAssignedAt ?? application.poolAssignedAt ?? new Date(),
          poolAssignedByAdminId: adminUser?.id ?? null,
          poolReassignedAt: params.poolReassignedAt ?? null,
          poolReassignedByAdminId: params.poolReassignedAt
            ? (adminUser?.id ?? null)
            : null,
          poolReassignmentNote: params.poolReassignmentNote ?? null,
          approvedAmount: params.approvedAmount ?? null,
          reservedAt: params.reservedAt ?? null,
          reservedByAdminId: params.reservedAt ? (adminUser?.id ?? null) : null,
          disbursedAmount: params.disbursedAmount ?? null,
          disbursedAt: params.disbursedAt ?? null,
          disbursementTransactionId,
          reviewNote: params.reviewNote ?? null,
          reviewedAt: params.reviewedAt ?? null,
          reviewedByAdminId: params.reviewedAt ? (adminUser?.id ?? null) : null,
          updatedAt: new Date(),
        })
        .where(eq(loanApplicationsTable.id, application.id))
        .returning();
    }

    if (params.repayments?.length) {
      for (const repayment of params.repayments) {
        const creditedToPoolAmount =
          repayment.creditedToPoolAmount ?? repayment.paidAmount ?? null;
        const returnPaidAmount = repayment.returnPaidAmount ?? null;
        const principalPaidAmount =
          repayment.principalPaidAmount ??
          (creditedToPoolAmount
            ? formatMoney(
                Math.max(
                  0,
                  parseMoney(creditedToPoolAmount) -
                    parseMoney(returnPaidAmount),
                ),
              )
            : "0.00");
        let existingRepayment = await first(
          db
            .select()
            .from(loanRepaymentsTable)
            .where(
              and(
                eq(loanRepaymentsTable.loanApplicationId, application!.id),
                eq(loanRepaymentsTable.dueDate, repayment.dueDate),
              ),
            ),
        );

        if (!existingRepayment) {
          [existingRepayment] = await db
            .insert(loanRepaymentsTable)
            .values({
              loanApplicationId: application!.id,
              dueDate: repayment.dueDate,
              amount: repayment.amount,
              paidAmount: repayment.paidAmount ?? null,
              principalPaidAmount,
              returnPaidAmount: returnPaidAmount ?? "0.00",
              creditedToPoolAmount: creditedToPoolAmount ?? "0",
              paidAt: repayment.paidAt ?? null,
              status: repayment.status,
              method: repayment.method,
            })
            .returning();
        } else {
          [existingRepayment] = await db
            .update(loanRepaymentsTable)
            .set({
              amount: repayment.amount,
              paidAmount: repayment.paidAmount ?? null,
              principalPaidAmount,
              returnPaidAmount: returnPaidAmount ?? "0.00",
              creditedToPoolAmount: creditedToPoolAmount ?? "0",
              paidAt: repayment.paidAt ?? null,
              status: repayment.status,
              method: repayment.method,
            })
            .where(eq(loanRepaymentsTable.id, existingRepayment.id))
            .returning();
        }

        if (creditedToPoolAmount && creditedToPoolAmount !== "0") {
          const repaymentWalletTransaction = await upsertWalletTransaction({
            idempotencyKey: `seed:loan-repayment:${application.id}:${repayment.dueDate.toISOString()}`,
            type: "LOAN_REPAYMENT",
            toWalletId: poolWallet.id,
            amount: creditedToPoolAmount,
            metadata: {
              seeded: true,
              borrowerId: params.borrowerId,
              poolId: params.poolId,
              loanApplicationId: application.id,
              loanRepaymentId: existingRepayment.id,
              dueDate: repayment.dueDate.toISOString(),
            },
          });

          await seedLendingReturnAllocation({
            poolId: params.poolId,
            repaymentId: existingRepayment.id,
            loanApplicationId: application.id,
            walletTransactionId: repaymentWalletTransaction.id,
            grossAmount: creditedToPoolAmount,
            principalAmount: principalPaidAmount,
            returnAmount: returnPaidAmount,
            occurredAt: repayment.paidAt ?? repayment.dueDate,
          });
        }
      }
    }
  };

  const upsertHelpingHandApplication = async (params: {
    userId: number;
    poolId?: number;
    purpose: string;
    status:
      | "SUBMITTED"
      | "UNDER_REVIEW"
      | "APPROVED"
      | "FUNDED"
      | "REPAYING"
      | "CLOSED"
      | "REJECTED";
    requestedAmount: string;
    approvedAmount?: string;
    disbursedAmount?: string;
    reviewNote?: string;
    reviewedAt?: Date;
    disbursedAt?: Date;
    repayments?: Array<{
      dueDate: Date;
      amount: string;
      status: "PENDING" | "PAID" | "LATE" | "WAIVED";
      paidAmount?: string | null;
      paidAt?: Date | null;
      method?: "STRIPE" | "EFT" | "INTERAC" | "WIRE" | "WALLET";
    }>;
  }) => {
    let application = await first(
      db
        .select()
        .from(helpingHandApplicationsTable)
        .where(
          and(
            eq(helpingHandApplicationsTable.userId, params.userId),
            eq(helpingHandApplicationsTable.purpose, params.purpose),
          ),
        ),
    );

    const nextApplicationValues = {
      userId: params.userId,
      poolId: params.poolId ?? null,
      requestedAmount: params.requestedAmount,
      purpose: params.purpose,
      term: "12 months",
      repaymentSchedule: "Monthly",
      notes: `${params.status} Matching Fund demo case`,
      status: params.status,
      poolAssignedAt:
        params.poolId && (params.reviewedAt ?? params.disbursedAt)
          ? (params.reviewedAt ?? params.disbursedAt ?? null)
          : null,
      poolAssignedByAdminId: params.poolId ? (adminUser?.id ?? null) : null,
      approvedAmount: params.approvedAmount ?? null,
      disbursedAmount: params.disbursedAmount ?? params.approvedAmount ?? null,
      reviewNote: params.reviewNote ?? null,
      reviewedAt: params.reviewedAt ?? null,
      reviewedByAdminId: adminUser?.id ?? null,
      disbursedAt: params.disbursedAt ?? null,
      updatedAt: new Date(),
    };

    if (!application) {
      [application] = await db
        .insert(helpingHandApplicationsTable)
        .values(nextApplicationValues)
        .returning();
    } else {
      [application] = await db
        .update(helpingHandApplicationsTable)
        .set(nextApplicationValues as any)
        .where(eq(helpingHandApplicationsTable.id, application.id))
        .returning();
    }

    if (
      params.poolId &&
      params.disbursedAt &&
      ["FUNDED", "REPAYING", "CLOSED"].includes(params.status)
    ) {
      const [memberWallet, poolWallet] = await Promise.all([
        ensureWallet("USER", params.userId),
        ensureWallet("LENDING_POOL", params.poolId),
      ]);

      const disbursementTransaction = await upsertWalletTransaction({
        idempotencyKey: `seed:helping-hand-disbursement:${application.id}`,
        type: "HELPING_HAND_DISBURSEMENT",
        fromWalletId: poolWallet.id,
        toWalletId: memberWallet.id,
        amount:
          params.disbursedAmount ??
          params.approvedAmount ??
          params.requestedAmount,
        metadata: {
          seeded: true,
          helpingHandApplicationId: application.id,
          poolId: params.poolId,
          userId: params.userId,
        },
      });

      [application] = await db
        .update(helpingHandApplicationsTable)
        .set({
          disbursementTransactionId: disbursementTransaction.id,
          disbursedAmount:
            params.disbursedAmount ??
            params.approvedAmount ??
            params.requestedAmount,
          updatedAt: new Date(),
        } as any)
        .where(eq(helpingHandApplicationsTable.id, application.id))
        .returning();
    }

    if (params.repayments?.length) {
      const existingRepayment = await first(
        db
          .select()
          .from(helpingHandRepaymentsTable)
          .where(
            eq(
              helpingHandRepaymentsTable.helpingHandApplicationId,
              application!.id,
            ),
          ),
      );
      if (!existingRepayment) {
        await db.insert(helpingHandRepaymentsTable).values(
          params.repayments.map((repayment) => ({
            helpingHandApplicationId: application!.id,
            dueDate: repayment.dueDate,
            amount: repayment.amount,
            paidAmount: repayment.paidAmount ?? null,
            paidAt: repayment.paidAt ?? null,
            status: repayment.status,
            method: repayment.method,
          })),
        );
      }

      if (params.poolId) {
        const [memberWallet, poolWallet] = await Promise.all([
          ensureWallet("USER", params.userId),
          ensureWallet("LENDING_POOL", params.poolId),
        ]);

        const repayments = await db
          .select()
          .from(helpingHandRepaymentsTable)
          .where(
            eq(
              helpingHandRepaymentsTable.helpingHandApplicationId,
              application.id,
            ),
          )
          .orderBy(asc(helpingHandRepaymentsTable.dueDate));

        for (const [index, repayment] of params.repayments.entries()) {
          const paidAmount = repayment.paidAmount ?? null;
          if (!paidAmount || Number(paidAmount) <= 0) continue;

          await upsertWalletTransaction({
            idempotencyKey: `seed:helping-hand-repayment:${application.id}:${index}`,
            type: "HELPING_HAND_REPAYMENT",
            fromWalletId: memberWallet.id,
            toWalletId: poolWallet.id,
            amount: paidAmount,
            metadata: {
              seeded: true,
              helpingHandApplicationId: application.id,
              helpingHandRepaymentId: repayments[index]?.id ?? null,
              poolId: params.poolId,
              userId: params.userId,
            },
          });
        }
      }
    }
  };

  console.log("   Creating lifecycle demo members...");

  const amina = await ensureLifecycleMember({
    email: "amina@demo.wwn.ca",
    username: "amina_demo",
    firstName: "Amina",
    lastName: "Bello",
    segment: "ENTREPRENEUR",
    city: "Toronto",
    province: "ON",
    occupation: "Catering Founder",
    creditScore: 79,
    circlePosition: 5,
  });

  const naomi = await ensureLifecycleMember({
    email: "naomi@demo.wwn.ca",
    username: "naomi_demo",
    firstName: "Naomi",
    lastName: "Mensah",
    segment: "PROFESSIONAL",
    city: "Brampton",
    province: "ON",
    occupation: "Retail Operator",
    creditScore: 82,
    circlePosition: 6,
  });

  const chioma = await ensureLifecycleMember({
    email: "chioma@demo.wwn.ca",
    username: "chioma_demo",
    firstName: "Chioma",
    lastName: "Adeyemi",
    segment: "ENTREPRENEUR",
    city: "Mississauga",
    province: "ON",
    occupation: "Salon Owner",
    creditScore: 76,
    circlePosition: 7,
  });

  const zuri = await ensureLifecycleMember({
    email: "zuri@demo.wwn.ca",
    username: "zuri_demo",
    firstName: "Zuri",
    lastName: "Carter",
    segment: "PROFESSIONAL",
    city: "Hamilton",
    province: "ON",
    occupation: "Food Producer",
    creditScore: 84,
    circlePosition: 8,
  });

  const leila = await ensureLifecycleMember({
    email: "leila@demo.wwn.ca",
    username: "leila_demo",
    firstName: "Leila",
    lastName: "Abrahams",
    segment: "ENTREPRENEUR",
    city: "Ottawa",
    province: "ON",
    occupation: "Pop-up Retail Owner",
    creditScore: 74,
    circlePosition: 9,
  });

  await normalizeCircleMembershipOrder(circle7!.id);

  const microLoanPoolWallet = await ensureWallet(
    "LENDING_POOL",
    microLoanPool!.id,
  );
  const reliefPoolWallet = await ensureWallet("LENDING_POOL", reliefPool!.id);

  await db
    .delete(walletTransactionsTable)
    .where(
      inArray(walletTransactionsTable.idempotencyKey, [
        "seed:lending-pool-top-up:micro-loan-pool-q3-2026",
        "seed:lending-pool-top-up:relief-fund",
      ]),
    );

  await upsertWalletTransaction({
    idempotencyKey: "seed:confirmed-commitment-funding:micro-loan-pool-q3-2026",
    type: "TOP_UP",
    toWalletId: microLoanPoolWallet.id,
    amount: "22000.00",
    metadata: {
      seeded: true,
      poolId: microLoanPool!.id,
      source: "seed_confirmed_commitment_funding",
    },
  });

  await upsertWalletTransaction({
    idempotencyKey: "seed:confirmed-commitment-funding:relief-fund",
    type: "TOP_UP",
    toWalletId: reliefPoolWallet.id,
    amount: "500.00",
    metadata: {
      seeded: true,
      poolId: reliefPool!.id,
      source: "seed_confirmed_commitment_funding",
    },
  });

  await upsertLoanApplication({
    borrowerId: amina.id,
    poolId: microLoanPool!.id,
    purpose: "Kitchen equipment expansion",
    status: "SUBMITTED",
    requestedAmount: "4500.00",
    reviewNote: "Submitted and awaiting analyst review.",
    poolAssignedAt: dateAt(2026, 6, 8),
  });

  await upsertLoanApplication({
    borrowerId: naomi.id,
    poolId: microLoanPool!.id,
    purpose: "Inventory purchase bridge loan",
    status: "APPROVED_RESERVED",
    requestedAmount: "6000.00",
    approvedAmount: "5500.00",
    reviewNote: "Approved and reserved against the selected pool.",
    poolAssignedAt: dateAt(2026, 6, 8),
    reservedAt: dateAt(2026, 6, 10),
    reviewedAt: dateAt(2026, 6, 10),
  });

  await upsertLoanApplication({
    borrowerId: chioma.id,
    poolId: microLoanPool!.id,
    purpose: "Salon chair and dryer financing",
    status: "FUNDED",
    requestedAmount: "8000.00",
    approvedAmount: "7500.00",
    disbursedAmount: "7500.00",
    reviewNote: "Funded and repayment schedule issued.",
    poolAssignedAt: dateAt(2026, 5, 10),
    reservedAt: dateAt(2026, 5, 12),
    reviewedAt: dateAt(2026, 5, 12),
    disbursedAt: dateAt(2026, 5, 20),
    repayments: [
      { dueDate: dateAt(2026, 6, 20), amount: "625.00", status: "PENDING" },
      { dueDate: dateAt(2026, 7, 20), amount: "625.00", status: "PENDING" },
      { dueDate: dateAt(2026, 8, 20), amount: "625.00", status: "PENDING" },
    ],
  });

  await upsertLoanApplication({
    borrowerId: zuri.id,
    poolId: microLoanPool!.id,
    purpose: "Commercial freezer and van servicing",
    status: "REPAYING",
    requestedAmount: "9000.00",
    approvedAmount: "8500.00",
    disbursedAmount: "8500.00",
    reviewNote: "Repayment in progress with mixed repayment states.",
    poolAssignedAt: dateAt(2026, 3, 15),
    reservedAt: dateAt(2026, 3, 18),
    reviewedAt: dateAt(2026, 3, 18),
    disbursedAt: dateAt(2026, 3, 25),
    repayments: [
      {
        dueDate: dateAt(2026, 4, 25),
        amount: "708.33",
        paidAmount: "708.33",
        principalPaidAmount: "625.00",
        returnPaidAmount: "83.33",
        creditedToPoolAmount: "708.33",
        paidAt: dateAt(2026, 4, 24),
        status: "PAID",
        method: "EFT",
      },
      {
        dueDate: dateAt(2026, 5, 25),
        amount: "708.33",
        paidAmount: "300.00",
        principalPaidAmount: "250.00",
        returnPaidAmount: "50.00",
        creditedToPoolAmount: "300.00",
        status: "LATE",
        method: "INTERAC",
      },
      {
        dueDate: dateAt(2026, 6, 25),
        amount: "708.34",
        status: "PENDING",
      },
    ],
  });

  await upsertLoanApplication({
    borrowerId: leila.id,
    poolId: reliefPool!.id,
    purpose: "Emergency stall rent support",
    status: "WAITLISTED",
    requestedAmount: "2500.00",
    reviewNote:
      "Pool is underfunded, so the application remains on the waitlist pending top-up or reassignment.",
    poolAssignedAt: dateAt(2026, 6, 11),
  });

  await upsertHelpingHandApplication({
    userId: amina.id,
    purpose: "SUSU cashout stock-up support",
    status: "SUBMITTED",
    requestedAmount: "1200.00",
    reviewNote: "Active submission for review queue.",
  });

  await upsertHelpingHandApplication({
    userId: naomi.id,
    poolId: microLoanPool!.id,
    purpose: "Temporary working capital after circle payout",
    status: "FUNDED",
    requestedAmount: "1000.00",
    approvedAmount: "1000.00",
    disbursedAmount: "1000.00",
    reviewNote: "Funded example with open schedule.",
    reviewedAt: dateAt(2026, 6, 5),
    disbursedAt: dateAt(2026, 6, 7),
    repayments: [
      { dueDate: dateAt(2026, 7, 7), amount: "250.00", status: "PENDING" },
      { dueDate: dateAt(2026, 8, 7), amount: "250.00", status: "PENDING" },
      { dueDate: dateAt(2026, 9, 7), amount: "250.00", status: "PENDING" },
      { dueDate: dateAt(2026, 10, 7), amount: "250.00", status: "PENDING" },
    ],
  });

  await upsertHelpingHandApplication({
    userId: chioma.id,
    poolId: microLoanPool!.id,
    purpose: "Vendor payment bridge after payout cycle",
    status: "REPAYING",
    requestedAmount: "1500.00",
    approvedAmount: "1400.00",
    disbursedAmount: "1400.00",
    reviewNote: "Repaying example with paid and late installments.",
    reviewedAt: dateAt(2026, 4, 10),
    disbursedAt: dateAt(2026, 4, 12),
    repayments: [
      {
        dueDate: dateAt(2026, 5, 12),
        amount: "350.00",
        paidAmount: "350.00",
        paidAt: dateAt(2026, 5, 11),
        status: "PAID",
        method: "WALLET",
      },
      {
        dueDate: dateAt(2026, 6, 12),
        amount: "350.00",
        paidAmount: "175.00",
        status: "LATE",
        method: "WALLET",
      },
      { dueDate: dateAt(2026, 7, 12), amount: "350.00", status: "PENDING" },
      { dueDate: dateAt(2026, 8, 12), amount: "350.00", status: "PENDING" },
    ],
  });

  for (const pool of [microLoanPool, reliefPool]) {
    const [commitmentTotals] = await db
      .select({ total: sum(lendingCommitmentsTable.amount) })
      .from(lendingCommitmentsTable)
      .where(
        and(
          eq(lendingCommitmentsTable.poolId, pool!.id),
          eq(lendingCommitmentsTable.status, "CONFIRMED"),
        ),
      );

    await db
      .update(lendingPoolsTable)
      .set({
        committedAmount: commitmentTotals?.total ?? "0",
        updatedAt: new Date(),
      })
      .where(eq(lendingPoolsTable.id, pool!.id));
  }
  console.log("   ✅  Lifecycle demo data created");

  console.log(`
╔════════════════════════════════════════════════════╗
║           🌸  WWN Database Seeded!  🌸             ║
╠════════════════════════════════════════════════════╣
║  Demo Credentials (password: Demo2026!)            ║
║                                                    ║
║  Individual:   sarah@demo.wwn.ca                   ║
║                username: sarah_demo                ║
║                                                    ║
║  Organization: grace@demo.wwn.ca                   ║
║                username: grace_demo                ║
║                                                    ║
║  Investor:     fatima@demo.wwn.ca                  ║
║                username: fatima_demo               ║
║                                                    ║
║  Extra demos:   amina@demo.wwn.ca                  ║
║                 naomi@demo.wwn.ca                  ║
║                 chioma@demo.wwn.ca                 ║
║                 zuri@demo.wwn.ca                   ║
║                 leila@demo.wwn.ca                  ║
║                                                    ║
║  Admin:        admin@demo.wwn.ca  (Admin2026!)     ║
║                username: admin_demo                ║
╚════════════════════════════════════════════════════╝
`);
}

main()
  .catch((e) => {
    console.error("❌  Seed failed:", e);
    process.exit(1);
  })
  .finally(async () => {
    await pool.end();
  });
