WITH commitment_membership AS (
  SELECT
    lc.opportunity_id,
    bool_or(u.membership_type = 'INVESTOR') AS has_investor_funder,
    bool_or(u.membership_type = 'INDIVIDUAL') AS has_individual_funder
  FROM lending_commitments lc
  INNER JOIN users u ON u.id = lc.user_id
  WHERE lc.status IN ('PENDING', 'CONFIRMED')
  GROUP BY lc.opportunity_id
),
classified_opportunities AS (
  SELECT
    cm.opportunity_id,
    CASE
      WHEN cm.has_investor_funder THEN 'P2P_LENDING'
      WHEN cm.has_individual_funder THEN 'COMMUNITY_LENDING'
      ELSE NULL
    END AS loan_type
  FROM commitment_membership cm
)
UPDATE lending_opportunities o
SET loan_type = classified_opportunities.loan_type::lending_loan_type_enum
FROM classified_opportunities
WHERE classified_opportunities.loan_type IS NOT NULL
  AND o.id = classified_opportunities.opportunity_id;

UPDATE loan_applications a
SET loan_type = o.loan_type
FROM lending_opportunities o
WHERE a.opportunity_id = o.id;

UPDATE lending_templates t
SET loan_type = o.loan_type
FROM lending_opportunities o
WHERE t.source_opportunity_id = o.id;

UPDATE lending_templates t
SET loan_type = a.loan_type
FROM loan_applications a
WHERE a.template_id = t.id
  AND a.loan_type = 'COMMUNITY_LENDING';

UPDATE lending_opportunities o
SET loan_type = t.loan_type
FROM lending_templates t
WHERE o.template_id = t.id
  AND t.loan_type = 'COMMUNITY_LENDING';

UPDATE loan_applications a
SET loan_type = t.loan_type
FROM lending_templates t
WHERE a.template_id = t.id
  AND t.loan_type = 'COMMUNITY_LENDING';