-- ============================================================
-- Migration 001: System Admin Features + Trial System + CRM
-- Run after base schema.sql
-- ============================================================

-- ── Trial fields on memberships ──────────────────────────────────────────
ALTER TABLE memberships
  ADD COLUMN IF NOT EXISTS is_trial BOOLEAN DEFAULT FALSE AFTER plan,
  ADD COLUMN IF NOT EXISTS trial_start DATE AFTER is_trial,
  ADD COLUMN IF NOT EXISTS trial_end DATE AFTER trial_start,
  ADD COLUMN IF NOT EXISTS trial_extended_days INT DEFAULT 0 AFTER trial_end,
  ADD COLUMN IF NOT EXISTS trial_extended_by INT UNSIGNED AFTER trial_extended_days,
  ADD COLUMN IF NOT EXISTS converted_from_trial BOOLEAN DEFAULT FALSE AFTER trial_extended_by;

-- ── 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;

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;

-- ── Platform 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;

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','crm','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;

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;

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 ───────────────────────────────────────────────────────────
-- (Tracks which users have admin access and their admin-specific details)
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 initial promo code ───────────────────────────────────────────────
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),
  ('CHAMBER2025', '$50 off annual membership', 'fixed', 50.00, 0, 200, '2025-12-31', TRUE);

-- ── Seed 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, CRM, events, press releases, and more. <a href="/member/billing">Activate your membership</a> before your trial ends to keep access.', 'info', 'trial', TRUE);
