import {
  pgTable,
  serial,
  text,
  boolean,
  timestamp,
  integer,
  pgEnum,
  numeric,
  json,
  index,
  uniqueIndex,
} from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod/v4";

// ═══════════════════════════════════════════════════════════════
// ENUMS
// ═══════════════════════════════════════════════════════════════

export const userRoleEnum = pgEnum("user_role_enum", [
  "MEMBER",
  "ADMIN",
  "SUPER_ADMIN",
]);
export const userSegmentEnum = pgEnum("user_segment_enum", [
  "PROFESSIONAL",
  "ENTREPRENEUR",
  "YOUTH",
  "SCHOOL",
  "INSTITUTION",
]);
export const membershipTypeEnum = pgEnum("membership_type_enum", [
  "INDIVIDUAL",
  "ORGANIZATION",
  "INVESTOR",
]);
export const membershipStatusEnum = pgEnum("membership_status_enum", [
  "PENDING",
  "ACTIVE",
  "SUSPENDED",
  "EXPIRED",
  "CANCELLED",
]);
export const membershipPlanTierEnum = pgEnum("membership_plan_tier_enum", [
  "STARTER",
  "PROFESSIONAL",
  "ELITE",
  "ENTREPRENEUR",
  "YOUTH",
  "COMMUNITY",
  "EDUCATION",
  "GRASSROOT",
  "WORKER",
  "IMPACT",
  "SPONSOR",
]);
export const investorCategoryEnum = pgEnum("investor_category_enum", [
  "WORKER_OWNER",
  "COMMUNITY_SPONSOR",
  "IMPACT_INVESTOR",
]);
export const planBillingModeEnum = pgEnum("plan_billing_mode_enum", [
  "ONE_TIME",
  "RECURRING",
]);
export const planBillingCycleEnum = pgEnum("plan_billing_cycle_enum", [
  "MONTHLY",
  "ANNUAL",
]);
export const contributionFrequencyEnum = pgEnum("contribution_frequency_enum", [
  "WEEKLY",
  "BIWEEKLY",
  "MONTHLY",
]);
export const lendingRepaymentFrequencyEnum = pgEnum(
  "lending_repayment_frequency_enum",
  ["WEEKLY", "BIWEEKLY", "MONTHLY"],
);
export const contributionStatusEnum = pgEnum("contribution_status_enum", [
  "PAID",
  "PENDING",
  "MISSED",
  "WAIVED",
]);
export const paymentMethodEnum = pgEnum("payment_method_enum", [
  "STRIPE",
  "EFT",
  "INTERAC",
  "WIRE",
  "WALLET",
]);
export const paymentStatusEnum = pgEnum("payment_status_enum", [
  "PENDING",
  "COMPLETED",
  "FAILED",
  "REFUNDED",
]);
export const invoiceStatusEnum = pgEnum("invoice_status_enum", [
  "DRAFT",
  "SENT",
  "PAID",
  "OVERDUE",
  "CANCELLED",
]);
export const circleStatusEnum = pgEnum("circle_status_enum", [
  "FORMING",
  "UPCOMING",
  "ACTIVE",
  "COMPLETED",
  "CANCELLED",
]);
export const payoutStatusEnum = pgEnum("payout_status_enum", [
  "PENDING",
  "PAID",
  "SKIPPED",
]);
export const walletOwnerTypeEnum = pgEnum("wallet_owner_type_enum", [
  "USER",
  "ORGANIZATION",
  "SUSU",
  "INVESTMENT",
  "CIRCLE",
  "LENDING_POOL",
  "LENDING_MASTER",
  "PLATFORM",
  "ASCA",
]);
export const walletTransactionTypeEnum = pgEnum(
  "wallet_transaction_type_enum",
  [
    "TOP_UP",
    "CASH_OUT",
    "REFUND",
    "CONTRIBUTION",
    "PAYOUT",
    "ADJUSTMENT",
    "INVESTMENT_PURCHASE",
    "INVESTMENT_PRINCIPAL_PAYOUT",
    "INVESTMENT_RETURN_PAYOUT",
    "LENDING_PRINCIPAL_PAYOUT",
    "LENDING_RETURN_PAYOUT",
    "LOAN_DISBURSEMENT",
    "LOAN_REPAYMENT",
    "HELPING_HAND_DISBURSEMENT",
    "HELPING_HAND_REPAYMENT",
    "ASCA_CONTRIBUTION",
    "ASCA_REFUND",
    "ASCA_INVESTMENT",
    "ASCA_OFFLINE_WITHDRAWAL",
  ],
);
export const paymentProviderEnum = pgEnum("payment_provider_enum", [
  "STRIPE",
  "INTERAC",
  "MANUAL",
]);
export const walletPaymentIntentTypeEnum = pgEnum(
  "wallet_payment_intent_type_enum",
  ["TOP_UP", "CASH_OUT", "REFUND"],
);
export const walletPaymentIntentStatusEnum = pgEnum(
  "wallet_payment_intent_status_enum",
  ["CREATED", "PENDING", "SUCCEEDED", "FAILED", "CANCELLED"],
);
export const webhookEventStatusEnum = pgEnum("webhook_event_status_enum", [
  "PENDING",
  "PROCESSED",
  "FAILED",
]);
export const helpingHandStatusEnum = pgEnum("helping_hand_status_enum", [
  "DRAFT",
  "SUBMITTED",
  "UNDER_REVIEW",
  "APPROVED",
  "REJECTED",
  "FUNDED",
  "REPAYING",
  "CLOSED",
]);
export const loanApplicationStatusEnum = pgEnum(
  "loan_application_status_enum",
  [
    "DRAFT",
    "SUBMITTED",
    "WAITLISTED",
    "UNDER_REVIEW",
    "APPROVED",
    "APPROVED_RESERVED",
    "REJECTED",
    "CANCELLED",
    "FUNDED",
    "REPAYING",
    "CLOSED",
  ],
);
export const repaymentStatusEnum = pgEnum("repayment_status_enum", [
  "PENDING",
  "PAID",
  "LATE",
  "WAIVED",
]);
export const investmentCategoryEnum = pgEnum("investment_category_enum", [
  "REAL_ESTATE",
  "AGRICULTURE",
  "SMALL_BUSINESS",
  "COMMUNITY_DEVELOPMENT",
  "BONDS",
  "OTHER",
]);
export const investmentOpportunityStatusEnum = pgEnum(
  "investment_opportunity_status_enum",
  ["DRAFT", "OPEN", "FUNDED", "CLOSED"],
);
export const investmentInterestStatusEnum = pgEnum(
  "investment_interest_status_enum",
  ["PENDING", "CONFIRMED", "CANCELLED"],
);
export const investmentIntentReservationStatusEnum = pgEnum(
  "investment_intent_reservation_status_enum",
  ["RESERVED", "FINALIZED", "RELEASED"],
);
export const riskLevelEnum = pgEnum("risk_level_enum", [
  "LOW",
  "MEDIUM",
  "HIGH",
]);
export const lendingPoolStatusEnum = pgEnum("lending_pool_status_enum", [
  "DRAFT",
  "OPEN",
  "PAUSED",
  "CLOSED",
]);
export const commitmentStatusEnum = pgEnum("commitment_status_enum", [
  "PENDING",
  "CONFIRMED",
  "CANCELLED",
]);
export const lendingPoolReturnModelEnum = pgEnum(
  "lending_pool_return_model_enum",
  ["FIXED_SIMPLE"],
);
export const lendingMinAppreciationTypeEnum = pgEnum(
  "lending_min_appreciation_type_enum",
  ["NONE", "FIXED", "PCT_OF_PRINCIPAL", "TERM_TIERED"],
);
export const lendingLoanTypeEnum = pgEnum("lending_loan_type_enum", [
  "COMMUNITY_LENDING",
  "P2P_LENDING",
]);

export const kycStatusEnum = pgEnum("kyc_status_enum", [
  "PENDING",
  "APPROVED",
  "REJECTED",
]);
export const eventTypeEnum = pgEnum("event_type_enum", [
  "VIRTUAL",
  "IN_PERSON",
  "HYBRID",
]);
export const rsvpStatusEnum = pgEnum("rsvp_status_enum", [
  "GOING",
  "WAITLISTED",
  "CANCELLED",
]);
export const notificationCategoryEnum = pgEnum("notification_category_enum", [
  "SYSTEM",
  "MEMBERSHIP",
  "SAVINGS",
  "LENDING",
  "INVESTMENT",
  "ACADEMY",
  "PAYMENT",
  "COMMUNITY",
]);
export const notificationSeverityEnum = pgEnum("notification_severity_enum", [
  "INFO",
  "SUCCESS",
  "WARNING",
  "ERROR",
]);
export const pushPlatformEnum = pgEnum("push_platform_enum", ["EXPO"]);
export const notificationChannelEnum = pgEnum("notification_channel_enum", [
  "PUSH",
  "EMAIL",
]);
export const notificationDeliveryStatusEnum = pgEnum(
  "notification_delivery_status_enum",
  ["SENT", "FAILED", "SKIPPED"],
);
export const resourceFileTypeEnum = pgEnum("resource_file_type_enum", [
  "PDF",
  "VIDEO",
  "DOCUMENT",
  "SPREADSHEET",
  "IMAGE",
  "LINK",
  "OTHER",
]);

export const resourceKindEnum = pgEnum("resource_kind_enum", [
  "FILE",
  "ARTICLE",
]);
export const orgMemberRoleEnum = pgEnum("org_member_role_enum", [
  "OWNER",
  "ADMIN",
  "MEMBER",
]);
export const organizationMemberStatusEnum = pgEnum(
  "organization_member_status_enum",
  ["PENDING", "ACTIVE"],
);
export const intakeStatusEnum = pgEnum("intake_status_enum", [
  "PENDING",
  "IN_REVIEW",
  "QUOTED",
  "ACCEPTED",
  "COMPLETED",
  "CANCELLED",
]);
export const organizationTypeEnum = pgEnum("organization_type_enum", [
  "SCHOOL",
  "GRASSROOTS_ROSCA",
  "NFP_ALUMNI_FAITH",
]);
export const organizationOperationalStatusEnum = pgEnum(
  "organization_operational_status_enum",
  ["PENDING", "ACTIVE", "SUSPENDED", "ARCHIVED"],
);
export const organizationInvitationStatusEnum = pgEnum(
  "organization_invitation_status_enum",
  ["PENDING", "ACCEPTED", "CANCELLED", "EXPIRED"],
);
export const organizationMemberObligationStatusEnum = pgEnum(
  "organization_member_obligation_status_enum",
  ["PENDING", "PAID", "CANCELLED"],
);
export const organizationMemberObligationKindEnum = pgEnum(
  "organization_member_obligation_kind_enum",
  ["TREASURY", "ORG_SUSU"],
);

