DO $$
BEGIN
  ALTER TYPE wallet_owner_type_enum ADD VALUE IF NOT EXISTS 'LENDING_MASTER';
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

CREATE TABLE IF NOT EXISTS lending_templates (
  id serial PRIMARY KEY,
  legacy_pool_id integer UNIQUE,
  source_opportunity_id integer UNIQUE,
  title text NOT NULL,
  description text NOT NULL,
  status lending_pool_status_enum NOT NULL DEFAULT 'OPEN',
  is_snapshot boolean NOT NULL DEFAULT false,
  min_commitment numeric(10, 2) NOT NULL,
  min_request_amount numeric(10, 2) NOT NULL DEFAULT '500.00',
  max_request_amount numeric(12, 2) NOT NULL DEFAULT '25000.00',
  borrower_appreciation_amount numeric(10, 2) NOT NULL DEFAULT '0.00',
  borrower_term_months integer NOT NULL DEFAULT 12,
  repayment_frequency lending_repayment_frequency_enum NOT NULL DEFAULT 'MONTHLY',
  processing_fee_pct numeric(5, 2),
  platform_appreciation_share_pct numeric(5, 2) NOT NULL DEFAULT '0.00',
  min_appreciation_type lending_min_appreciation_type_enum NOT NULL DEFAULT 'NONE',
  min_appreciation_value numeric(10, 2) NOT NULL DEFAULT '0.00',
  min_appreciation_tiers json,
  economics_updated_at timestamp,
  economics_updated_by_admin_id integer REFERENCES users(id),
  economics_update_reason text,
  expected_return_pct numeric(5, 2) NOT NULL DEFAULT '8.00',
  term_months integer NOT NULL DEFAULT 12,
  return_model lending_pool_return_model_enum NOT NULL DEFAULT 'FIXED_SIMPLE',
  expected_return text NOT NULL,
  term text NOT NULL,
  min_borrower_plan_tier membership_plan_tier_enum NOT NULL DEFAULT 'STARTER',
  borrower_type text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS lending_opportunities (
  id serial PRIMARY KEY,
  legacy_pool_id integer UNIQUE,
  template_id integer REFERENCES lending_templates(id),
  application_id integer UNIQUE REFERENCES loan_applications(id),
  title text NOT NULL,
  description text NOT NULL,
  status lending_pool_status_enum NOT NULL DEFAULT 'DRAFT',
  target_amount numeric(12, 2) NOT NULL,
  committed_amount numeric(12, 2) NOT NULL DEFAULT '0',
  min_commitment numeric(10, 2) NOT NULL,
  borrower_appreciation_amount numeric(10, 2) NOT NULL DEFAULT '0.00',
  borrower_term_months integer NOT NULL DEFAULT 12,
  repayment_frequency lending_repayment_frequency_enum NOT NULL DEFAULT 'MONTHLY',
  processing_fee_pct numeric(5, 2),
  platform_appreciation_share_pct numeric(5, 2) NOT NULL DEFAULT '0.00',
  min_appreciation_type lending_min_appreciation_type_enum NOT NULL DEFAULT 'NONE',
  min_appreciation_value numeric(10, 2) NOT NULL DEFAULT '0.00',
  min_appreciation_tiers json,
  expected_return_pct numeric(5, 2) NOT NULL DEFAULT '8.00',
  term_months integer NOT NULL DEFAULT 12,
  return_model lending_pool_return_model_enum NOT NULL DEFAULT 'FIXED_SIMPLE',
  expected_return text NOT NULL,
  term text NOT NULL,
  min_borrower_plan_tier membership_plan_tier_enum NOT NULL DEFAULT 'STARTER',
  borrower_type text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

ALTER TABLE loan_applications
ADD COLUMN IF NOT EXISTS template_id integer REFERENCES lending_templates(id),
ADD COLUMN IF NOT EXISTS opportunity_id integer REFERENCES lending_opportunities(id);

ALTER TABLE lending_commitments
ADD COLUMN IF NOT EXISTS opportunity_id integer REFERENCES lending_opportunities(id);

CREATE TABLE IF NOT EXISTS lending_opportunity_return_events (
  id serial PRIMARY KEY,
  legacy_return_event_id integer UNIQUE,
  opportunity_id integer NOT NULL REFERENCES lending_opportunities(id),
  repayment_id integer NOT NULL REFERENCES loan_repayments(id),
  loan_application_id integer NOT NULL REFERENCES loan_applications(id),
  wallet_transaction_id integer REFERENCES wallet_transactions(id),
  gross_amount numeric(10, 2) NOT NULL DEFAULT '0',
  principal_amount numeric(10, 2) NOT NULL DEFAULT '0',
  return_amount numeric(10, 2) NOT NULL DEFAULT '0',
  allocated_return_amount numeric(10, 2) NOT NULL DEFAULT '0',
  occurred_at timestamp NOT NULL DEFAULT now(),
  created_at timestamp NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS lending_opportunity_return_events_repayment_wallet_uidx
ON lending_opportunity_return_events(repayment_id, wallet_transaction_id);

CREATE TABLE IF NOT EXISTS lending_opportunity_return_allocations (
  id serial PRIMARY KEY,
  return_event_id integer NOT NULL REFERENCES lending_opportunity_return_events(id),
  commitment_id integer NOT NULL REFERENCES lending_commitments(id),
  opportunity_id integer NOT NULL REFERENCES lending_opportunities(id),
  user_id integer NOT NULL REFERENCES users(id),
  commitment_amount_snapshot numeric(10, 2) NOT NULL DEFAULT '0',
  ownership_pct_snapshot numeric(8, 6) NOT NULL DEFAULT '0',
  allocated_return_amount numeric(10, 2) NOT NULL DEFAULT '0',
  occurred_at timestamp NOT NULL DEFAULT now(),
  created_at timestamp NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS lending_opportunity_return_allocations_event_commitment_uidx
ON lending_opportunity_return_allocations(return_event_id, commitment_id);

CREATE TEMP TABLE tmp_lending_opportunity_pool_ids AS
SELECT DISTINCT pool_id AS pool_id FROM loan_applications WHERE pool_id IS NOT NULL
UNION
SELECT DISTINCT pool_id AS pool_id FROM lending_commitments WHERE pool_id IS NOT NULL
UNION
SELECT DISTINCT pool_id AS pool_id FROM helping_hand_applications WHERE pool_id IS NOT NULL;

INSERT INTO lending_opportunities (
  legacy_pool_id,
  title,
  description,
  status,
  target_amount,
  committed_amount,
  min_commitment,
  borrower_appreciation_amount,
  borrower_term_months,
  repayment_frequency,
  processing_fee_pct,
  platform_appreciation_share_pct,
  min_appreciation_type,
  min_appreciation_value,
  min_appreciation_tiers,
  expected_return_pct,
  term_months,
  return_model,
  expected_return,
  term,
  min_borrower_plan_tier,
  borrower_type,
  created_at,
  updated_at
)
SELECT
  p.id,
  p.title,
  p.description,
  p.status,
  p.target_amount,
  p.committed_amount,
  p.min_commitment,
  p.borrower_appreciation_amount,
  p.borrower_term_months,
  p.repayment_frequency,
  p.processing_fee_pct,
  p.platform_appreciation_share_pct,
  p.min_appreciation_type,
  p.min_appreciation_value,
  p.min_appreciation_tiers,
  p.expected_return_pct,
  p.term_months,
  p.return_model,
  p.expected_return,
  p.term,
  p.min_borrower_plan_tier,
  p.borrower_type,
  p.created_at,
  p.updated_at
FROM lending_pools p
INNER JOIN tmp_lending_opportunity_pool_ids t ON t.pool_id = p.id
ON CONFLICT (legacy_pool_id) DO NOTHING;

INSERT INTO lending_templates (
  legacy_pool_id,
  title,
  description,
  status,
  min_commitment,
  min_request_amount,
  max_request_amount,
  borrower_appreciation_amount,
  borrower_term_months,
  repayment_frequency,
  processing_fee_pct,
  platform_appreciation_share_pct,
  min_appreciation_type,
  min_appreciation_value,
  min_appreciation_tiers,
  economics_updated_at,
  economics_updated_by_admin_id,
  economics_update_reason,
  expected_return_pct,
  term_months,
  return_model,
  expected_return,
  term,
  min_borrower_plan_tier,
  borrower_type,
  created_at,
  updated_at
)
SELECT
  p.id,
  p.title,
  p.description,
  p.status,
  p.min_commitment,
  p.min_request_amount,
  p.max_request_amount,
  p.borrower_appreciation_amount,
  p.borrower_term_months,
  p.repayment_frequency,
  p.processing_fee_pct,
  p.platform_appreciation_share_pct,
  p.min_appreciation_type,
  p.min_appreciation_value,
  p.min_appreciation_tiers,
  p.economics_updated_at,
  p.economics_updated_by_admin_id,
  p.economics_update_reason,
  p.expected_return_pct,
  p.term_months,
  p.return_model,
  p.expected_return,
  p.term,
  p.min_borrower_plan_tier,
  p.borrower_type,
  p.created_at,
  p.updated_at
FROM lending_pools p
LEFT JOIN tmp_lending_opportunity_pool_ids t ON t.pool_id = p.id
WHERE t.pool_id IS NULL
ON CONFLICT (legacy_pool_id) DO NOTHING;

UPDATE loan_applications a
SET opportunity_id = o.id
FROM lending_opportunities o
WHERE a.pool_id IS NOT NULL AND o.legacy_pool_id = a.pool_id;

UPDATE lending_commitments c
SET opportunity_id = o.id
FROM lending_opportunities o
WHERE c.pool_id IS NOT NULL AND o.legacy_pool_id = c.pool_id;

UPDATE helping_hand_applications hh
SET disbursement_transaction_id = hh.disbursement_transaction_id
WHERE hh.id IS NOT NULL;

WITH parsed AS (
  SELECT
    a.id AS application_id,
    lt.id AS template_id
  FROM loan_applications a
  INNER JOIN lending_pools op ON op.id = a.pool_id
  INNER JOIN lending_pools tp ON op.title LIKE 'Request - %' AND tp.title = substring(op.title from 11)
  INNER JOIN lending_templates lt ON lt.legacy_pool_id = tp.id
  WHERE a.template_id IS NULL
)
UPDATE loan_applications a
SET template_id = parsed.template_id
FROM parsed
WHERE a.id = parsed.application_id;

WITH missing AS (
  SELECT DISTINCT o.id AS opportunity_id
  FROM lending_opportunities o
  INNER JOIN loan_applications a ON a.opportunity_id = o.id
  WHERE a.template_id IS NULL
)
UPDATE lending_opportunities o
SET template_id = t.id
FROM lending_templates t
WHERE o.template_id IS NULL
  AND t.legacy_pool_id IS NOT NULL
  AND o.legacy_pool_id = t.legacy_pool_id;

WITH inserted AS (
  INSERT INTO lending_templates (
    source_opportunity_id,
    title,
    description,
    status,
    is_snapshot,
    min_commitment,
    min_request_amount,
    max_request_amount,
    borrower_appreciation_amount,
    borrower_term_months,
    repayment_frequency,
    processing_fee_pct,
    platform_appreciation_share_pct,
    min_appreciation_type,
    min_appreciation_value,
    min_appreciation_tiers,
    expected_return_pct,
    term_months,
    return_model,
    expected_return,
    term,
    min_borrower_plan_tier,
    borrower_type,
    created_at,
    updated_at
  )
  SELECT
    o.id,
    o.title,
    o.description,
    o.status,
    true,
    o.min_commitment,
    '1.00',
    o.target_amount,
    o.borrower_appreciation_amount,
    o.borrower_term_months,
    o.repayment_frequency,
    o.processing_fee_pct,
    o.platform_appreciation_share_pct,
    o.min_appreciation_type,
    o.min_appreciation_value,
    o.min_appreciation_tiers,
    o.expected_return_pct,
    o.term_months,
    o.return_model,
    o.expected_return,
    o.term,
    o.min_borrower_plan_tier,
    o.borrower_type,
    o.created_at,
    o.updated_at
  FROM lending_opportunities o
  WHERE o.template_id IS NULL
  RETURNING id, source_opportunity_id
)
UPDATE lending_opportunities o
SET template_id = inserted.id
FROM inserted
WHERE o.id = inserted.source_opportunity_id;

UPDATE loan_applications a
SET template_id = o.template_id
FROM lending_opportunities o
WHERE a.template_id IS NULL AND a.opportunity_id = o.id;

UPDATE lending_opportunities o
SET application_id = a.id
FROM loan_applications a
WHERE o.application_id IS NULL AND a.opportunity_id = o.id;

INSERT INTO lending_opportunity_return_events (
  legacy_return_event_id,
  opportunity_id,
  repayment_id,
  loan_application_id,
  wallet_transaction_id,
  gross_amount,
  principal_amount,
  return_amount,
  allocated_return_amount,
  occurred_at,
  created_at
)
SELECT
  e.id,
  o.id,
  e.repayment_id,
  e.loan_application_id,
  e.wallet_transaction_id,
  e.gross_amount,
  e.principal_amount,
  e.return_amount,
  e.allocated_return_amount,
  e.occurred_at,
  e.created_at
FROM lending_pool_return_events e
INNER JOIN lending_opportunities o ON o.legacy_pool_id = e.pool_id
ON CONFLICT (legacy_return_event_id) DO NOTHING;

INSERT INTO lending_opportunity_return_allocations (
  return_event_id,
  commitment_id,
  opportunity_id,
  user_id,
  commitment_amount_snapshot,
  ownership_pct_snapshot,
  allocated_return_amount,
  occurred_at,
  created_at
)
SELECT
  ne.id,
  a.commitment_id,
  o.id,
  a.user_id,
  a.commitment_amount_snapshot,
  a.ownership_pct_snapshot,
  a.allocated_return_amount,
  a.occurred_at,
  a.created_at
FROM lending_commitment_return_allocations a
INNER JOIN lending_opportunity_return_events ne ON ne.legacy_return_event_id = a.return_event_id
INNER JOIN lending_opportunities o ON o.legacy_pool_id = a.pool_id
ON CONFLICT DO NOTHING;

ALTER TABLE helping_hand_applications
DROP COLUMN IF EXISTS pool_id,
DROP COLUMN IF EXISTS pool_assigned_at,
DROP COLUMN IF EXISTS pool_assigned_by_admin_id;

ALTER TABLE loan_applications
DROP COLUMN IF EXISTS pool_id,
DROP COLUMN IF EXISTS pricing_source_pool_id;

ALTER TABLE lending_commitments
DROP COLUMN IF EXISTS pool_id;

DROP TABLE IF EXISTS lending_commitment_return_allocations;
DROP TABLE IF EXISTS lending_pool_return_events;
DROP TABLE IF EXISTS lending_pools;

DROP TABLE IF EXISTS tmp_lending_opportunity_pool_ids;
