import path from "node:path";
import { fileURLToPath } from "node:url";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const MIGRATIONS_DIR = path.resolve(__dirname, "../migrations");

export type SqlMigrationEntry = {
  fileName: string;
  reason: string;
};

function migrationPath(fileName: string) {
  return path.resolve(MIGRATIONS_DIR, fileName);
}

export const ACTIVE_SQL_MIGRATIONS: SqlMigrationEntry[] = [
  {
    fileName: "20260707_platform_wallet_and_lending_fee.sql",
    reason: "Current lending repayment/platform wallet schema support.",
  },
  {
    fileName: "20260708_lending_return_payout_wallet_tx_type.sql",
    reason: "Current lending wallet transaction enum support.",
  },
  {
    fileName: "20260709_bank_setup.sql",
    reason: "Current bank setup table bootstrap.",
  },
  {
    fileName: "20260709_circle_forming_timeout.sql",
    reason: "Current savings circle lifecycle support.",
  },
  {
    fileName: "20260713_organization_tenancy_phase1.sql",
    reason: "Organization tenancy baseline.",
  },
  {
    fileName: "20260713_organization_member_management_phase3.sql",
    reason: "Organization member invitation support.",
  },
  {
    fileName: "20260713_organization_susu_phase5.sql",
    reason: "Organization SUSU ownership support.",
  },
  {
    fileName: "20260713_organization_subscription_gating_backfill.sql",
    reason:
      "Backfill organization subscription state from already-activated organization memberships.",
  },
  {
    fileName: "20260713_auth_session_hardening.sql",
    reason:
      "Add session versioning and password reset attempt tracking for auth hardening.",
  },
  {
    fileName: "20260717_organization_treasury_phaseb.sql",
    reason:
      "Add organization-owned wallet support for isolated tenant treasury.",
  },
  {
    fileName: "20260717_organization_member_contributions_phasec.sql",
    reason:
      "Add organization member obligation support for Phase C contribution rails.",
  },
  {
    fileName: "20260717_organization_member_status_controls.sql",
    reason:
      "Add organization-owned member seat status controls for active and pending tenancy access.",
  },
  {
    fileName: "20260717_organization_bank_settings.sql",
    reason:
      "Add organization-owned bank settings for tenant treasury manual funding instructions.",
  },
  {
    fileName: "20260718_organization_bank_charges.sql",
    reason:
      "Add organization-owned bank charges for tenant treasury withdrawal fees.",
  },
  {
    fileName: "20260718_organization_bank_charges_default_zero.sql",
    reason: "Default organization bank charges to zero.",
  },
  {
    fileName: "20260718_org_susu_auto_deduct.sql",
    reason: "Add organization SUSU auto-deduct toggle.",
  },
  {
    fileName: "20260720_lending_loan_type_split.sql",
    reason:
      "Add explicit lending loan-type separation for Community Lending and P2P Lending.",
  },
  {
    fileName: "20260720_lending_loan_type_backfill.sql",
    reason:
      "Backfill deterministic lending loan-type classifications from linked records and actual funder membership.",
  },
  {
    fileName: "20260723_member_susu_auto_deduct.sql",
    reason: "Add member-level SUSU auto-deduct opt-in for universal circles.",
  },
  {
    fileName: "20260723_onboarding_program_role_target.sql",
    reason:
      "Add role-targeted onboarding program assignment by membership type.",
  },
  {
    fileName: "20260723_onboarding_enrollment_history.sql",
    reason:
      "Preserve onboarding enrollment history per program instead of overwriting prior program progress.",
  },
  {
    fileName: "20260724_membership_plan_entrepreneur_tier.sql",
    reason: "Add Entrepreneur as a fourth membership plan tier enum value.",
  },
  {
    fileName: "20260724_membership_plan_entrepreneur_seed.sql",
    reason:
      "Seed default Entrepreneur catalog entries for each membership type after the enum value exists.",
  },
  {
    fileName: "20260724_notification_query_indexes.sql",
    reason:
      "Add covering notification indexes for unread counts, inbox lists, and retention cleanup.",
  },
  {
    fileName: "20260726_p2p_rate_split.sql",
    reason:
      "Add explicit P2P WWN margin and reserve contribution pricing columns.",
  },
  {
    fileName: "20260727_membership_role_specific_plan_tiers.sql",
    reason:
      "Add role-specific membership plan tier values and remove tier uniqueness constraints for plans.",
  },
  {
    fileName: "20260727_membership_plan_tier_entitlement_cleanup.sql",
    reason:
      "Remap legacy membership plan rows to role-specific tiers and stop plan entitlements from depending on organization type.",
  },
  {
    fileName: "20260728_lending_principal_payout_wallet_tx_type.sql",
    reason:
      "Add a dedicated wallet transaction enum value for lending principal payouts.",
  },
  {
    fileName: "20260728_investment_principal_payout_wallet_tx_type.sql",
    reason:
      "Add a dedicated wallet transaction enum value for Susunomics investment principal payouts.",
  },
  {
    fileName: "20260729_resource_cms_engine.sql",
    reason:
      "Extend resources into a CMS-style article engine with role-based dashboard exposure.",
  },
  {
    fileName: "20260803_investment_opportunity_investor_tiers.sql",
    reason:
      "Add investor tier targeting to investment opportunities for tier-aware visibility and eligibility.",
  },
  {
    fileName: "20260803_loan_application_funder_opportunity_title.sql",
    reason:
      "Persist an admin-managed funder-facing lending opportunity title on loan applications.",
  },
  {
    fileName: "20260803_organization_service_request_history.sql",
    reason:
      "Allow organizations to submit multiple service requests and retain request history.",
  },
  {
    fileName: "20260805_asca_terms.sql",
    reason:
      "Add termsHtml rich-text body column on ASCA tables for admin-authored member terms.",
  },
  {
    fileName: "20260809_terms_html_columns.sql",
    reason:
      "Add nullable terms_html rich-text body columns on savings_circles, investment_opportunities, and lending_templates for admin-authored accept-terms flows.",
  },
  {
    fileName: "20260809_profile_completion_sections.sql",
    reason:
      "Add profile completion section fields (contact/employment/next-of-kin/referrer/consent/KYC/delete-account metadata).",
  },
  {
    fileName: "20260809_membership_id_number.sql",
    reason:
      "Add configurable membership registration ID settings (platform + organization) and store generated membership IDs on users.",
  },
  {
    fileName: "20260810_investment_opps_org_id.sql",
    reason:
      "Add organization_id to investment_opportunities for Organization Workspace Susunomics.",
  },
  {
    fileName: "20260814_force_email_notifications_true.sql",
    reason:
      "Force all stored EMAIL-channel notification preferences to enabled=true for every user and remove their ability to opt out of email notifications.",
  },
  {
    fileName: "20260814_add_referrer_wwn_id_column.sql",
    reason:
      "Add referrer_wwn_id TEXT column to users table for the new Referrer's WWN ID # profile field (matches Drizzle schema index.ts).",
  },
  {
    fileName: "20260816_community_forums_messaging.sql",
    reason: "Add community forums and messaging threads tables for Phase 4.",
  },
  {
    fileName: "20260816_community_posts_fix.sql",
    reason: "Fix missing community_posts table missing from Drizzle generate.",
  },
  {
    fileName: "20260823_asca_member_borrowing_phase1.sql",
    reason:
      "Add ASCA-linked loan application support for GENERAL ASCA member borrowing.",
  },
  {
    fileName: "20260824_investment_opportunity_funding_model.sql",
    reason:
      "Add crowd vs non-crowd funding model to investment opportunities.",
  },
  {
    fileName: "20260830_membership_plan_billing_mode.sql",
    reason:
      "Add billingMode (ONE_TIME|RECURRING) and defaultBillingCycle (MONTHLY|ANNUAL) columns to membership_plans so any plan tier supports recurring vs one-time purchase without schema changes.",
  },
  {
    fileName: "20260831_org_lending_plus_asca_eligible.sql",
    reason:
      "Add organization_id FK to lending_templates, lending_opportunities, and loan_applications; introduce org_lending_pools table for GENERAL ASCA org-community-lending pool management with eligibility mode and lifecycle status; introduce org_lending_pool_funder_uses join table to track which GENERAL ASCA funders contributed to which loan applications via an organization lending pool.",
  },
  {
    fileName: "20260831_helping_hand_org_scope.sql",
    reason:
      "Add nullable organization_id FKs + covering indexes on helping_hand_applications and helping_hand_repayments for org-scoped Matching Fund parity.",
  },
];

export const RETIRED_SQL_MIGRATIONS: SqlMigrationEntry[] = [
  {
    fileName: "20260707_lending_economics_config.sql",
    reason:
      "Historical pre-refactor lending_pools augmentation. Obsolete after the lending pool model was removed.",
  },
  {
    fileName: "20260707_lending_plan_tier_gating.sql",
    reason:
      "Historical pre-refactor lending_pools tier gating. Obsolete after the lending pool model was removed.",
  },
  {
    fileName: "20260711_lending_templates_opportunities.sql",
    reason:
      "One-time historical data migration from lending_pools to lending_templates/lending_opportunities. Non-replayable on the current post-refactor schema.",
  },
];

export const ACTIVE_SQL_MIGRATION_PATHS = ACTIVE_SQL_MIGRATIONS.map(
  (entry) => ({
    ...entry,
    path: migrationPath(entry.fileName),
  }),
);

export const RETIRED_SQL_MIGRATION_PATHS = RETIRED_SQL_MIGRATIONS.map(
  (entry) => ({
    ...entry,
    path: migrationPath(entry.fileName),
  }),
);
