-- ============================================================
-- Biz2Biz Chamber — Complete Database Schema
-- MariaDB / MySQL
-- Includes all migrations through 012
-- Run this on a fresh database to get a fully installed system.
-- ============================================================


-- ============================================================
-- CATEGORIES
-- ============================================================
CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  icon VARCHAR(100),
  parent_id INT UNSIGNED,
  is_active BOOLEAN DEFAULT TRUE,
  sort_order INT DEFAULT 0,
  INDEX idx_slug (slug),
  INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORGANIZATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS organizations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  logo VARCHAR(500),
  banner_image VARCHAR(500),
  description TEXT,
  short_description VARCHAR(500),
  website VARCHAR(500),
  email VARCHAR(255),
  phone VARCHAR(20),
  address VARCHAR(255),
  address2 VARCHAR(255),
  city VARCHAR(100),
  state VARCHAR(100),
  zip VARCHAR(20),
  country VARCHAR(100) DEFAULT 'USA',
  category_id INT UNSIGNED,
  subcategory_id INT UNSIGNED,
  keywords TEXT,
  tags JSON,
  social_links JSON,
  products_services JSON,
  year_established YEAR,
  employee_count ENUM('1-10','11-50','51-200','201-500','500+'),
  is_featured BOOLEAN DEFAULT FALSE,
  is_verified BOOLEAN DEFAULT FALSE,
  is_active BOOLEAN DEFAULT TRUE,
  rating DECIMAL(3,2) DEFAULT 0.00,
  review_count INT DEFAULT 0,
  directory_views INT DEFAULT 0,
  profile_completeness TINYINT DEFAULT 0,
  stripe_customer_id VARCHAR(255),
  stripe_connect_id VARCHAR(100),
  stripe_connect_status ENUM('none','pending','active','restricted','disabled') DEFAULT 'none',
  stripe_connect_onboarded_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_slug (slug),
  INDEX idx_owner (owner_id),
  INDEX idx_category (category_id),
  INDEX idx_featured (is_featured),
  INDEX idx_active (is_active),
  FULLTEXT idx_search (name, description, keywords)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- MEMBERSHIPS
