-- ============================================================
-- Migration 006: Stripe Connect (Member-to-Member Payments)
-- ============================================================

ALTER TABLE organizations
  ADD COLUMN IF NOT EXISTS stripe_connect_id VARCHAR(100) AFTER stripe_customer_id,
  ADD COLUMN IF NOT EXISTS stripe_connect_status ENUM('none','pending','active','restricted','disabled') DEFAULT 'none' AFTER stripe_connect_id,
  ADD COLUMN IF NOT EXISTS stripe_connect_onboarded_at DATETIME AFTER stripe_connect_status;

CREATE TABLE IF NOT EXISTS member_payments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  payer_org_id INT UNSIGNED NOT NULL,
  payer_user_id INT UNSIGNED NOT NULL,
  payee_org_id INT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  platform_fee DECIMAL(10,2) DEFAULT 0.00,
  currency VARCHAR(3) DEFAULT 'USD',
  description VARCHAR(500),
  reference VARCHAR(255),
  status ENUM('pending','completed','failed','refunded','disputed') DEFAULT 'pending',
  stripe_payment_intent VARCHAR(255),
  stripe_transfer_id VARCHAR(255),
  proposal_id INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (payer_org_id) REFERENCES organizations(id),
  FOREIGN KEY (payee_org_id) REFERENCES organizations(id),
  FOREIGN KEY (payer_user_id) REFERENCES users(id),
  FOREIGN KEY (proposal_id) REFERENCES proposals(id) ON DELETE SET NULL,
  INDEX idx_payer (payer_org_id),
  INDEX idx_payee (payee_org_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Update billing_transactions to accept referral_credit type
ALTER TABLE billing_transactions
  MODIFY COLUMN transaction_type ENUM('membership','event_ticket','addon','refund','adjustment','referral_credit','member_payment') DEFAULT 'membership';
