-- Phase 2: advertiser portal, Stripe payments, vendor reviews, benchmarks, knowledge vault, regulatory radar, threat intel
SET NAMES utf8mb4;

ALTER TABLE users MODIFY role ENUM('platform','chapter','member','advertiser') NOT NULL DEFAULT 'member';
ALTER TABLE users ADD COLUMN advertiser_id INT NULL AFTER chapter_id;

ALTER TABLE currencies ADD COLUMN usd_rate DECIMAL(18,8) NOT NULL DEFAULT 1 COMMENT 'units of this currency per 1 USD';
UPDATE currencies SET usd_rate = CASE code
  WHEN 'USD' THEN 1 WHEN 'EUR' THEN 0.92 WHEN 'GBP' THEN 0.78 WHEN 'AED' THEN 3.6725 WHEN 'SAR' THEN 3.75 WHEN 'INR' THEN 83.5
  WHEN 'KES' THEN 129 WHEN 'NGN' THEN 1550 WHEN 'ZAR' THEN 18.3 WHEN 'EGP' THEN 48.5 WHEN 'MXN' THEN 18.5 WHEN 'BRL' THEN 5.4
  WHEN 'SGD' THEN 1.34 WHEN 'AUD' THEN 1.52 WHEN 'CAD' THEN 1.37 ELSE 1 END;

