-- Add email to participants for OAuth identity mapping
ALTER TABLE participants ADD COLUMN IF NOT EXISTS email TEXT UNIQUE;

-- Add owner_id and account_holder_name to statements
ALTER TABLE statements ADD COLUMN IF NOT EXISTS owner_id INTEGER NOT NULL DEFAULT 1 REFERENCES participants(id);
ALTER TABLE statements ADD COLUMN IF NOT EXISTS account_holder_name TEXT;
CREATE INDEX IF NOT EXISTS idx_statements_owner_id ON statements(owner_id);

-- Auto-assignment mapping table: (bank_name, account_number) -> owner
CREATE TABLE IF NOT EXISTS account_owner_mappings (
  id SERIAL PRIMARY KEY,
  bank_name TEXT NOT NULL,
  account_number TEXT NOT NULL,
  owner_id INTEGER NOT NULL REFERENCES participants(id),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(bank_name, account_number)
);