export const onboardingProgramStatusEnum = pgEnum(
  "onboarding_program_status_enum",
  ["DRAFT", "PUBLISHED", "ARCHIVED"],
);
export const onboardingProgramDurationUnitEnum = pgEnum(
  "onboarding_program_duration_unit_enum",
  ["WEEK", "DAY"],
);
export const onboardingActivityTypeEnum = pgEnum(
  "onboarding_activity_type_enum",
  [
    "VIDEO",
    "FILE",
    "RICH_TEXT",
    "EXTERNAL_LINK",
    "QUIZ",
    "CHECKLIST",
    "SURVEY",
  ],
);
export const onboardingActivityStatusEnum = pgEnum(
  "onboarding_activity_status_enum",
  ["NOT_STARTED", "IN_PROGRESS", "COMPLETED"],
);

export const ascaTypeEnum = pgEnum("asca_type_enum", ["GENERAL", "INVESTMENT"]);
export const ascaStatusEnum = pgEnum("asca_status_enum", [
  "DRAFT",
  "FUNDING",
  "READY",
  "PENDING_INVESTMENT",
  "INVESTED",
  "MATURED",
  "CLOSED",
  "CANCELLED",
]);
export const ascaMemberStatusEnum = pgEnum("asca_member_status_enum", [
  "ACTIVE",
  "DEFAULTED",
  "WITHDRAWN",
]);
export const ascaContributionStatusEnum = pgEnum(
  "asca_contribution_status_enum",
  ["PENDING", "PAID", "LATE", "REFUNDED"],
);

// ═══════════════════════════════════════════════════════════════
// ORGANIZATION
// ═══════════════════════════════════════════════════════════════

export const organizationsTable = pgTable("organizations", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  type: text("type").notNull(),
  organizationType: organizationTypeEnum("organization_type"),
  operationalStatus: organizationOperationalStatusEnum("operational_status")
    .notNull()
    .default("PENDING"),
  description: text("description"),
  website: text("website"),
  contactName: text("contact_name"),
  contactTitle: text("contact_title"),
  businessType: text("business_type"),
  yearsInBusiness: text("years_in_business"),
  annualRevenue: text("annual_revenue"),
  province: text("province"),
  city: text("city"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

// ═══════════════════════════════════════════════════════════════
// USER & AUTH
// ═══════════════════════════════════════════════════════════════

export const usersTable = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    email: text("email").notNull().unique(),
    username: text("username").unique(),
    phone: text("phone"),
    passwordHash: text("password_hash").notNull(),
    firstName: text("first_name").notNull(),
    lastName: text("last_name"),
    segment: userSegmentEnum("segment"),
    membershipType: membershipTypeEnum("membership_type"),
    membershipStatus: membershipStatusEnum("membership_status")
      .notNull()
      .default("PENDING"),
    role: text("role").default("member"),
    userRole: userRoleEnum("user_role").notNull().default("MEMBER"),
    isEmailVerified: boolean("is_email_verified").notNull().default(false),
    bio: text("bio"),
    avatarUrl: text("avatar_url"),
    city: text("city"),
    province: text("province"),
    contactAddress: text("contact_address"),
    contactStreet: text("contact_street"),
    contactPostalCode: text("contact_postal_code"),
    contactCountryIso2: text("contact_country_iso2"),
    ageRange: text("age_range"),
    occupation: text("occupation"),
    employmentEmployed: boolean("employment_employed"),
    employmentStatus: text("employment_status"),
    employerName: text("employer_name"),
    employerAddress: text("employer_address"),
    employmentPosition: text("employment_position"),
    businessName: text("business_name"),
    businessType: text("business_type"),
    yearsInBusiness: text("years_in_business"),
    annualRevenue: text("annual_revenue"),
    howHeard: text("how_heard"),
    referrerName: text("referrer_name"),
    referrerPhone: text("referrer_phone"),
    referrerWwnId: text("referrer_wwn_id"),
    referrerAddress: text("referrer_address"),
    referrerStreet: text("referrer_street"),
    referrerCity: text("referrer_city"),
    referrerPostalCode: text("referrer_postal_code"),
    goals: text("goals"),
    consentAccepted: boolean("consent_accepted").notNull().default(false),
    consentAcceptedAt: timestamp("consent_accepted_at"),
    nextOfKinFirstName: text("next_of_kin_first_name"),
    nextOfKinLastName: text("next_of_kin_last_name"),
    nextOfKinOtherNames: text("next_of_kin_other_names"),
    nextOfKinGender: text("next_of_kin_gender"),
    nextOfKinResidentialAddress: text("next_of_kin_residential_address"),
    nextOfKinStreet: text("next_of_kin_street"),
    nextOfKinCity: text("next_of_kin_city"),
    nextOfKinPostalCode: text("next_of_kin_postal_code"),
    nextOfKinEmail: text("next_of_kin_email"),
    nextOfKinPhone: text("next_of_kin_phone"),
    nextOfKinRelationship: text("next_of_kin_relationship"),
    nextOfKinOccupation: text("next_of_kin_occupation"),
    kycIdFrontUrl: text("kyc_id_front_url"),
    kycIdBackUrl: text("kyc_id_back_url"),
    kycSelfieUrl: text("kyc_selfie_url"),
    kycStatus: kycStatusEnum("kyc_status"),
    kycSubmittedAt: timestamp("kyc_submitted_at"),
    kycReviewedAt: timestamp("kyc_reviewed_at"),
    kycReviewedByUserId: integer("kyc_reviewed_by_user_id"),
    kycRejectionReason: text("kyc_rejection_reason"),
    investorCategory: investorCategoryEnum("investor_category"),
    organizationId: integer("organization_id").references(
      () => organizationsTable.id,
    ),
    onboardingCompletedAt: timestamp("onboarding_completed_at"),
    lastLoginAt: timestamp("last_login_at"),
    authTokenVersion: integer("auth_token_version").notNull().default(0),
    membershipRegistrationId: text("membership_registration_id"),
    deletedAt: timestamp("deleted_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("users_membership_created_idx").on(
      t.membershipStatus,
      t.membershipType,
      t.createdAt,
    ),
    uniqueIndex("users_membership_registration_id_uidx").on(
      t.membershipRegistrationId,
    ),
  ],
);

export const verificationTokensTable = pgTable("verification_tokens", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => usersTable.id, { onDelete: "cascade" }),
  codeHash: text("code_hash").notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  attempts: integer("attempts").notNull().default(0),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const passwordResetTokensTable = pgTable("password_reset_tokens", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .notNull()
    .references(() => usersTable.id, { onDelete: "cascade" }),
  tokenHash: text("token_hash").notNull(),
  expiresAt: timestamp("expires_at").notNull(),
  attempts: integer("attempts").notNull().default(0),
  isExchangeToken: boolean("is_exchange_token").notNull().default(false),
  used: boolean("used").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const organizationMembersTable = pgTable(
  "organization_members",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    role: orgMemberRoleEnum("role").notNull().default("MEMBER"),
    status: organizationMemberStatusEnum("status").notNull().default("ACTIVE"),
    joinedAt: timestamp("joined_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("org_members_org_user_uidx").on(t.organizationId, t.userId),
  ],
);

export const organizationInvitationsTable = pgTable(
  "organization_invitations",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    email: text("email").notNull(),
    firstName: text("first_name"),
    lastName: text("last_name"),
    role: orgMemberRoleEnum("role").notNull().default("MEMBER"),
    invitedByUserId: integer("invited_by_user_id").references(
      () => usersTable.id,
      {
        onDelete: "set null",
      },
    ),
    tokenHash: text("token_hash").notNull().unique(),
    status: organizationInvitationStatusEnum("status")
      .notNull()
      .default("PENDING"),
    expiresAt: timestamp("expires_at").notNull(),
    acceptedAt: timestamp("accepted_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("org_invites_org_status_created_idx").on(
      t.organizationId,
      t.status,
      t.createdAt,
    ),
    uniqueIndex("org_invites_org_email_pending_uidx")
      .on(t.organizationId, t.email)
      .where(sql`${t.status} = 'PENDING'`),
  ],
);

export const organizationIntakesTable = pgTable(
  "organization_intakes",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    programInterests: text("program_interests"),
    annualBudget: text("annual_budget"),
    staffCount: text("staff_count"),
    goals: text("goals"),
    additionalNotes: text("additional_notes"),
    status: intakeStatusEnum("status").notNull().default("PENDING"),
    quoteAmount: numeric("quote_amount", { precision: 12, scale: 2 }),
    quoteNote: text("quote_note"),
    quoteLink: text("quote_link"),
    reviewedByAdminId: integer("reviewed_by_admin_id"),
    reviewedAt: timestamp("reviewed_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("org_intakes_org_created_idx").on(t.organizationId, t.createdAt),
    index("org_intakes_org_updated_idx").on(t.organizationId, t.updatedAt),
  ],
);

export const organizationMemberObligationsTable = pgTable(
  "organization_member_obligations",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    memberUserId: integer("member_user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    assignedByUserId: integer("assigned_by_user_id").references(
      () => usersTable.id,
      {
        onDelete: "set null",
      },
    ),
    kind: organizationMemberObligationKindEnum("kind")
      .notNull()
      .default("TREASURY"),
    title: text("title").notNull(),
    description: text("description"),
    amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
    currency: text("currency").notNull().default("CAD"),
    status: organizationMemberObligationStatusEnum("status")
      .notNull()
      .default("PENDING"),
    dueDate: timestamp("due_date"),
    paidAt: timestamp("paid_at"),
    walletTransactionId: integer("wallet_transaction_id"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("org_member_obligation_org_member_status_idx").on(
      t.organizationId,
      t.memberUserId,
      t.status,
    ),
    index("org_member_obligation_org_created_idx").on(
      t.organizationId,
      t.createdAt,
    ),
  ],
);

