-- ============================================================
-- Migration 002: Partner Perks (Member Benefits from 3rd-party software partners)
-- ============================================================

CREATE TABLE IF NOT EXISTS partner_perks (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  partner_name VARCHAR(255) NOT NULL,
  partner_logo VARCHAR(500),
  partner_url VARCHAR(500) NOT NULL,
  category VARCHAR(100) NOT NULL DEFAULT 'General',
  headline VARCHAR(255) NOT NULL,
  description TEXT,
  promo_code VARCHAR(100),
  discount_details VARCHAR(500),
  is_exclusive BOOLEAN DEFAULT FALSE,
  valid_from DATE,
  valid_until DATE,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INT DEFAULT 0,
  created_by INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_active (is_active),
  INDEX idx_category (category),
  INDEX idx_sort (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS perk_claims (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  perk_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  org_id INT UNSIGNED,
  claimed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_claim (perk_id, user_id),
  FOREIGN KEY (perk_id) REFERENCES partner_perks(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed a few example perks so the page isn't empty on first launch
INSERT IGNORE INTO partner_perks (partner_name, partner_url, category, headline, description, promo_code, discount_details, is_exclusive, valid_until, sort_order)
VALUES
  ('Beezifi CRM', 'https://crm.beezifi.com', 'CRM', '3 months free on Beezifi CRM', 'Full-featured CRM built for small business. Manage contacts, deals, pipelines, and follow-ups — all in one place.', 'B2BCRM3FREE', '3 months free, then standard pricing applies', TRUE, DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 1),
  ('QuickBooks Online', 'https://quickbooks.intuit.com', 'Accounting', '30% off QuickBooks for 6 months', 'Award-winning accounting software trusted by millions of small businesses. Track income, expenses, invoices, and payroll.', 'B2BQBO30', '30% off for the first 6 months of a new subscription', TRUE, DATE_ADD(CURDATE(), INTERVAL 6 MONTH), 2),
  ('Mailchimp', 'https://mailchimp.com', 'Email Marketing', '15% off any paid Mailchimp plan', 'Industry-leading email marketing platform. Design beautiful campaigns, automate follow-ups, and grow your audience.', 'B2BMAIL15', '15% off monthly or annual subscription for new accounts', FALSE, DATE_ADD(CURDATE(), INTERVAL 9 MONTH), 3),
  ('Canva Pro', 'https://canva.com/pro', 'Design', '1 month Canva Pro free', 'Create stunning graphics, presentations, social posts, and marketing materials with Canva Pro. No design experience needed.', 'B2BCANVA1M', '1 month free trial of Canva Pro', TRUE, DATE_ADD(CURDATE(), INTERVAL 1 YEAR), 4),
  ('Zoom Business', 'https://zoom.us/pricing', 'Video Conferencing', '$50 off Zoom Business annual plan', 'Host unlimited meetings, webinars, and virtual events with Zoom Business. Includes cloud recording and admin controls.', 'B2BZOOM50', '$50 off the annual Business plan for new subscribers', FALSE, DATE_ADD(CURDATE(), INTERVAL 6 MONTH), 5);
