ALTER TABLE lending_templates
  ADD COLUMN IF NOT EXISTS organization_id integer REFERENCES organizations(id);

ALTER TABLE lending_opportunities
  ADD COLUMN IF NOT EXISTS organization_id integer REFERENCES organizations(id);

ALTER TABLE loan_applications
  ADD COLUMN IF NOT EXISTS organization_id integer REFERENCES organizations(id);

CREATE INDEX IF NOT EXISTS idx_lending_templates_org_status_created
  ON lending_templates (organization_id, status, created_at);

CREATE INDEX IF NOT EXISTS idx_lending_opps_org_status_created
  ON lending_opportunities (organization_id, status, created_at);

CREATE INDEX IF NOT EXISTS idx_loan_apps_org_status_created
  ON loan_applications (organization_id, status, created_at);

CREATE TABLE IF NOT EXISTS org_lending_pools (
  id serial PRIMARY KEY,
  organization_id integer NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  template_id integer REFERENCES lending_templates(id),
  name text NOT NULL,
  description text,
  eligibility_mode text NOT NULL DEFAULT 'CONTRIBUTORS_ONLY'
    CHECK (eligibility_mode IN ('CONTRIBUTORS_ONLY', 'ALL_ORG_MEMBERS')),
  status text NOT NULL DEFAULT 'DRAFT'
    CHECK (status IN ('DRAFT', 'OPEN', 'CLOSED')),
  min_request_amount numeric(10,2) NOT NULL DEFAULT '500.00',
  max_request_amount numeric(12,2) NOT NULL DEFAULT '25000.00',
  default_term_months integer NOT NULL DEFAULT 12,
  default_repayment_frequency lending_repayment_frequency_enum NOT NULL DEFAULT 'MONTHLY',
  default_borrower_appreciation_pct numeric(5,2) NOT NULL DEFAULT '0.00',
  default_processing_fee_pct numeric(5,2) NOT NULL DEFAULT '0.00',
  notes text,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS org_lending_pools_org_status_created_idx
  ON org_lending_pools (organization_id, status, created_at);

CREATE UNIQUE INDEX IF NOT EXISTS org_lending_pools_org_template_uniq_idx
  ON org_lending_pools (organization_id, template_id)
  WHERE template_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS org_lending_pool_funder_uses (
  id serial PRIMARY KEY,
  organization_id integer NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  pool_id integer NOT NULL REFERENCES org_lending_pools(id) ON DELETE CASCADE,
  loan_application_id integer NOT NULL REFERENCES loan_applications(id) ON DELETE CASCADE,
  asca_id integer NOT NULL REFERENCES ascas(id) ON DELETE CASCADE,
  funded_amount numeric(12,2) NOT NULL,
  created_at timestamp NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS org_lending_pool_funder_uses_org_pool_app_asca_uq
  ON org_lending_pool_funder_uses (organization_id, pool_id, loan_application_id, asca_id);

CREATE INDEX IF NOT EXISTS org_lending_pool_funder_uses_org_created_idx
  ON org_lending_pool_funder_uses (organization_id, created_at);

CREATE INDEX IF NOT EXISTS org_lending_pool_funder_uses_pool_created_idx
  ON org_lending_pool_funder_uses (pool_id, created_at);

CREATE INDEX IF NOT EXISTS org_lending_pool_funder_uses_asca_idx
  ON org_lending_pool_funder_uses (asca_id);

CREATE INDEX IF NOT EXISTS org_lending_pool_funder_uses_loan_app_idx
  ON org_lending_pool_funder_uses (loan_application_id);