-- ============================================================
CREATE TABLE IF NOT EXISTS memberships (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  plan VARCHAR(50) DEFAULT 'annual',
  is_trial BOOLEAN DEFAULT FALSE,
  trial_start DATE,
  trial_end DATE,
  trial_extended_days INT DEFAULT 0,
  trial_extended_by INT UNSIGNED,
  converted_from_trial BOOLEAN DEFAULT FALSE,
  amount DECIMAL(10,2) DEFAULT 299.00,
  status ENUM('active','expired','cancelled','suspended','pending','trial') DEFAULT 'pending',
  start_date DATE,
  end_date DATE,
  auto_renew BOOLEAN DEFAULT TRUE,
  stripe_subscription_id VARCHAR(255),
  stripe_customer_id VARCHAR(255),
  stripe_price_id VARCHAR(255),
  renewal_reminder_sent BOOLEAN DEFAULT FALSE,
  cancelled_at DATETIME,
  cancel_reason TEXT,
  suspended_at DATETIME,
  suspend_reason TEXT,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_org (org_id),
  INDEX idx_user (user_id),
  INDEX idx_status (status),
  INDEX idx_end_date (end_date),
  INDEX idx_stripe_sub (stripe_subscription_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EVENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS events (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED,
  created_by INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(300) NOT NULL UNIQUE,
  description TEXT,
  short_description VARCHAR(500),
  event_type ENUM('in-person','virtual','hybrid','webinar','networking','workshop','conference') DEFAULT 'in-person',
  start_date DATETIME NOT NULL,
  end_date DATETIME NOT NULL,
  timezone VARCHAR(100) DEFAULT 'America/Chicago',
  location VARCHAR(500),
  location_address VARCHAR(500),
  location_city VARCHAR(100),
  location_state VARCHAR(100),
  virtual_link VARCHAR(500),
  virtual_platform VARCHAR(100),
  capacity INT,
  registered_count INT DEFAULT 0,
  waitlist_count INT DEFAULT 0,
  ticket_price DECIMAL(10,2) DEFAULT 0.00,
  early_bird_price DECIMAL(10,2),
  early_bird_deadline DATETIME,
  is_free BOOLEAN DEFAULT TRUE,
  banner_image VARCHAR(500),
  tags JSON,
  category_id INT UNSIGNED,
  status ENUM('draft','published','cancelled','completed','postponed') DEFAULT 'draft',
  registration_deadline DATETIME,
  is_members_only BOOLEAN DEFAULT FALSE,
  stripe_product_id VARCHAR(255),
  stripe_price_id VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL,
  FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_start_date (start_date),
  INDEX idx_status (status),
  INDEX idx_created_by (created_by),
  INDEX idx_org (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EVENT REGISTRATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS event_registrations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  org_id INT UNSIGNED,
  ticket_type VARCHAR(100) DEFAULT 'general',
  quantity INT DEFAULT 1,
  unit_price DECIMAL(10,2) DEFAULT 0.00,
  amount_paid DECIMAL(10,2) DEFAULT 0.00,
  status ENUM('registered','cancelled','waitlist','checked_in','no_show') DEFAULT 'registered',
  qr_code VARCHAR(500),
  qr_code_data TEXT,
  checked_in BOOLEAN DEFAULT FALSE,
  checked_in_at DATETIME,
  payment_intent_id VARCHAR(255),
  confirmation_number VARCHAR(50),
  attendee_info JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_registration (event_id, user_id),
  INDEX idx_event (event_id),
  INDEX idx_user (user_id),
  INDEX idx_status (status),
  INDEX idx_qr (qr_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BULLETIN POSTS
-- ============================================================
CREATE TABLE IF NOT EXISTS bulletin_posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  category ENUM('announcement','opportunity','partnership','vendor','job','promotion','general') DEFAULT 'announcement',
  attachments JSON,
  image VARCHAR(500),
  likes_count INT DEFAULT 0,
  comments_count INT DEFAULT 0,
  views INT DEFAULT 0,
  is_pinned BOOLEAN DEFAULT FALSE,
  is_featured BOOLEAN DEFAULT FALSE,
  status ENUM('active','removed','pending_review') DEFAULT 'active',
  expires_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_org (org_id),
  INDEX idx_category (category),
  INDEX idx_status (status),
  FULLTEXT idx_search (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BULLETIN COMMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS bulletin_comments (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  content TEXT NOT NULL,
  parent_id INT UNSIGNED,
  status ENUM('active','removed') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES bulletin_posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_post (post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BULLETIN LIKES
-- ============================================================
CREATE TABLE IF NOT EXISTS bulletin_likes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  post_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (post_id) REFERENCES bulletin_posts(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  UNIQUE KEY unique_like (post_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PRESS RELEASES
-- ============================================================
CREATE TABLE IF NOT EXISTS press_releases (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  content LONGTEXT NOT NULL,
  excerpt TEXT,
  image VARCHAR(500),
  contact_name VARCHAR(255),
  contact_email VARCHAR(255),
  contact_phone VARCHAR(20),
  status ENUM('draft','submitted','published','distributed','rejected','archived') DEFAULT 'draft',
  views INT DEFAULT 0,
  shares INT DEFAULT 0,
  published_at DATETIME,
  distributed_at DATETIME,
  distribution_channels JSON,
  rejection_reason TEXT,
  admin_notes TEXT,
  external_id VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_org (org_id),
  INDEX idx_status (status),
  INDEX idx_published (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SOCIAL SUBMISSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS social_submissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  submission_type ENUM('news','product','event','announcement','promotion') DEFAULT 'news',
  title VARCHAR(255),
  content TEXT NOT NULL,
  image VARCHAR(500),
  link VARCHAR(500),
  hashtags JSON,
  scheduled_at DATETIME,
  published_at DATETIME,
  status ENUM('pending','approved','published','rejected','scheduled') DEFAULT 'pending',
  platforms JSON,
  likes INT DEFAULT 0,
  shares INT DEFAULT 0,
  clicks INT DEFAULT 0,
  impressions INT DEFAULT 0,
  rejection_reason TEXT,
  reviewed_by INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_org (org_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LOYALTY PROGRAMS
-- ============================================================
CREATE TABLE IF NOT EXISTS loyalty_programs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  external_id VARCHAR(255),
  moochedda_token_symbol VARCHAR(50),
  moochedda_token_name VARCHAR(255),
  moochedda_token_supply BIGINT DEFAULT 0,
  moochedda_token_id VARCHAR(255),
  moochedda_token_created_at DATETIME,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  program_type ENUM('points','stamps','coupons','referral','tiered') DEFAULT 'points',
  points_per_dollar DECIMAL(5,2) DEFAULT 1.00,
  status ENUM('active','paused','ended') DEFAULT 'active',
  settings JSON,
  total_customers INT DEFAULT 0,
  total_points_issued BIGINT DEFAULT 0,
  total_redemptions INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  INDEX idx_org (org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LOYALTY REWARDS
-- ============================================================
CREATE TABLE IF NOT EXISTS loyalty_rewards (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  program_id INT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  points_required INT DEFAULT 0,
  reward_type ENUM('discount','freebie','service','cashback','gift_card') DEFAULT 'discount',
  value DECIMAL(10,2),
  discount_percent DECIMAL(5,2),
  quantity_available INT,
  quantity_redeemed INT DEFAULT 0,
  expiry_date DATE,
  image VARCHAR(500),
  status ENUM('active','inactive','expired') DEFAULT 'active',
  moochedda_product_id VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (program_id) REFERENCES loyalty_programs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LOYALTY CUSTOMERS
-- ============================================================
CREATE TABLE IF NOT EXISTS loyalty_customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  program_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  phone VARCHAR(20),
  points_balance INT DEFAULT 0,
  total_points_earned INT DEFAULT 0,
  total_points_redeemed INT DEFAULT 0,
  tier ENUM('bronze','silver','gold','platinum') DEFAULT 'bronze',
  referral_code VARCHAR(50),
  referred_by INT UNSIGNED,
  moochedda_address VARCHAR(200),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (program_id) REFERENCES loyalty_programs(id) ON DELETE CASCADE,
  UNIQUE KEY unique_customer (program_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- LOYALTY TRANSACTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS loyalty_transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  program_id INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  transaction_type ENUM('earn','redeem','expire','adjust','bonus') DEFAULT 'earn',
  points INT NOT NULL,
  balance_after INT,
  description VARCHAR(500),
  reference_id VARCHAR(255),
  created_by INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (program_id) REFERENCES loyalty_programs(id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES loyalty_customers(id) ON DELETE CASCADE,
  INDEX idx_program (program_id),
  INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AGREEMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS agreements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  created_by INT UNSIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  agreement_type ENUM('NDA','service','vendor','employment','partnership','lease','custom') DEFAULT 'custom',
  content LONGTEXT NOT NULL,
  status ENUM('draft','pending_signature','partially_signed','signed','expired','cancelled','rejected') DEFAULT 'draft',
  parties JSON,
  template_id INT UNSIGNED,
  expires_at DATE,
  effective_date DATE,
  signed_at DATETIME,
  signature_deadline DATETIME,
  external_id VARCHAR(255),
  file_path VARCHAR(500),
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by) REFERENCES users(id),
  INDEX idx_org (org_id),
  INDEX idx_status (status),
  INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AGREEMENT TEMPLATES
-- ============================================================
CREATE TABLE IF NOT EXISTS agreement_templates (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by INT UNSIGNED,
  org_id INT UNSIGNED,
  title VARCHAR(255) NOT NULL,
  template_type ENUM('NDA','service','vendor','employment','partnership','custom') DEFAULT 'custom',
  content LONGTEXT NOT NULL,
  is_public BOOLEAN DEFAULT FALSE,
  usage_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- MESSAGES
-- ============================================================
CREATE TABLE IF NOT EXISTS messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sender_id INT UNSIGNED NOT NULL,
  recipient_id INT UNSIGNED NOT NULL,
  subject VARCHAR(255),
  content TEXT NOT NULL,
  is_read BOOLEAN DEFAULT FALSE,
  read_at DATETIME,
  is_deleted_sender BOOLEAN DEFAULT FALSE,
  is_deleted_recipient BOOLEAN DEFAULT FALSE,
  thread_id INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (sender_id) REFERENCES users(id),
  FOREIGN KEY (recipient_id) REFERENCES users(id),
  INDEX idx_recipient (recipient_id, is_read),
  INDEX idx_sender (sender_id),
  INDEX idx_thread (thread_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- DISCUSSION GROUPS
-- ============================================================
CREATE TABLE IF NOT EXISTS discussion_groups (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  created_by INT UNSIGNED NOT NULL,
  org_id INT UNSIGNED,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT,
  category VARCHAR(100),
  banner_image VARCHAR(500),
  is_public BOOLEAN DEFAULT TRUE,
  is_moderated BOOLEAN DEFAULT FALSE,
  member_count INT DEFAULT 0,
  post_count INT DEFAULT 0,
  status ENUM('active','archived') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL,
  INDEX idx_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- GROUP MEMBERS
-- ============================================================
CREATE TABLE IF NOT EXISTS group_members (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  member_role ENUM('owner','admin','moderator','member') DEFAULT 'member',
  joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (group_id) REFERENCES discussion_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_member (group_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- GROUP POSTS
-- ============================================================
CREATE TABLE IF NOT EXISTS group_posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  content TEXT NOT NULL,
  image VARCHAR(500),
  likes_count INT DEFAULT 0,
  comments_count INT DEFAULT 0,
  is_pinned BOOLEAN DEFAULT FALSE,
  status ENUM('active','removed','pending_review') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (group_id) REFERENCES discussion_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_group (group_id),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- REVIEWS
-- ============================================================
CREATE TABLE IF NOT EXISTS reviews (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED NOT NULL,
  reviewer_id INT UNSIGNED,
  reviewer_org_id INT UNSIGNED,
  title VARCHAR(255),
  reviewer_name VARCHAR(255),
  reviewer_email VARCHAR(255),
  reviewer_company VARCHAR(255),
  source ENUM('member','public') DEFAULT 'member',
  moderation_notes TEXT,
  rating TINYINT NOT NULL,
  content TEXT,
  response TEXT,
  responded_at DATETIME,
  status ENUM('active','removed','pending_review') DEFAULT 'active',
  helpful_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (reviewer_id) REFERENCES users(id),
  UNIQUE KEY unique_member_review (org_id, reviewer_id),
  INDEX idx_status_source (status, source),
  CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BILLING TRANSACTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS billing_transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED,
  user_id INT UNSIGNED NOT NULL,
  transaction_type ENUM('membership','event_ticket','addon','refund','adjustment','referral_credit','member_payment') DEFAULT 'membership',
  amount DECIMAL(10,2) NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD',
  status ENUM('pending','completed','failed','refunded','disputed') DEFAULT 'pending',
  payment_method VARCHAR(100),
  stripe_payment_intent VARCHAR(255),
  stripe_charge_id VARCHAR(255),
  stripe_invoice_id VARCHAR(255),
  description VARCHAR(500),
  metadata JSON,
  refunded_at DATETIME,
  refund_amount DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_org (org_id),
  INDEX idx_user (user_id),
  INDEX idx_status (status),
  INDEX idx_type (transaction_type),
  INDEX idx_stripe_pi (stripe_payment_intent)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- MEMBER PAYMENTS (Stripe Connect)
-- ============================================================
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;

-- ============================================================
-- TOKEN GRANTS (MooChedda B2B Wallet)
-- ============================================================
CREATE TABLE IF NOT EXISTS token_grants (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  from_address VARCHAR(200) NOT NULL,
  to_address VARCHAR(200) NOT NULL,
  token_symbol VARCHAR(20) NOT NULL,
  amount DECIMAL(18,6) NOT NULL,
  reason VARCHAR(255),
  tx_hash VARCHAR(255),
  status ENUM('pending','completed','failed') DEFAULT 'pending',
  error_message TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PARTNER PERKS
-- ============================================================
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),
  token_price DECIMAL(18,0) DEFAULT 0,
  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;

-- ============================================================
-- PERK CLAIMS
-- ============================================================
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;

-- ============================================================
-- MEMBER REFERRALS
-- ============================================================
CREATE TABLE IF NOT EXISTS member_referrals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  referrer_user_id INT UNSIGNED NOT NULL,
  referred_user_id INT UNSIGNED NOT NULL UNIQUE,
  status ENUM('pending','converted','expired') DEFAULT 'pending',
  referral_credit DECIMAL(10,2) DEFAULT 25.00,
  credited_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_referrer (referrer_user_id),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- NOTIFICATIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS notifications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  notification_type VARCHAR(100) NOT NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT,
  link VARCHAR(500),
  data JSON,
  is_read BOOLEAN DEFAULT FALSE,
  read_at DATETIME,
  email_sent BOOLEAN DEFAULT FALSE,
  sms_sent BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_unread (user_id, is_read),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AUDIT LOGS
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED,
  action VARCHAR(100) NOT NULL,
  entity VARCHAR(100),
  entity_id INT UNSIGNED,
  old_values JSON,
  new_values JSON,
  details JSON,
  ip_address VARCHAR(50),
  user_agent TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_action (action),
  INDEX idx_entity (entity, entity_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- FAVORITES
-- ============================================================
CREATE TABLE IF NOT EXISTS favorites (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  org_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  UNIQUE KEY unique_favorite (user_id, org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- BUSINESS CONNECTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS business_connections (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  requester_org_id INT UNSIGNED NOT NULL,
  target_org_id INT UNSIGNED NOT NULL,
  connection_type ENUM('vendor','customer','partner','referral','general') DEFAULT 'general',
  message TEXT,
  status ENUM('pending','accepted','declined','withdrawn') DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (requester_org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  FOREIGN KEY (target_org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  UNIQUE KEY unique_connection (requester_org_id, target_org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CONTACT FORM SUBMISSIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS contact_submissions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(20),
  company VARCHAR(255),
  subject VARCHAR(255),
  message TEXT NOT NULL,
  status ENUM('new','read','replied','archived') DEFAULT 'new',
  ip_address VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TESTIMONIALS
-- ============================================================
CREATE TABLE IF NOT EXISTS testimonials (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  org_id INT UNSIGNED,
  name VARCHAR(255) NOT NULL,
  title VARCHAR(255),
  company VARCHAR(255),
  content TEXT NOT NULL,
  rating TINYINT DEFAULT 5,
  avatar VARCHAR(500),
  is_featured BOOLEAN DEFAULT FALSE,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SYSTEM SETTINGS
-- ============================================================
CREATE TABLE IF NOT EXISTS system_settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT,
  setting_type ENUM('string','number','boolean','json') DEFAULT 'string',
  category VARCHAR(50) DEFAULT 'general',
  label VARCHAR(255),
  description VARCHAR(500),
  is_encrypted BOOLEAN DEFAULT FALSE,
  updated_by INT UNSIGNED,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_key (setting_key),
  INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PROMO CODES
-- ============================================================
CREATE TABLE IF NOT EXISTS promo_codes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(500),
  discount_type ENUM('percent','fixed','trial_extension','free_month') DEFAULT 'percent',
  discount_value DECIMAL(10,2) NOT NULL DEFAULT 0,
  trial_extension_days INT DEFAULT 0,
  max_uses INT,
  uses_count INT DEFAULT 0,
  min_purchase DECIMAL(10,2) DEFAULT 0,
  valid_from DATE,
  valid_until DATE,
  is_active BOOLEAN DEFAULT TRUE,
  created_by INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_code (code),
  INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PROMO REDEMPTIONS
-- ============================================================
CREATE TABLE IF NOT EXISTS promo_redemptions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  promo_id INT UNSIGNED NOT NULL,
  org_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  discount_amount DECIMAL(10,2),
  redeemed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (promo_id) REFERENCES promo_codes(id),
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
  UNIQUE KEY unique_redemption (promo_id, org_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ANNOUNCEMENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS announcements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  announcement_type ENUM('info','success','warning','danger','feature') DEFAULT 'info',
  target ENUM('all','members','admin','trial') DEFAULT 'all',
  is_dismissible BOOLEAN DEFAULT TRUE,
  starts_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  ends_at DATETIME,
  is_active BOOLEAN DEFAULT TRUE,
  created_by INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_active_target (is_active, target)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ANNOUNCEMENT DISMISSALS
-- ============================================================
CREATE TABLE IF NOT EXISTS announcement_dismissals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  announcement_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  dismissed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (announcement_id) REFERENCES announcements(id) ON DELETE CASCADE,
  UNIQUE KEY unique_dismiss (announcement_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SUPPORT TICKETS
-- ============================================================
CREATE TABLE IF NOT EXISTS support_tickets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_number VARCHAR(20) NOT NULL UNIQUE,
  org_id INT UNSIGNED,
  user_id INT UNSIGNED,
  subject VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  category ENUM('billing','technical','membership','general','feature_request') DEFAULT 'general',
  priority ENUM('low','medium','high','urgent') DEFAULT 'medium',
  status ENUM('open','in_progress','waiting_on_customer','resolved','closed') DEFAULT 'open',
  assigned_to INT UNSIGNED,
  satisfaction_rating TINYINT,
  resolved_at DATETIME,
  closed_at DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_status (status),
  INDEX idx_priority (priority),
  INDEX idx_assigned (assigned_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SUPPORT REPLIES
-- ============================================================
CREATE TABLE IF NOT EXISTS support_replies (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  content TEXT NOT NULL,
  is_staff_reply BOOLEAN DEFAULT FALSE,
  is_internal_note BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id),
  INDEX idx_ticket (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- EMAIL CAMPAIGNS
-- ============================================================
CREATE TABLE IF NOT EXISTS email_campaigns (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  content LONGTEXT NOT NULL,
  target_audience ENUM('all_members','active_members','trial_members','expired_members','pending_members','newsletter') DEFAULT 'all_members',
  status ENUM('draft','scheduled','sending','sent','cancelled') DEFAULT 'draft',
  scheduled_at DATETIME,
  sent_at DATETIME,
  recipient_count INT DEFAULT 0,
  sent_count INT DEFAULT 0,
  open_count INT DEFAULT 0,
  click_count INT DEFAULT 0,
  created_by INT UNSIGNED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CAMPAIGN RECIPIENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS campaign_recipients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  campaign_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED,
  email VARCHAR(255) NOT NULL,
  status ENUM('pending','sent','failed','opened','clicked','unsubscribed') DEFAULT 'pending',
  sent_at DATETIME,
  opened_at DATETIME,
  FOREIGN KEY (campaign_id) REFERENCES email_campaigns(id) ON DELETE CASCADE,
  INDEX idx_campaign (campaign_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ADMIN STAFF
-- ============================================================
CREATE TABLE IF NOT EXISTS admin_staff (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL UNIQUE,
  department VARCHAR(100),
  admin_notes TEXT,
  permissions JSON,
  last_admin_action DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- SEED DATA
-- ============================================================

-- System settings
INSERT IGNORE INTO system_settings (setting_key, setting_value, setting_type, category, label, description) VALUES
('trial_duration_days',   '30',                        'number',  'membership', 'Trial Duration (days)',    'Number of days for the free trial period'),
('trial_enabled',         'true',                      'boolean', 'membership', 'Free Trial Enabled',       'Enable 30-day free trial for new registrations'),
('membership_price',      '299.00',                    'number',  'membership', 'Membership Price ($)',      'Annual membership fee in USD'),
('auto_approve_members',  'false',                     'boolean', 'membership', 'Auto-Approve Members',     'Automatically approve new member registrations'),
('maintenance_mode',      'false',                     'boolean', 'general',    'Maintenance Mode',         'Put the site in maintenance mode'),
('registration_open',     'true',                      'boolean', 'general',    'Registration Open',        'Allow new member registrations'),
('max_file_upload_mb',    '10',                        'number',  'general',    'Max Upload Size (MB)',      'Maximum file upload size in megabytes'),
('site_name',             'Biz2Biz Chamber',           'string',  'general',    'Site Name',                'Platform display name'),
('site_url',              'https://biz2bizchamber.com','string',  'general',    'Site URL',                 'Public-facing URL of the platform');

-- Starter promo codes
INSERT IGNORE INTO promo_codes (code, description, discount_type, discount_value, trial_extension_days, max_uses, valid_until, is_active) VALUES
('WELCOME30', 'Welcome promo — 30-day extended trial',  'trial_extension', 0,     30, 500, DATE_ADD(CURDATE(), INTERVAL 1 YEAR),  TRUE),
('LAUNCH50',  'Launch special — 50% off first year',    'percent',         50.00,  0, 100, DATE_ADD(CURDATE(), INTERVAL 3 MONTH), TRUE),
('CHAMBER25', '$25 off annual membership',              'fixed',           25.00,  0, 200, DATE_ADD(CURDATE(), INTERVAL 1 YEAR),  TRUE);

-- Welcome announcement
INSERT IGNORE INTO announcements (title, content, announcement_type, target, is_active) VALUES
('Welcome to Biz2Biz Chamber!',
 'Your account includes a <strong>30-day free trial</strong>. Explore all features — directory, events, press releases, and more. <a href="/member/billing">Activate your membership</a> before your trial ends to keep access.',
 'info', 'trial', TRUE);

-- Partner perks (Beezifi, Torvali, JozieAI)
INSERT IGNORE INTO partner_perks (partner_name, partner_url, category, headline, description, token_price, is_exclusive, is_active, sort_order) VALUES
('Beezifi', 'https://workspace.beezifi.com',   'Beezifi',  'Workspace',         'Messaging, collaboration, and team productivity in one connected hub.',                                        500, TRUE, TRUE, 10),
('Beezifi', 'https://accounting.beezifi.com',  'Beezifi',  'Accounting',        'Financial management, invoicing, and reporting built for modern businesses.',                                  500, TRUE, TRUE, 11),
('Beezifi', 'https://crm.beezifi.com',         'Beezifi',  'CRM',               'Customer relationship management with AI-powered insights and automation.',                                    500, TRUE, TRUE, 12),
('Beezifi', 'https://grc.beezifi.com',         'Beezifi',  'GRC',               'Governance, risk, and compliance management with built-in audit trails.',                                      500, TRUE, TRUE, 13),
('Beezifi', 'https://dataroom.beezifi.com',    'Beezifi',  'DataRoom',          'Secure document storage and deal room for sensitive business data.',                                           500, TRUE, TRUE, 14),
('Beezifi', 'https://newswire.beezifi.com',    'Beezifi',  'Newswire',          'Self-serve press release publishing with public newsroom distribution.',                                       500, TRUE, TRUE, 15),
('Beezifi', 'https://iom.beezifi.com',         'Beezifi',  'IOM',               'Inventory and order management with warehouse execution and accounting sync.',                                 500, TRUE, TRUE, 16),
('Beezifi', 'https://storage.beezifi.com',     'Beezifi',  'Storage',           'Private S3-compatible object storage with no egress fees and full data sovereignty.',                          500, TRUE, TRUE, 17),
('Beezifi', 'https://agreement.beezifi.com',   'Beezifi',  'Agreement',         'AI-powered e-signature and document workflow management at scale.',                                            500, TRUE, TRUE, 18),
('Beezifi', 'https://identity.beezifi.com',    'Beezifi',  'Identity',          'Complete identity and access management with SSO, MFA, and RBAC.',                                            500, TRUE, TRUE, 19),
('Beezifi', 'https://security.beezifi.com',    'Beezifi',  'Security',          'Cybersecurity and compliance infrastructure with threat monitoring and encryption.',                           500, TRUE, TRUE, 20),
('Torvali', 'https://torvali.com/signup',      'Torvali',  'Online Store',      'A beautiful, fully-featured online store with drag-and-drop builder, payments, and analytics.',               500, TRUE, TRUE, 30),
('Torvali', 'https://torvali.com/marketplace', 'Torvali',  'Theme Marketplace', 'Browse and install themes crafted by independent designers with one-click application.',                      500, TRUE, TRUE, 31),
('Torvali', 'https://torvali.com/designer',    'Torvali',  'Designer Program',  'Submit CSS themes and earn revenue from merchant installations — no hosting required.',                        500, TRUE, TRUE, 32),
('Companions by JozieAI', 'https://companions.jozieai.com', 'AI Tools', 'Companions', 'AI-powered business assistants that automate customer interactions and support your team around the clock.', 500, TRUE, TRUE, 50);
