-- Investor Portal — a genuinely separate product from the Team Portal:
-- different audience (external investors, not staff), different login
-- (email+password, checked against this same investors table used by
-- the Finance dashboard already), no school_id (platform-level, same
-- tier as platform_admins/schools/team_projects).
--
-- Deliberately NOT included here: revenue-share rate, partnership term,
-- earned-to-date, or payout tracking — explicitly out of scope for now.
-- This only covers login, viewing real funding/commitment data (already
-- exists via investors/investment_opportunities/investor_commitments),
-- a shared document library, and a company updates feed.

ALTER TABLE investors ADD COLUMN IF NOT EXISTS email TEXT;
ALTER TABLE investors ADD COLUMN IF NOT EXISTS password_hash TEXT;
CREATE UNIQUE INDEX IF NOT EXISTS idx_investors_email ON investors (lower(email)) WHERE email IS NOT NULL;

-- A shared resource library (agreements, prospectus, pitch decks) every
-- logged-in investor can see — not assigned per-investor, since nothing
-- in the reference design implied personalized documents, just a
-- general "Documents" section.
CREATE TABLE IF NOT EXISTS investor_documents (
  id            SERIAL PRIMARY KEY,
  title         TEXT NOT NULL,
  file_path     TEXT NOT NULL,
  file_size_kb  INTEGER NOT NULL DEFAULT 0,
  uploaded_by   TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- "Latest Company Updates" feed — posted by Finance/lead_engineer via
-- the Team Portal, shown to every investor.
CREATE TABLE IF NOT EXISTS investor_updates (
  id            SERIAL PRIMARY KEY,
  title         TEXT NOT NULL,
  body          TEXT,
  posted_by     TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);