CREATE TABLE vendors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(180) NOT NULL UNIQUE,
  website VARCHAR(255) NOT NULL DEFAULT '',
  category ENUM('his_ehr','erp','lis_ris_pacs','cloud_infra','cybersecurity','networking','telehealth','analytics_ai','rcm_billing','pharmacy','devices_iot','consulting','other') NOT NULL DEFAULT 'other',
  description VARCHAR(600) NOT NULL DEFAULT '',
  hq_country CHAR(2) NULL,
  status ENUM('pending','active','hidden') NOT NULL DEFAULT 'pending',
  suggested_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_vendor_cat (status, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE vendor_reviews (
  id INT AUTO_INCREMENT PRIMARY KEY,
  vendor_id INT NOT NULL,
  user_id INT NOT NULL,
  product VARCHAR(160) NOT NULL,
  go_live_year SMALLINT NOT NULL,
  r_implementation TINYINT NOT NULL,
  r_support TINYINT NOT NULL,
  r_pricing TINYINT NOT NULL COMMENT 'pricing honesty / no surprises',
  r_product TINYINT NOT NULL,
  would_buy_again TINYINT(1) NOT NULL,
  pros TEXT NOT NULL,
  cons TEXT NOT NULL,
  advice TEXT NULL,
  show_name TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('published','removed') NOT NULL DEFAULT 'published',
  vendor_reply TEXT NULL,
  vendor_reply_at DATETIME NULL,
  vendor_reply_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_review (vendor_id, user_id, product),
  FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE advertisers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  website VARCHAR(255) NOT NULL,
  country_iso2 CHAR(2) NULL,
  billing_email VARCHAR(190) NOT NULL,
  tax_id VARCHAR(60) NOT NULL DEFAULT '',
  billing_address VARCHAR(500) NOT NULL DEFAULT '',
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  vendor_id INT NULL COMMENT 'links to vendors for right-to-reply',
  status ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
  review_note VARCHAR(500) NOT NULL DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ad_rates (
  slot_code VARCHAR(40) NOT NULL,
  model ENUM('week','cpm','cpc') NOT NULL,
  currency CHAR(3) NOT NULL,
  price_minor BIGINT NOT NULL COMMENT 'price per week, per 1000 views, or per click, in minor units',
  PRIMARY KEY (slot_code, model, currency),
  FOREIGN KEY (slot_code) REFERENCES ad_slots(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE ad_campaigns
  ADD COLUMN advertiser_id INT NULL AFTER id,
  ADD COLUMN pricing_model ENUM('house','week','cpm','cpc') NOT NULL DEFAULT 'house',
  ADD COLUMN units INT NOT NULL DEFAULT 0 COMMENT 'weeks, thousands of views, or clicks bought',
  ADD COLUMN price_minor BIGINT NOT NULL DEFAULT 0,
  ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD',
  ADD COLUMN total_minor BIGINT NOT NULL DEFAULT 0,
  ADD COLUMN payment_status ENUM('not_required','unpaid','paid','refunded') NOT NULL DEFAULT 'not_required',
  ADD COLUMN review_status ENUM('approved','pending','rejected') NOT NULL DEFAULT 'approved',
  ADD COLUMN review_note VARCHAR(500) NOT NULL DEFAULT '',
  ADD COLUMN segments VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'comma list of org types, blank = all',
  ADD CONSTRAINT fk_camp_adv FOREIGN KEY (advertiser_id) REFERENCES advertisers(id) ON DELETE CASCADE;

ALTER TABLE events ADD COLUMN price_minor BIGINT NOT NULL DEFAULT 0, ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD';
ALTER TABLE event_rsvps ADD COLUMN payment_id INT NULL;

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_no VARCHAR(30) NULL UNIQUE,
  kind ENUM('ad','event') NOT NULL,
  ref_id INT NOT NULL,
  user_id INT NOT NULL,
  advertiser_id INT NULL,
  description VARCHAR(300) NOT NULL,
  amount_minor BIGINT NOT NULL,
  currency CHAR(3) NOT NULL,
  provider VARCHAR(20) NOT NULL DEFAULT 'stripe',
  session_id VARCHAR(255) NULL UNIQUE,
  payment_intent VARCHAR(255) NOT NULL DEFAULT '',
  status ENUM('pending','paid','failed','expired','refunded') NOT NULL DEFAULT 'pending',
  paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  KEY idx_pay_ref (kind, ref_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE stripe_events (
  id VARCHAR(255) PRIMARY KEY,
  type VARCHAR(100) NOT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE benchmark_metrics (
  code VARCHAR(40) PRIMARY KEY,
  unit ENUM('percent','number','usd') NOT NULL,
  min_value DECIMAL(18,4) NOT NULL DEFAULT 0,
  max_value DECIMAL(18,4) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE benchmark_points (
  id INT AUTO_INCREMENT PRIMARY KEY,
  metric_code VARCHAR(40) NOT NULL,
  user_id INT NOT NULL,
  year SMALLINT NOT NULL,
  value DECIMAL(18,4) NOT NULL COMMENT 'money metrics stored in USD',
  region_id INT NOT NULL,
  org_type VARCHAR(30) NOT NULL,
  beds_band ENUM('lt100','100_299','300_699','700plus','na') NOT NULL DEFAULT 'na',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_point (metric_code, user_id, year),
  FOREIGN KEY (metric_code) REFERENCES benchmark_metrics(code),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO benchmark_metrics (code, unit, min_value, max_value, sort_order) VALUES
('it_budget_pct_revenue','percent',0,30,1),
('it_staff_per_100_beds','number',0,200,2),
('cyber_pct_it_budget','percent',0,60,3),
('cloud_pct_it_budget','percent',0,100,4),
('his_amc_pct_licence','percent',0,60,5),
('ehr_cost_per_bed_year','usd',0,100000,6),
('helpdesk_tickets_per_bed_month','number',0,100,7),
('cio_total_comp','usd',0,2000000,8);

CREATE TABLE vault_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  description VARCHAR(800) NOT NULL DEFAULT '',
  category ENUM('rfp','policy','security','dr_bcp','contract','board','playbook','other') NOT NULL DEFAULT 'other',
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  file_path VARCHAR(255) NOT NULL,
  file_name VARCHAR(200) NOT NULL,
  mime VARCHAR(120) NOT NULL,
  size_bytes INT NOT NULL,
  sha256 CHAR(64) NOT NULL,
  downloads INT NOT NULL DEFAULT 0,
  status ENUM('published','removed') NOT NULL DEFAULT 'published',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  KEY idx_vault (status, category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE regulations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  jurisdiction VARCHAR(8) NOT NULL COMMENT 'ISO2, EU, or GLOBAL',
  title VARCHAR(220) NOT NULL,
  regulator VARCHAR(160) NOT NULL DEFAULT '',
  stage ENUM('proposed','enacted','in_force','amended','repealed') NOT NULL DEFAULT 'enacted',
  effective_on DATE NULL,
  summary TEXT NOT NULL,
  checklist TEXT NULL COMMENT 'one action per line',
  source_url VARCHAR(500) NOT NULL DEFAULT '',
  lang VARCHAR(8) NOT NULL DEFAULT 'en',
  status ENUM('draft','published') NOT NULL DEFAULT 'draft',
  created_by INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_reg (status, jurisdiction)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE regulation_follows (
  user_id INT NOT NULL,
  jurisdiction VARCHAR(8) NOT NULL,
  PRIMARY KEY (user_id, jurisdiction),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Threat intel: TLP marking and auto-expiry on posts in flagged circles
ALTER TABLE circles ADD COLUMN is_threat_intel TINYINT(1) NOT NULL DEFAULT 0;
ALTER TABLE posts ADD COLUMN tlp ENUM('clear','green','amber','red') NULL, ADD COLUMN expires_at DATETIME NULL;
INSERT INTO circles (name, slug, kind, description, is_private, is_threat_intel) VALUES
('Threat intel (CISOs)','threat-intel','role','Closed channel for incident, phishing and vulnerability alerts. Posts carry a TLP marking and expire automatically.',1,1);

ALTER TABLE content_reports MODIFY target_type ENUM('post','comment','review','vault') NOT NULL;

INSERT INTO settings (k, v) VALUES ('give_to_get','1'), ('benchmark_min_n','5'), ('invoice_prefix','INV'), ('seller_details','Caresoft Foundation\nMumbai, India');

INSERT INTO comm_templates (code, channel, lang, subject, body) VALUES
('advertiser_approved','email','en','Your advertiser account is approved','Hello {{name}},\n\nYour advertiser account on {{site}} is approved. You can now book placements:\n{{link}}\n\n{{site}}'),
('campaign_reviewed','email','en','Your campaign was reviewed: {{title}}','Hello {{name}},\n\n{{message}}\n\nManage your campaigns: {{link}}\n\n{{site}}'),
('payment_receipt','email','en','Payment received {{ref}}','Hello {{name}},\n\nWe received your payment for {{title}}.\nInvoice: {{link}}\n\n{{site}}'),
('regulation_update','email','en','Regulatory update: {{title}}','Hello {{name}},\n\nA new regulatory update was published for a jurisdiction you follow:\n{{title}}\n{{link}}\n\n{{site}}');