export const organizationBankSettingsTable = pgTable(
  "organization_bank_settings",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .unique()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    eftAccountName: text("eft_account_name").notNull(),
    eftInstitutionNumber: text("eft_institution_number").notNull(),
    eftTransitNumber: text("eft_transit_number").notNull(),
    eftAccountNumber: text("eft_account_number").notNull(),
    interacRecipientName: text("interac_recipient_name").notNull(),
    interacEmail: text("interac_email").notNull(),
    cashoutMinAmount: numeric("cashout_min_amount", {
      precision: 12,
      scale: 2,
    }).notNull(),
    cashoutFeePct: numeric("cashout_fee_pct", {
      precision: 6,
      scale: 3,
    }).notNull(),
    cashoutFeeFixed: numeric("cashout_fee_fixed", {
      precision: 12,
      scale: 2,
    }).notNull(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("org_bank_settings_org_updated_idx").on(
      t.organizationId,
      t.updatedAt,
    ),
  ],
);

export const organizationMembershipIdSettingsTable = pgTable(
  "organization_membership_id_settings",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .unique()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    prefix: text("prefix").notNull().default("WWN"),
    digits: integer("digits").notNull().default(7),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("org_membership_id_settings_org_updated_idx").on(
      t.organizationId,
      t.updatedAt,
    ),
  ],
);

// ═══════════════════════════════════════════════════════════════
// MEMBERSHIP PLANS & SUBSCRIPTIONS
// ═══════════════════════════════════════════════════════════════

export const membershipPlansTable = pgTable("membership_plans", {
  id: serial("id").primaryKey(),
  membershipType: membershipTypeEnum("membership_type").notNull(),
  organizationType: organizationTypeEnum("organization_type"),
  tier: membershipPlanTierEnum("tier").notNull(),
  name: text("name").notNull(),
  description: text("description"),
  priceMonthly: numeric("price_monthly", {
    precision: 10,
    scale: 2,
  }).notNull(),
  priceAnnual: numeric("price_annual", { precision: 10, scale: 2 }).notNull(),
  currency: text("currency").notNull().default("CAD"),
  features: json("features").notNull().default([]),
  entitlements: json("entitlements")
    .$type<Record<string, unknown>>()
    .notNull()
    .default({}),
  isActive: boolean("is_active").notNull().default(true),
  sortOrder: integer("sort_order").notNull().default(0),
  billingMode: planBillingModeEnum("billing_mode")
    .notNull()
    .default("RECURRING"),
  defaultBillingCycle: planBillingCycleEnum("default_billing_cycle")
    .notNull()
    .default("ANNUAL"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const membershipSubscriptionsTable = pgTable(
  "membership_subscriptions",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    planId: integer("plan_id")
      .notNull()
      .references(() => membershipPlansTable.id),
    status: membershipStatusEnum("status").notNull().default("PENDING"),
    startDate: timestamp("start_date"),
    endDate: timestamp("end_date"),
    renewsAt: timestamp("renews_at"),
    cancelledAt: timestamp("cancelled_at"),
    stripeCustomerId: text("stripe_customer_id"),
    stripeSubscriptionId: text("stripe_subscription_id"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("member_sub_user_status_created_idx").on(
      t.userId,
      t.status,
      t.createdAt,
    ),
    uniqueIndex("member_sub_user_active_uidx")
      .on(t.userId)
      .where(sql`${t.status} in ('PENDING', 'ACTIVE')`),
  ],
);

export const organizationSubscriptionsTable = pgTable(
  "organization_subscriptions",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    planId: integer("plan_id")
      .notNull()
      .references(() => membershipPlansTable.id),
    status: membershipStatusEnum("status").notNull().default("PENDING"),
    startDate: timestamp("start_date"),
    endDate: timestamp("end_date"),
    renewsAt: timestamp("renews_at"),
    cancelledAt: timestamp("cancelled_at"),
    stripeCustomerId: text("stripe_customer_id"),
    stripeSubscriptionId: text("stripe_subscription_id"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("org_sub_org_status_created_idx").on(
      t.organizationId,
      t.status,
      t.createdAt,
    ),
    uniqueIndex("org_sub_org_active_uidx")
      .on(t.organizationId)
      .where(sql`${t.status} in ('PENDING', 'ACTIVE')`),
  ],
);

// ═══════════════════════════════════════════════════════════════
// PAYMENTS & INVOICES
// ═══════════════════════════════════════════════════════════════

export const invoicesTable = pgTable(
  "invoices",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    subscriptionId: integer("subscription_id").references(
      () => membershipSubscriptionsTable.id,
    ),
    invoiceNumber: text("invoice_number").notNull().unique(),
    amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
    currency: text("currency").notNull().default("CAD"),
    status: invoiceStatusEnum("status").notNull().default("DRAFT"),
    dueDate: timestamp("due_date").notNull(),
    paidAt: timestamp("paid_at"),
    pdfUrl: text("pdf_url"),
    lineItems: json("line_items").notNull().default([]),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("invoices_user_created_idx").on(t.userId, t.createdAt),
    index("invoices_sub_created_idx").on(t.subscriptionId, t.createdAt),
  ],
);

export const paymentsTable = pgTable(
  "payments",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
    currency: text("currency").notNull().default("CAD"),
    method: paymentMethodEnum("method").notNull(),
    status: paymentStatusEnum("status").notNull().default("PENDING"),
    reference: text("reference"),
    description: text("description"),
    metadata: json("metadata"),
    invoiceId: integer("invoice_id").references(() => invoicesTable.id),
    processedAt: timestamp("processed_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("payments_invoice_created_idx").on(t.invoiceId, t.createdAt),
    index("payments_user_created_idx").on(t.userId, t.createdAt),
    index("payments_status_created_idx").on(t.status, t.createdAt),
  ],
);

// ═══════════════════════════════════════════════════════════════
// WWN ACADEMY DIRECTORY
// ═══════════════════════════════════════════════════════════════

export const academyCategoriesTable = pgTable("academy_categories", {
  id: serial("id").primaryKey(),
  name: text("name").notNull().unique(),
  slug: text("slug").notNull().unique(),
  sortOrder: integer("sort_order").notNull().default(0),
});

export const academyCoursesTable = pgTable("academy_courses", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").notNull().unique(),
  description: text("description").notNull(),
  thumbnailUrl: text("thumbnail_url"),
  categoryId: integer("category_id")
    .notNull()
    .references(() => academyCategoriesTable.id),
  instructor: text("instructor").notNull(),
  duration: text("duration").notNull(),
  level: text("level").notNull(),
  rating: numeric("rating", { precision: 3, scale: 1 }),
  enrolledCount: integer("enrolled_count").notNull().default(0),
  externalUrl: text("external_url").notNull(),
  tags: json("tags").notNull().default([]),
  isPublished: boolean("is_published").notNull().default(true),
  sortOrder: integer("sort_order").notNull().default(0),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const courseInterestsTable = pgTable(
  "course_interests",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    courseId: integer("course_id")
      .notNull()
      .references(() => academyCoursesTable.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("course_interests_user_course_uidx").on(t.userId, t.courseId),
  ],
);

// ═══════════════════════════════════════════════════════════════
// SUSU SAVINGS CIRCLES
// ═══════════════════════════════════════════════════════════════

export const savingsCirclesTable = pgTable(
  "savings_circles",
  {
    id: serial("id").primaryKey(),
    name: text("name").notNull(),
    description: text("description"),
    termsHtml: text("terms_html"),
    contributionAmount: numeric("contribution_amount", {
      precision: 10,
      scale: 2,
    }).notNull(),
    frequency: contributionFrequencyEnum("frequency")
      .notNull()
      .default("MONTHLY"),
    helpingHandMultiplier: numeric("helping_hand_multiplier", {
      precision: 6,
      scale: 2,
    }),
    orgAutoDeductEnabled: boolean("org_auto_deduct_enabled")
      .notNull()
      .default(false),
    isFreeToJoin: boolean("is_free_to_join").notNull().default(false),
    maxMembers: integer("max_members").notNull(),
    formingTimeoutDays: integer("forming_timeout_days").notNull().default(14),
    status: circleStatusEnum("status").notNull().default("UPCOMING"),
    startDate: timestamp("start_date"),
    endDate: timestamp("end_date"),
    organizationId: integer("organization_id").references(
      () => organizationsTable.id,
      { onDelete: "cascade" },
    ),
    createdByAdminId: integer("created_by_admin_id"),
    createdByOrganizationMemberUserId: integer(
      "created_by_organization_member_user_id",
    ).references(() => usersTable.id, { onDelete: "set null" }),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("savings_circles_org_idx").on(t.organizationId, t.createdAt),
    index("savings_circles_org_status_idx").on(t.organizationId, t.status),
  ],
);

export const circleMembershipsTable = pgTable(
  "circle_memberships",
  {
    id: serial("id").primaryKey(),
    circleId: integer("circle_id")
      .notNull()
      .references(() => savingsCirclesTable.id, { onDelete: "cascade" }),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    position: integer("position").notNull(),
    memberAutoDeductEnabled: boolean("member_auto_deduct_enabled")
      .notNull()
      .default(false),
    joinedAt: timestamp("joined_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("circle_memberships_circle_user_uidx").on(t.circleId, t.userId),
    uniqueIndex("circle_memberships_circle_pos_uidx").on(
      t.circleId,
      t.position,
    ),
    index("circle_memberships_user_idx").on(t.userId),
  ],
);

export const savingsContributionsTable = pgTable(
  "savings_contributions",
  {
    id: serial("id").primaryKey(),
    circleId: integer("circle_id")
      .notNull()
      .references(() => savingsCirclesTable.id),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    dueDate: timestamp("due_date").notNull(),
    paidAt: timestamp("paid_at"),
    amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
    status: contributionStatusEnum("status").notNull().default("PENDING"),
    method: paymentMethodEnum("method"),
    receiptRef: text("receipt_ref"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("savings_contributions_circle_user_due_uidx").on(
      t.circleId,
      t.userId,
      t.dueDate,
    ),
    index("savings_contrib_circle_status_due_idx").on(
      t.circleId,
      t.status,
      t.dueDate,
    ),
    index("savings_contrib_user_due_idx").on(t.userId, t.dueDate),
  ],
);

export const circlePayoutsTable = pgTable(
  "circle_payouts",
  {
    id: serial("id").primaryKey(),
    circleId: integer("circle_id")
      .notNull()
      .references(() => savingsCirclesTable.id),
    userId: integer("user_id").notNull(),
    position: integer("position").notNull(),
    scheduledDate: timestamp("scheduled_date").notNull(),
    paidDate: timestamp("paid_date"),
    amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
    status: payoutStatusEnum("status").notNull().default("PENDING"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("circle_payouts_circle_user_pos_uidx").on(
      t.circleId,
      t.userId,
      t.position,
    ),
    index("circle_payouts_circle_status_sched_idx").on(
      t.circleId,
      t.status,
      t.scheduledDate,
    ),
  ],
);

export const walletsTable = pgTable(
  "wallets",
  {
    id: serial("id").primaryKey(),
    ownerType: walletOwnerTypeEnum("owner_type").notNull(),
    ownerId: integer("owner_id").notNull(),
    currency: text("currency").notNull().default("CAD"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [uniqueIndex("wallets_owner_uidx").on(t.ownerType, t.ownerId)],
);

export const walletTransactionsTable = pgTable(
  "wallet_transactions",
  {
    id: serial("id").primaryKey(),
    type: walletTransactionTypeEnum("type").notNull(),
    idempotencyKey: text("idempotency_key"),
    fromWalletId: integer("from_wallet_id").references(() => walletsTable.id),
    toWalletId: integer("to_wallet_id").references(() => walletsTable.id),
    amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
    metadata: json("metadata").notNull().default({}),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("wallet_transactions_idempotency_uidx").on(t.idempotencyKey),
    index("wallet_txs_from_created_idx").on(t.fromWalletId, t.createdAt),
    index("wallet_txs_to_created_idx").on(t.toWalletId, t.createdAt),
    index("wallet_txs_type_wallets_idx").on(
      t.type,
      t.fromWalletId,
      t.toWalletId,
    ),
  ],
);

export const walletPaymentIntentsTable = pgTable(
  "wallet_payment_intents",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    walletId: integer("wallet_id")
      .notNull()
      .references(() => walletsTable.id, { onDelete: "cascade" }),
    provider: paymentProviderEnum("provider").notNull().default("MANUAL"),
    type: walletPaymentIntentTypeEnum("type").notNull(),
    amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
    currency: text("currency").notNull().default("CAD"),
    status: walletPaymentIntentStatusEnum("status")
      .notNull()
      .default("CREATED"),
    providerReference: text("provider_reference"),
    metadata: json("metadata").notNull().default({}),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("wallet_payment_intents_provider_ref_uidx").on(
      t.provider,
      t.providerReference,
    ),
    index("wallet_intents_user_created_idx").on(t.userId, t.createdAt),
    index("wallet_intents_wallet_status_idx").on(
      t.walletId,
      t.status,
      t.createdAt,
    ),
  ],
);

export const bankSetupTable = pgTable("bank_setup", {
  id: serial("id").primaryKey(),
  eftAccountName: text("eft_account_name").notNull(),
  eftInstitutionNumber: text("eft_institution_number").notNull(),
  eftTransitNumber: text("eft_transit_number").notNull(),
  eftAccountNumber: text("eft_account_number").notNull(),
  interacRecipientName: text("interac_recipient_name").notNull(),
  interacEmail: text("interac_email").notNull(),
  cashoutMinAmount: numeric("cashout_min_amount", {
    precision: 12,
    scale: 2,
  }).notNull(),
  cashoutFeePct: numeric("cashout_fee_pct", {
    precision: 6,
    scale: 3,
  }).notNull(),
  cashoutFeeFixed: numeric("cashout_fee_fixed", {
    precision: 12,
    scale: 2,
  }).notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const membershipIdSettingsTable = pgTable("membership_id_settings", {
  id: integer("id").primaryKey(),
  prefix: text("prefix").notNull().default("WWN"),
  digits: integer("digits").notNull().default(7),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const paymentWebhookEventsTable = pgTable(
  "payment_webhook_events",
  {
    id: serial("id").primaryKey(),
    provider: paymentProviderEnum("provider").notNull(),
    eventId: text("event_id").notNull(),
    eventType: text("event_type").notNull(),
    payload: json("payload").notNull(),
    status: webhookEventStatusEnum("status").notNull().default("PENDING"),
    error: text("error"),
    attempts: integer("attempts").notNull().default(0),
    receivedAt: timestamp("received_at").notNull().defaultNow(),
    processedAt: timestamp("processed_at"),
  },
  (t) => [
    uniqueIndex("payment_webhook_events_provider_event_uidx").on(
      t.provider,
      t.eventId,
    ),
    index("webhook_events_status_received_idx").on(t.status, t.receivedAt),
  ],
);

// ═══════════════════════════════════════════════════════════════
// WWN CREDIT SCORE ENGINE
// ═══════════════════════════════════════════════════════════════

export const wwnCreditScoresTable = pgTable("wwn_credit_scores", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .notNull()
    .unique()
    .references(() => usersTable.id, { onDelete: "cascade" }),
  score: integer("score").notNull(),
  computedAt: timestamp("computed_at").notNull().defaultNow(),
});

export const creditScoreFactorsTable = pgTable("credit_score_factors", {
  id: serial("id").primaryKey(),
  creditScoreId: integer("credit_score_id")
    .notNull()
    .references(() => wwnCreditScoresTable.id, { onDelete: "cascade" }),
  factorName: text("factor_name").notNull(),
  score: integer("score").notNull(),
  weight: text("weight").notNull(),
});

// ═══════════════════════════════════════════════════════════════
// HELPING HAND
// ═══════════════════════════════════════════════════════════════

export const helpingHandApplicationsTable = pgTable(
  "helping_hand_applications",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    requestedAmount: numeric("requested_amount", {
      precision: 10,
      scale: 2,
    }).notNull(),
    purpose: text("purpose").notNull(),
    term: text("term").notNull(),
    repaymentSchedule: text("repayment_schedule").notNull(),
    notes: text("notes"),
    status: helpingHandStatusEnum("status").notNull().default("DRAFT"),
    reviewedByAdminId: integer("reviewed_by_admin_id"),
    reviewedAt: timestamp("reviewed_at"),
    reviewNote: text("review_note"),
    qualifyingCircleId: integer("qualifying_circle_id").references(
      () => savingsCirclesTable.id,
    ),
    qualifyingPayoutId: integer("qualifying_payout_id").references(
      () => circlePayoutsTable.id,
    ),
    qualifyingPayoutAmount: numeric("qualifying_payout_amount", {
      precision: 12,
      scale: 2,
    }),
    qualifyingMultiplier: numeric("qualifying_multiplier", {
      precision: 6,
      scale: 2,
    }),
    approvedAmount: numeric("approved_amount", { precision: 10, scale: 2 }),
    disbursedAmount: numeric("disbursed_amount", { precision: 10, scale: 2 }),
    disbursementTransactionId: integer(
      "disbursement_transaction_id",
    ).references(() => walletTransactionsTable.id),
    disbursedAt: timestamp("disbursed_at"),
    organizationId: integer("organization_id").references(() => organizationsTable.id),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("hh_apps_user_created_idx").on(t.userId, t.createdAt),
    index("hh_apps_status_created_idx").on(t.status, t.createdAt),
    index("hh_apps_org_created_idx").on(t.organizationId, t.createdAt),
  ],
);

export const helpingHandRepaymentsTable = pgTable(
  "helping_hand_repayments",
  {
    id: serial("id").primaryKey(),
    helpingHandApplicationId: integer("helping_hand_application_id")
      .notNull()
      .references(() => helpingHandApplicationsTable.id),
    dueDate: timestamp("due_date").notNull(),
    amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
    paidAmount: numeric("paid_amount", { precision: 10, scale: 2 }),
    paidAt: timestamp("paid_at"),
    status: repaymentStatusEnum("status").notNull().default("PENDING"),
    method: paymentMethodEnum("method"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
    organizationId: integer("organization_id").references(() => organizationsTable.id),
  },
  (t) => [
    index("hh_repayments_app_due_idx").on(
      t.helpingHandApplicationId,
      t.dueDate,
    ),
    index("hh_repayments_status_due_idx").on(t.status, t.dueDate),
    index("hh_repayments_org_due_idx").on(t.organizationId, t.status, t.dueDate),
  ],
);

// ═══════════════════════════════════════════════════════════════
// SUSUNOMICS — COLLECTIVE INVESTMENTS
// ═══════════════════════════════════════════════════════════════

export const investmentOpportunitiesTable = pgTable(
  "investment_opportunities",
  {
    id: serial("id").primaryKey(),
    title: text("title").notNull(),
    slug: text("slug").notNull().unique(),
    description: text("description").notNull(),
    termsHtml: text("terms_html"),
    category: investmentCategoryEnum("category").notNull(),
    expectedReturnPct: integer("expected_return_pct").notNull(),
    minInvestment: numeric("min_investment", {
      precision: 10,
      scale: 2,
    }).notNull(),
    targetAmount: numeric("target_amount", {
      precision: 12,
      scale: 2,
    }).notNull(),
    raisedAmount: numeric("raised_amount", { precision: 12, scale: 2 })
      .notNull()
      .default("0"),
    deadline: timestamp("deadline").notNull(),
    riskLevel: riskLevelEnum("risk_level").notNull().default("MEDIUM"),
    status: investmentOpportunityStatusEnum("status")
      .notNull()
      .default("DRAFT"),
    featured: boolean("featured").notNull().default(false),
    fundingModel: text("funding_model")
      .$type<"CROWD" | "NON_CROWD">()
      .notNull()
      .default("CROWD"),
    eligibleInvestorTiers: json("eligible_investor_tiers")
      .$type<string[]>()
      .notNull()
      .default([]),
    organizationId: integer("organization_id").references(
      () => organizationsTable.id,
    ),
    videoUrl: text("video_url"),
    academyLink: text("academy_link"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("invest_opps_status_featured_deadline_idx").on(
      t.status,
      t.featured,
      t.deadline,
    ),
  ],
);

export const investmentInterestsTable = pgTable(
  "investment_interests",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    opportunityId: integer("opportunity_id")
      .notNull()
      .references(() => investmentOpportunitiesTable.id),
    amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
    notes: text("notes"),
    status: investmentInterestStatusEnum("status").notNull().default("PENDING"),
    confirmedAt: timestamp("confirmed_at"),
    cancelledAt: timestamp("cancelled_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("investment_interests_user_opp_uidx").on(
      t.userId,
      t.opportunityId,
    ),
    index("invest_interests_opp_status_idx").on(t.opportunityId, t.status),
    index("invest_interests_user_created_idx").on(t.userId, t.createdAt),
  ],
);

export const investmentIntentReservationsTable = pgTable(
  "investment_intent_reservations",
  {
    id: serial("id").primaryKey(),
    interestId: integer("interest_id")
      .notNull()
      .references(() => investmentInterestsTable.id, { onDelete: "cascade" }),
    walletId: integer("wallet_id")
      .notNull()
      .references(() => walletsTable.id, { onDelete: "cascade" }),
    amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
    status: investmentIntentReservationStatusEnum("status")
      .notNull()
      .default("RESERVED"),
    finalizedTransactionId: integer("finalized_transaction_id").references(
      () => walletTransactionsTable.id,
    ),
    reservedAt: timestamp("reserved_at").notNull().defaultNow(),
    finalizedAt: timestamp("finalized_at"),
    releasedAt: timestamp("released_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("investment_intent_reservations_interest_uidx").on(
      t.interestId,
    ),
    index("invest_reservations_wallet_status_idx").on(t.walletId, t.status),
    index("invest_reservations_status_created_idx").on(t.status, t.createdAt),
  ],
);

export const investmentTransactionsTable = pgTable(
  "investment_transactions",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    opportunityId: integer("opportunity_id")
      .notNull()
      .references(() => investmentOpportunitiesTable.id),
    amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
    type: text("type").notNull(),
    description: text("description"),
    processedAt: timestamp("processed_at").notNull().defaultNow(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    index("invest_txs_user_processed_idx").on(t.userId, t.processedAt),
    index("invest_txs_user_opp_type_idx").on(t.userId, t.opportunityId, t.type),
  ],
);

// ═══════════════════════════════════════════════════════════════
// WWN LENDING PLATFORM
// ═══════════════════════════════════════════════════════════════

export const lendingTemplatesTable = pgTable(
  "lending_templates",
  {
    id: serial("id").primaryKey(),
    legacyPoolId: integer("legacy_pool_id").unique(),
    sourceOpportunityId: integer("source_opportunity_id").unique(),
    loanType: lendingLoanTypeEnum("loan_type").notNull().default("P2P_LENDING"),
    title: text("title").notNull(),
    description: text("description").notNull(),
    termsHtml: text("terms_html"),
    status: lendingPoolStatusEnum("status").notNull().default("OPEN"),
    isSnapshot: boolean("is_snapshot").notNull().default(false),
    minCommitment: numeric("min_commitment", {
      precision: 10,
      scale: 2,
    }).notNull(),
    minRequestAmount: numeric("min_request_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("500.00"),
    maxRequestAmount: numeric("max_request_amount", {
      precision: 12,
      scale: 2,
    })
      .notNull()
      .default("25000.00"),
    borrowerAppreciationAmount: numeric("borrower_appreciation_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    borrowerTermMonths: integer("borrower_term_months").notNull().default(12),
    repaymentFrequency: lendingRepaymentFrequencyEnum("repayment_frequency")
      .notNull()
      .default("MONTHLY"),
    processingFeePct: numeric("processing_fee_pct", {
      precision: 5,
      scale: 2,
    }),
    wwnMarginPct: numeric("wwn_margin_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    reserveContributionPct: numeric("reserve_contribution_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    platformAppreciationSharePct: numeric("platform_appreciation_share_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    minAppreciationType: lendingMinAppreciationTypeEnum("min_appreciation_type")
      .notNull()
      .default("NONE"),
    minAppreciationValue: numeric("min_appreciation_value", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    minAppreciationTiers: json("min_appreciation_tiers"),
    economicsUpdatedAt: timestamp("economics_updated_at"),
    economicsUpdatedByAdminId: integer(
      "economics_updated_by_admin_id",
    ).references(() => usersTable.id),
    economicsUpdateReason: text("economics_update_reason"),
    expectedReturnPct: numeric("expected_return_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("8.00"),
    termMonths: integer("term_months").notNull().default(12),
    returnModel: lendingPoolReturnModelEnum("return_model")
      .notNull()
      .default("FIXED_SIMPLE"),
    expectedReturn: text("expected_return").notNull(),
    term: text("term").notNull(),
    minBorrowerPlanTier: membershipPlanTierEnum("min_borrower_plan_tier")
      .notNull()
      .default("STARTER"),
    borrowerType: text("borrower_type"),
    organizationId: integer("organization_id").references(
      () => organizationsTable.id,
    ),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("lending_templates_status_created_idx").on(t.status, t.createdAt),
    index("idx_lending_templates_org_status_created").on(
      t.organizationId,
      t.status,
      t.createdAt,
    ),
  ],
);

export const lendingOpportunitiesTable = pgTable(
  "lending_opportunities",
  {
    id: serial("id").primaryKey(),
    legacyPoolId: integer("legacy_pool_id").unique(),
    templateId: integer("template_id").references(
      () => lendingTemplatesTable.id,
    ),
    applicationId: integer("application_id").unique(),
    loanType: lendingLoanTypeEnum("loan_type").notNull().default("P2P_LENDING"),
    title: text("title").notNull(),
    description: text("description").notNull(),
    status: lendingPoolStatusEnum("status").notNull().default("DRAFT"),
    targetAmount: numeric("target_amount", {
      precision: 12,
      scale: 2,
    }).notNull(),
    committedAmount: numeric("committed_amount", { precision: 12, scale: 2 })
      .notNull()
      .default("0"),
    minCommitment: numeric("min_commitment", {
      precision: 10,
      scale: 2,
    }).notNull(),
    borrowerAppreciationAmount: numeric("borrower_appreciation_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    borrowerTermMonths: integer("borrower_term_months").notNull().default(12),
    repaymentFrequency: lendingRepaymentFrequencyEnum("repayment_frequency")
      .notNull()
      .default("MONTHLY"),
    processingFeePct: numeric("processing_fee_pct", {
      precision: 5,
      scale: 2,
    }),
    wwnMarginPct: numeric("wwn_margin_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    reserveContributionPct: numeric("reserve_contribution_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    platformAppreciationSharePct: numeric("platform_appreciation_share_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    minAppreciationType: lendingMinAppreciationTypeEnum("min_appreciation_type")
      .notNull()
      .default("NONE"),
    minAppreciationValue: numeric("min_appreciation_value", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    minAppreciationTiers: json("min_appreciation_tiers"),
    expectedReturnPct: numeric("expected_return_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("8.00"),
    termMonths: integer("term_months").notNull().default(12),
    returnModel: lendingPoolReturnModelEnum("return_model")
      .notNull()
      .default("FIXED_SIMPLE"),
    expectedReturn: text("expected_return").notNull(),
    term: text("term").notNull(),
    minBorrowerPlanTier: membershipPlanTierEnum("min_borrower_plan_tier")
      .notNull()
      .default("STARTER"),
    borrowerType: text("borrower_type"),
    organizationId: integer("organization_id").references(
      () => organizationsTable.id,
    ),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("lending_opps_status_created_idx").on(t.status, t.createdAt),
    index("lending_opps_template_status_created_idx").on(
      t.templateId,
      t.status,
      t.createdAt,
    ),
    index("idx_lending_opps_org_status_created").on(
      t.organizationId,
      t.status,
      t.createdAt,
    ),
  ],
);

export const lendingCommitmentsTable = pgTable(
  "lending_commitments",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    opportunityId: integer("opportunity_id")
      .notNull()
      .references(() => lendingOpportunitiesTable.id),
    amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
    status: commitmentStatusEnum("status").notNull().default("PENDING"),
    confirmedAt: timestamp("confirmed_at"),
    statusChangedAt: timestamp("status_changed_at"),
    statusChangedByAdminId: integer("status_changed_by_admin_id").references(
      () => usersTable.id,
    ),
    reviewNote: text("review_note"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("lend_commit_opp_status_created_idx").on(
      t.opportunityId,
      t.status,
      t.createdAt,
    ),
    index("lend_commit_user_status_created_idx").on(
      t.userId,
      t.status,
      t.createdAt,
    ),
    uniqueIndex("lend_commit_user_opp_active_uidx")
      .on(t.userId, t.opportunityId)
      .where(sql`${t.status} in ('PENDING', 'CONFIRMED')`),
  ],
);

export const loanApplicationsTable = pgTable(
  "loan_applications",
  {
    id: serial("id").primaryKey(),
    ascaId: integer("asca_id").references(() => ascasTable.id),
    borrowerId: integer("borrower_id")
      .notNull()
      .references(() => usersTable.id),
    templateId: integer("template_id").references(
      () => lendingTemplatesTable.id,
    ),
    opportunityId: integer("opportunity_id"),
    loanType: lendingLoanTypeEnum("loan_type").notNull().default("P2P_LENDING"),
    requestedAmount: numeric("requested_amount", {
      precision: 10,
      scale: 2,
    }).notNull(),
    purpose: text("purpose").notNull(),
    term: text("term").notNull(),
    repaymentSchedule: text("repayment_schedule").notNull(),
    termMonths: integer("term_months").notNull().default(12),
    borrowerAppreciationAmount: numeric("borrower_appreciation_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    repaymentFrequency: lendingRepaymentFrequencyEnum("repayment_frequency")
      .notNull()
      .default("MONTHLY"),
    processingFeePct: numeric("processing_fee_pct", {
      precision: 5,
      scale: 2,
    }),
    wwnMarginPct: numeric("wwn_margin_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    reserveContributionPct: numeric("reserve_contribution_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    platformAppreciationSharePct: numeric("platform_appreciation_share_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    pricingLockedAt: timestamp("pricing_locked_at"),
    totalRepayableAmount: numeric("total_repayable_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    estimatedInstallmentAmount: numeric("estimated_installment_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    funderOpportunityTitle: text("funder_opportunity_title"),
    notes: text("notes"),
    status: loanApplicationStatusEnum("status").notNull().default("DRAFT"),
    poolAssignedAt: timestamp("pool_assigned_at"),
    poolAssignedByAdminId: integer("pool_assigned_by_admin_id").references(
      () => usersTable.id,
    ),
    poolReassignedAt: timestamp("pool_reassigned_at"),
    poolReassignedByAdminId: integer("pool_reassigned_by_admin_id").references(
      () => usersTable.id,
    ),
    poolReassignmentNote: text("pool_reassignment_note"),
    reviewedByAdminId: integer("reviewed_by_admin_id"),
    reviewedAt: timestamp("reviewed_at"),
    reviewNote: text("review_note"),
    approvedAmount: numeric("approved_amount", { precision: 10, scale: 2 }),
    reservedAt: timestamp("reserved_at"),
    reservedByAdminId: integer("reserved_by_admin_id").references(
      () => usersTable.id,
    ),
    disbursedAt: timestamp("disbursed_at"),
    disbursedAmount: numeric("disbursed_amount", { precision: 10, scale: 2 }),
    disbursementTransactionId: integer(
      "disbursement_transaction_id",
    ).references(() => walletTransactionsTable.id),
    organizationId: integer("organization_id").references(
      () => organizationsTable.id,
    ),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("loan_apps_borrower_created_idx").on(t.borrowerId, t.createdAt),
    index("loan_apps_asca_status_created_idx").on(t.ascaId, t.status, t.createdAt),
    index("loan_apps_opp_status_created_idx").on(
      t.opportunityId,
      t.status,
      t.createdAt,
    ),
    index("idx_loan_apps_org_status_created").on(
      t.organizationId,
      t.status,
      t.createdAt,
    ),
    uniqueIndex("loan_apps_borrower_active_uidx")
      .on(t.borrowerId)
      .where(
        sql`${t.status} in ('DRAFT', 'SUBMITTED', 'WAITLISTED', 'UNDER_REVIEW', 'APPROVED', 'APPROVED_RESERVED', 'FUNDED', 'REPAYING')`,
      ),
  ],
);

export const loanRepaymentsTable = pgTable(
  "loan_repayments",
  {
    id: serial("id").primaryKey(),
    loanApplicationId: integer("loan_application_id")
      .notNull()
      .references(() => loanApplicationsTable.id),
    dueDate: timestamp("due_date").notNull(),
    amount: numeric("amount", { precision: 10, scale: 2 }).notNull(),
    paidAmount: numeric("paid_amount", { precision: 10, scale: 2 }),
    principalPaidAmount: numeric("principal_paid_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    returnPaidAmount: numeric("return_paid_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    feePaidAmount: numeric("fee_paid_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    creditedToPoolAmount: numeric("credited_to_pool_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    paidAt: timestamp("paid_at"),
    status: repaymentStatusEnum("status").notNull().default("PENDING"),
    method: paymentMethodEnum("method"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("loan_repayments_app_due_idx").on(t.loanApplicationId, t.dueDate),
    index("loan_repayments_status_due_idx").on(t.status, t.dueDate),
  ],
);

export const orgLendingPoolsTable = pgTable(
  "org_lending_pools",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    templateId: integer("template_id").references(() => lendingTemplatesTable.id),
    name: text("name").notNull(),
    description: text("description"),
    eligibilityMode: text("eligibility_mode")
      .$type<"CONTRIBUTORS_ONLY" | "ALL_ORG_MEMBERS">()
      .notNull()
      .default("CONTRIBUTORS_ONLY"),
    status: text("status")
      .$type<"DRAFT" | "OPEN" | "CLOSED">()
      .notNull()
      .default("DRAFT"),
    minRequestAmount: numeric("min_request_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("500.00"),
    maxRequestAmount: numeric("max_request_amount", {
      precision: 12,
      scale: 2,
    })
      .notNull()
      .default("25000.00"),
    defaultTermMonths: integer("default_term_months").notNull().default(12),
    defaultRepaymentFrequency: lendingRepaymentFrequencyEnum(
      "default_repayment_frequency",
    )
      .notNull()
      .default("MONTHLY"),
    defaultBorrowerAppreciationPct: numeric(
      "default_borrower_appreciation_pct",
      { precision: 5, scale: 2 },
    )
      .notNull()
      .default("0.00"),
    defaultProcessingFeePct: numeric("default_processing_fee_pct", {
      precision: 5,
      scale: 2,
    })
      .notNull()
      .default("0.00"),
    notes: text("notes"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("org_lending_pools_org_status_created_idx").on(
      t.organizationId,
      t.status,
      t.createdAt,
    ),
    uniqueIndex("org_lending_pools_org_template_uniq_idx")
      .on(t.organizationId, t.templateId)
      .where(sql`${t.templateId} IS NOT NULL`),
  ],
);

export const orgLendingPoolFunderUsesTable = pgTable(
  "org_lending_pool_funder_uses",
  {
    id: serial("id").primaryKey(),
    organizationId: integer("organization_id")
      .notNull()
      .references(() => organizationsTable.id, { onDelete: "cascade" }),
    poolId: integer("pool_id")
      .notNull()
      .references(() => orgLendingPoolsTable.id, { onDelete: "cascade" }),
    loanApplicationId: integer("loan_application_id")
      .notNull()
      .references(() => loanApplicationsTable.id, { onDelete: "cascade" }),
    ascaId: integer("asca_id")
      .notNull()
      .references(() => ascasTable.id, { onDelete: "cascade" }),
    fundedAmount: numeric("funded_amount", { precision: 12, scale: 2 })
      .notNull(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("org_lending_pool_funder_uses_org_pool_app_asca_uq").on(
      t.organizationId,
      t.poolId,
      t.loanApplicationId,
      t.ascaId,
    ),
    index("org_lending_pool_funder_uses_org_created_idx").on(
      t.organizationId,
      t.createdAt,
    ),
    index("org_lending_pool_funder_uses_pool_created_idx").on(
      t.poolId,
      t.createdAt,
    ),
    index("org_lending_pool_funder_uses_asca_idx").on(t.ascaId),
    index("org_lending_pool_funder_uses_loan_app_idx").on(t.loanApplicationId),
  ],
);

export const lendingOpportunityReturnEventsTable = pgTable(
  "lending_opportunity_return_events",
  {
    id: serial("id").primaryKey(),
    legacyReturnEventId: integer("legacy_return_event_id").unique(),
    opportunityId: integer("opportunity_id")
      .notNull()
      .references(() => lendingOpportunitiesTable.id),
    repaymentId: integer("repayment_id")
      .notNull()
      .references(() => loanRepaymentsTable.id),
    loanApplicationId: integer("loan_application_id")
      .notNull()
      .references(() => loanApplicationsTable.id),
    walletTransactionId: integer("wallet_transaction_id").references(
      () => walletTransactionsTable.id,
    ),
    grossAmount: numeric("gross_amount", { precision: 10, scale: 2 })
      .notNull()
      .default("0"),
    principalAmount: numeric("principal_amount", { precision: 10, scale: 2 })
      .notNull()
      .default("0"),
    returnAmount: numeric("return_amount", { precision: 10, scale: 2 })
      .notNull()
      .default("0"),
    allocatedReturnAmount: numeric("allocated_return_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    occurredAt: timestamp("occurred_at").notNull().defaultNow(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("lending_opportunity_return_events_repayment_wallet_uidx").on(
      t.repaymentId,
      t.walletTransactionId,
    ),
  ],
);

export const lendingOpportunityReturnAllocationsTable = pgTable(
  "lending_opportunity_return_allocations",
  {
    id: serial("id").primaryKey(),
    returnEventId: integer("return_event_id")
      .notNull()
      .references(() => lendingOpportunityReturnEventsTable.id),
    commitmentId: integer("commitment_id")
      .notNull()
      .references(() => lendingCommitmentsTable.id),
    opportunityId: integer("opportunity_id")
      .notNull()
      .references(() => lendingOpportunitiesTable.id),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id),
    commitmentAmountSnapshot: numeric("commitment_amount_snapshot", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    ownershipPctSnapshot: numeric("ownership_pct_snapshot", {
      precision: 8,
      scale: 6,
    })
      .notNull()
      .default("0"),
    allocatedReturnAmount: numeric("allocated_return_amount", {
      precision: 10,
      scale: 2,
    })
      .notNull()
      .default("0"),
    occurredAt: timestamp("occurred_at").notNull().defaultNow(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex(
      "lending_opportunity_return_allocations_event_commitment_uidx",
    ).on(t.returnEventId, t.commitmentId),
  ],
);

// ═══════════════════════════════════════════════════════════════
// NOTIFICATIONS
// ═══════════════════════════════════════════════════════════════

export const notificationsTable = pgTable(
  "notifications",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    category: notificationCategoryEnum("category").notNull().default("SYSTEM"),
    severity: notificationSeverityEnum("severity").notNull().default("INFO"),
    title: text("title").notNull(),
    body: text("body").notNull(),
    read: boolean("read").notNull().default(false),
    readAt: timestamp("read_at"),
    actionLabel: text("action_label"),
    actionHref: text("action_href"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    index("notifications_user_read_created_idx").on(
      t.userId,
      t.read,
      t.createdAt,
      t.id,
    ),
    index("notifications_user_created_idx").on(t.userId, t.createdAt, t.id),
    index("notifications_created_idx").on(t.createdAt),
    index("notifications_read_read_at_idx").on(t.read, t.readAt),
  ],
);

export const userPushTokensTable = pgTable(
  "user_push_tokens",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    platform: pushPlatformEnum("platform").notNull().default("EXPO"),
    token: text("token").notNull().unique(),
    deviceName: text("device_name"),
    deviceId: text("device_id"),
    isEnabled: boolean("is_enabled").notNull().default(true),
    lastSeenAt: timestamp("last_seen_at").notNull().defaultNow(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [index("push_tokens_user_enabled_idx").on(t.userId, t.isEnabled)],
);

export const userNotificationPreferencesTable = pgTable(
  "user_notification_preferences",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    channel: notificationChannelEnum("channel").notNull(),
    category: notificationCategoryEnum("category").notNull(),
    enabled: boolean("enabled").notNull(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("user_notification_prefs_user_channel_category_uidx").on(
      t.userId,
      t.channel,
      t.category,
    ),
  ],
);

export const notificationDeliveriesTable = pgTable(
  "notification_deliveries",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    channel: notificationChannelEnum("channel").notNull(),
    category: notificationCategoryEnum("category").notNull(),
    status: notificationDeliveryStatusEnum("status").notNull(),
    idempotencyKey: text("idempotency_key").notNull().unique(),
    notificationId: integer("notification_id").references(
      () => notificationsTable.id,
      { onDelete: "set null" },
    ),
    provider: text("provider"),
    errorMessage: text("error_message"),
    metadata: json("metadata").notNull().default({}),
    occurredAt: timestamp("occurred_at").notNull().defaultNow(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    index("notification_deliveries_user_occurred_idx").on(
      t.userId,
      t.occurredAt,
    ),
    index("notification_deliveries_occurred_idx").on(t.occurredAt),
  ],
);

// ═══════════════════════════════════════════════════════════════
// COMMUNITY
// ═══════════════════════════════════════════════════════════════

export const announcementsTable = pgTable("announcements", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  body: text("body").notNull(),
  category: text("category").notNull(),
  pinned: boolean("pinned").notNull().default(false),
  publishedBy: integer("published_by"),
  publishedAt: timestamp("published_at").notNull().defaultNow(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const communityEventsTable = pgTable("community_events", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  description: text("description").notNull(),
  date: timestamp("date").notNull(),
  startTime: text("start_time").notNull(),
  endTime: text("end_time"),
  location: text("location").notNull(),
  type: eventTypeEnum("type").notNull().default("VIRTUAL"),
  maxAttendees: integer("max_attendees"),
  organizerId: integer("organizer_id"),
  coverImageUrl: text("cover_image_url"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const eventRsvpsTable = pgTable(
  "event_rsvps",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    eventId: integer("event_id")
      .notNull()
      .references(() => communityEventsTable.id, { onDelete: "cascade" }),
    status: rsvpStatusEnum("status").notNull().default("GOING"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [uniqueIndex("event_rsvps_user_event_uidx").on(t.userId, t.eventId)],
);

export const discussionsTable = pgTable("discussions", {
  id: serial("id").primaryKey(),
  authorId: integer("author_id")
    .notNull()
    .references(() => usersTable.id),
  title: text("title").notNull(),
  body: text("body").notNull(),
  category: text("category").notNull(),
  pinned: boolean("pinned").notNull().default(false),
  viewCount: integer("view_count").notNull().default(0),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const discussionRepliesTable = pgTable("discussion_replies", {
  id: serial("id").primaryKey(),
  discussionId: integer("discussion_id")
    .notNull()
    .references(() => discussionsTable.id, { onDelete: "cascade" }),
  authorId: integer("author_id")
    .notNull()
    .references(() => usersTable.id),
  body: text("body").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const memberSpotlightsTable = pgTable("member_spotlights", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .notNull()
    .unique()
    .references(() => usersTable.id, { onDelete: "cascade" }),
  achievement: text("achievement").notNull(),
  featured: boolean("featured").notNull().default(false),
  approvedAt: timestamp("approved_at"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

// ═══════════════════════════════════════════════════════════════
export const communityPostTypeEnum = pgEnum("community_post_type", [
  "USER_UPDATE",
  "MILESTONE",
  "ADMIN_STORY",
  "RESOURCE_SHARE",
]);

export const communityProfilesTable = pgTable("community_profiles", {
  id: serial("id").primaryKey(),
  userId: integer("user_id")
    .unique()
    .references(() => usersTable.id, { onDelete: "cascade" }),
  organizationId: integer("organization_id")
    .unique()
    .references(() => organizationsTable.id, { onDelete: "cascade" }),
  bio: text("bio"),
  location: text("location"),
  interests: text("interests").array(),
  badges: text("badges").array(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("community_profiles_user_idx").on(t.userId),
  index("community_profiles_org_idx").on(t.organizationId),
]);

export const communityPostsTable = pgTable("community_posts", {
  id: serial("id").primaryKey(),
  postType: communityPostTypeEnum("post_type").notNull().default("USER_UPDATE"),
  authorId: integer("author_id").references(() => usersTable.id), // For USER_UPDATE / ADMIN_STORY
  organizationId: integer("organization_id").references(() => organizationsTable.id), // If an Org posts
  title: text("title"), // Required for ADMIN_STORY
  slug: text("slug").unique(), // For ADMIN_STORY permalinks
  excerpt: text("excerpt"), // For ADMIN_STORY
  contentHtml: text("content_html").notNull(),
  coverImageUrl: text("cover_image_url"),
  
  // Visibility Flags (Primarily used for ADMIN_STORY)
  showToIndividuals: boolean("show_to_individuals").notNull().default(true),
  showToInvestors: boolean("show_to_investors").notNull().default(true),
  showToOrganizations: boolean("show_to_organizations").notNull().default(true),
  
  isPublished: boolean("is_published").notNull().default(true),
  publishedAt: timestamp("published_at").defaultNow(),
  
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("community_posts_type_published_idx").on(t.postType, t.isPublished, t.publishedAt),
]);

export const communityCommentsTable = pgTable("community_comments", {
  id: serial("id").primaryKey(),
  postId: integer("post_id").notNull().references(() => communityPostsTable.id, { onDelete: "cascade" }),
  authorId: integer("author_id").notNull().references(() => usersTable.id),
  parentId: integer("parent_id"), // Self-referencing for threaded replies. Can't use references() inline due to Drizzle circular dep limits without explicit relations
  contentHtml: text("content_html").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("community_comments_post_idx").on(t.postId, t.createdAt),
]);

export const communityReactionsTable = pgTable("community_reactions", {
  id: serial("id").primaryKey(),
  postId: integer("post_id").references(() => communityPostsTable.id, { onDelete: "cascade" }),
  commentId: integer("comment_id").references(() => communityCommentsTable.id, { onDelete: "cascade" }),
  userId: integer("user_id").notNull().references(() => usersTable.id),
  reactionType: text("reaction_type").notNull().default("LIKE"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
}, (t) => [
  uniqueIndex("community_reactions_post_user_uidx").on(t.postId, t.userId),
  uniqueIndex("community_reactions_comment_user_uidx").on(t.commentId, t.userId),
]);

export const communityForumsTable = pgTable("community_forums", {
  id: serial("id").primaryKey(),
  name: text("name").notNull().unique(),
  slug: text("slug").notNull().unique(),
  description: text("description"),
  sortOrder: integer("sort_order").notNull().default(0),
  isRestricted: boolean("is_restricted").notNull().default(false),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const forumThreadsTable = pgTable("forum_threads", {
  id: serial("id").primaryKey(),
  forumId: integer("forum_id").notNull().references(() => communityForumsTable.id, { onDelete: "cascade" }),
  authorId: integer("author_id").notNull().references(() => usersTable.id),
  title: text("title").notNull(),
  slug: text("slug").notNull().unique(),
  contentHtml: text("content_html").notNull(),
  isPinned: boolean("is_pinned").notNull().default(false),
  isLocked: boolean("is_locked").notNull().default(false),
  viewCount: integer("view_count").notNull().default(0),
  lastActivityAt: timestamp("last_activity_at").notNull().defaultNow(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("forum_threads_forum_idx").on(t.forumId, t.lastActivityAt),
]);

export const messageThreadsTable = pgTable("message_threads", {
  id: serial("id").primaryKey(),
  isGroup: boolean("is_group").notNull().default(false),
  name: text("name"), // For group chats
  ascaId: integer("asca_id").references(() => ascasTable.id), // Link to a Savings Circle
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

export const messageThreadParticipantsTable = pgTable("message_thread_participants", {
  id: serial("id").primaryKey(),
  threadId: integer("thread_id").notNull().references(() => messageThreadsTable.id, { onDelete: "cascade" }),
  userId: integer("user_id").notNull().references(() => usersTable.id, { onDelete: "cascade" }),
  lastReadAt: timestamp("last_read_at"),
  joinedAt: timestamp("joined_at").notNull().defaultNow(),
}, (t) => [
  uniqueIndex("message_thread_participants_uidx").on(t.threadId, t.userId),
]);

export const directMessagesTable = pgTable("direct_messages", {
  id: serial("id").primaryKey(),
  threadId: integer("thread_id").notNull().references(() => messageThreadsTable.id, { onDelete: "cascade" }),
  senderId: integer("sender_id").notNull().references(() => usersTable.id),
  content: text("content").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
}, (t) => [
  index("direct_messages_thread_idx").on(t.threadId, t.createdAt),
]);

// RESOURCE CENTRE
// 

export const resourceCategoriesTable = pgTable("resource_categories", {
  id: serial("id").primaryKey(),
  name: text("name").notNull().unique(),
  slug: text("slug").notNull().unique(),
  sortOrder: integer("sort_order").notNull().default(0),
});

export const resourcesTable = pgTable("resources", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  slug: text("slug").notNull().unique(),
  description: text("description"),
  resourceKind: resourceKindEnum("resource_kind").notNull().default("FILE"),
  categoryId: integer("category_id")
    .notNull()
    .references(() => resourceCategoriesTable.id),
  fileUrl: text("file_url"),
  fileType: resourceFileTypeEnum("file_type").notNull().default("PDF"),
  fileSize: integer("file_size"),
  coverImageUrl: text("cover_image_url"),
  authorName: text("author_name"),
  contentHtml: text("content_html"),
  showToIndividuals: boolean("show_to_individuals").notNull().default(false),
  showToInvestors: boolean("show_to_investors").notNull().default(true),
  showToOrganizations: boolean("show_to_organizations").notNull().default(true),
  uploadedById: integer("uploaded_by_id"),
  isPublished: boolean("is_published").notNull().default(false),
  publishedAt: timestamp("published_at"),
  downloads: integer("downloads").notNull().default(0),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

// ═══════════════════════════════════════════════════════════════
// ZOD SCHEMAS & TYPES
// ═══════════════════════════════════════════════════════════════

// ═══════════════════════════════════════════════════════════════
// ONBOARDING SLIDES
// ═══════════════════════════════════════════════════════════════

export const onboardingSlidesTable = pgTable("onboarding_slides", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  subtitle: text("subtitle"),
  body: text("body"),
  illustrationIcon: text("illustration_icon")
    .notNull()
    .default("diamond-stone"),
  illustrationUrl: text("illustration_url"),
  accentColor: text("accent_color").notNull().default("#f27a4a"),
  sortOrder: integer("sort_order").notNull().default(0),
  isActive: boolean("is_active").notNull().default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export type OnboardingSlide = typeof onboardingSlidesTable.$inferSelect;
export type InsertOnboardingSlide = typeof onboardingSlidesTable.$inferInsert;

export const onboardingProgramsTable = pgTable(
  "onboarding_programs",
  {
    id: serial("id").primaryKey(),
    title: text("title").notNull(),
    description: text("description"),
    targetMembershipType: membershipTypeEnum("target_membership_type")
      .notNull()
      .default("INDIVIDUAL"),
    numberOfWeeks: integer("number_of_weeks").notNull(),
    durationUnit: onboardingProgramDurationUnitEnum("duration_unit")
      .notNull()
      .default("WEEK"),
    startDate: timestamp("start_date"),
    endDate: timestamp("end_date"),
    status: onboardingProgramStatusEnum("status").notNull().default("DRAFT"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("onboarding_programs_status_idx").on(t.status),
    index("onboarding_programs_target_membership_idx").on(
      t.targetMembershipType,
    ),
    index("onboarding_programs_created_idx").on(t.createdAt),
  ],
);

export const onboardingProgramWeeksTable = pgTable(
  "onboarding_program_weeks",
  {
    id: serial("id").primaryKey(),
    programId: integer("program_id")
      .notNull()
      .references(() => onboardingProgramsTable.id, { onDelete: "cascade" }),
    weekNumber: integer("week_number").notNull(),
    title: text("title").notNull(),
    description: text("description"),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("onboarding_program_weeks_unique_idx").on(
      t.programId,
      t.weekNumber,
    ),
    index("onboarding_program_weeks_order_idx").on(t.programId, t.sortOrder),
  ],
);

export const onboardingWeekActivitiesTable = pgTable(
  "onboarding_week_activities",
  {
    id: serial("id").primaryKey(),
    weekId: integer("week_id")
      .notNull()
      .references(() => onboardingProgramWeeksTable.id, {
        onDelete: "cascade",
      }),
    title: text("title").notNull(),
    description: text("description"),
    durationMinutes: integer("duration_minutes"),
    type: onboardingActivityTypeEnum("type").notNull(),
    content: json("content").$type<Record<string, unknown> | null>(),
    sortOrder: integer("sort_order").notNull().default(0),
    isRequired: boolean("is_required").notNull().default(true),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("onboarding_week_activities_order_idx").on(t.weekId, t.sortOrder),
    uniqueIndex("onboarding_week_activities_unique_title_idx").on(
      t.weekId,
      t.title,
    ),
  ],
);

export const onboardingEnrollmentsTable = pgTable(
  "onboarding_enrollments",
  {
    id: serial("id").primaryKey(),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    programId: integer("program_id")
      .notNull()
      .references(() => onboardingProgramsTable.id, { onDelete: "restrict" }),
    enrolledAt: timestamp("enrolled_at").notNull().defaultNow(),
    completedAt: timestamp("completed_at"),
    lastActivityAt: timestamp("last_activity_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("onboarding_enrollments_user_program_unique_idx").on(
      t.userId,
      t.programId,
    ),
    index("onboarding_enrollments_user_idx").on(t.userId),
    index("onboarding_enrollments_program_idx").on(t.programId),
  ],
);

export const onboardingActivityProgressTable = pgTable(
  "onboarding_activity_progress",
  {
    id: serial("id").primaryKey(),
    enrollmentId: integer("enrollment_id")
      .notNull()
      .references(() => onboardingEnrollmentsTable.id, { onDelete: "cascade" }),
    activityId: integer("activity_id")
      .notNull()
      .references(() => onboardingWeekActivitiesTable.id, {
        onDelete: "cascade",
      }),
    status: onboardingActivityStatusEnum("status")
      .notNull()
      .default("NOT_STARTED"),
    startedAt: timestamp("started_at"),
    completedAt: timestamp("completed_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("onboarding_activity_progress_unique_idx").on(
      t.enrollmentId,
      t.activityId,
    ),
    index("onboarding_activity_progress_enrollment_idx").on(t.enrollmentId),
  ],
);

export type OnboardingProgram = typeof onboardingProgramsTable.$inferSelect;
export type OnboardingProgramWeek =
  typeof onboardingProgramWeeksTable.$inferSelect;
export type OnboardingWeekActivity =
  typeof onboardingWeekActivitiesTable.$inferSelect;
export type OnboardingEnrollment =
  typeof onboardingEnrollmentsTable.$inferSelect;
export type OnboardingActivityProgress =
  typeof onboardingActivityProgressTable.$inferSelect;

// ═══════════════════════════════════════════════════════════════
// ASCA (Accumulating Savings and Credit Association)
// ═══════════════════════════════════════════════════════════════

export const ascasTable = pgTable(
  "ascas",
  {
    id: serial("id").primaryKey(),
    name: text("name").notNull(),
    description: text("description"),
    termsHtml: text("terms_html"),
    organizationId: integer("organization_id").references(
      () => organizationsTable.id,
    ),
    type: ascaTypeEnum("type").notNull().default("GENERAL"),
    status: ascaStatusEnum("status").notNull().default("DRAFT"),
    investmentId: integer("investment_id").references(
      () => investmentOpportunitiesTable.id,
    ),
    escrowWalletId: integer("escrow_wallet_id").references(
      () => walletsTable.id,
    ),
    targetAmount: numeric("target_amount", { precision: 12, scale: 2 }),
    currentAmount: numeric("current_amount", { precision: 12, scale: 2 })
      .notNull()
      .default("0"),
    fundingStartAt: timestamp("funding_start_at"),
    fundingEndAt: timestamp("funding_end_at"),
    constitution: json("constitution")
      .$type<Record<string, unknown>>()
      .notNull()
      .default({}),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("ascas_status_idx").on(t.status),
    index("ascas_type_idx").on(t.type),
    index("ascas_investment_idx").on(t.investmentId),
    index("ascas_org_status_idx").on(t.organizationId, t.status),
  ],
);

export const ascaMembersTable = pgTable(
  "asca_members",
  {
    id: serial("id").primaryKey(),
    ascaId: integer("asca_id")
      .notNull()
      .references(() => ascasTable.id, { onDelete: "cascade" }),
    userId: integer("user_id")
      .notNull()
      .references(() => usersTable.id, { onDelete: "cascade" }),
    targetContribution: numeric("target_contribution", {
      precision: 12,
      scale: 2,
    }).notNull(),
    contributedAmount: numeric("contributed_amount", {
      precision: 12,
      scale: 2,
    })
      .notNull()
      .default("0"),
    status: ascaMemberStatusEnum("status").notNull().default("ACTIVE"),
    joinedAt: timestamp("joined_at").notNull().defaultNow(),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("asca_members_asca_user_idx").on(t.ascaId, t.userId),
    index("asca_members_user_idx").on(t.userId),
  ],
);

export const ascaContributionsTable = pgTable(
  "asca_contributions",
  {
    id: serial("id").primaryKey(),
    ascaId: integer("asca_id")
      .notNull()
      .references(() => ascasTable.id, { onDelete: "cascade" }),
    memberId: integer("member_id")
      .notNull()
      .references(() => ascaMembersTable.id, { onDelete: "cascade" }),
    amount: numeric("amount", { precision: 12, scale: 2 }).notNull(),
    status: ascaContributionStatusEnum("status").notNull().default("PENDING"),
    dueDate: timestamp("due_date"),
    paidAt: timestamp("paid_at"),
    transactionId: integer("transaction_id").references(
      () => walletTransactionsTable.id,
    ),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("asca_contributions_member_idx").on(t.memberId),
    index("asca_contributions_asca_idx").on(t.ascaId),
    index("asca_contributions_status_idx").on(t.status),
  ],
);

export type Asca = typeof ascasTable.$inferSelect;
export type AscaMember = typeof ascaMembersTable.$inferSelect;
export type AscaContribution = typeof ascaContributionsTable.$inferSelect;

// ═══════════════════════════════════════════════════════════════
// DERIVED TYPES
// ═══════════════════════════════════════════════════════════════

export const insertUserSchema = createInsertSchema(usersTable).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});
export type InsertUser = z.infer<typeof insertUserSchema>;
export type User = typeof usersTable.$inferSelect;
export type VerificationToken = typeof verificationTokensTable.$inferSelect;
export type Organization = typeof organizationsTable.$inferSelect;
export type MembershipPlan = typeof membershipPlansTable.$inferSelect;
export type SavingsCircle = typeof savingsCirclesTable.$inferSelect;
export type Wallet = typeof walletsTable.$inferSelect;
export type WalletTransaction = typeof walletTransactionsTable.$inferSelect;
export type InvestmentOpportunity =
  typeof investmentOpportunitiesTable.$inferSelect;
export type Notification = typeof notificationsTable.$inferSelect;
