-- ============================================================
-- Maison Palo Santo Portal — Database Schema
-- Node.js + React + MySQL
-- ============================================================

CREATE DATABASE IF NOT EXISTS mps_portal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mps_portal;

-- ============================================================
-- SITE SETTINGS (key-value config)
-- ============================================================
CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(100) NOT NULL UNIQUE,
  setting_value TEXT,
  setting_group VARCHAR(50) DEFAULT 'general',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- TRACKING PIXELS
-- ============================================================
CREATE TABLE tracking_pixels (
  id INT AUTO_INCREMENT PRIMARY KEY,
  platform VARCHAR(50) NOT NULL,
  pixel_id VARCHAR(255) NOT NULL,
  enabled TINYINT(1) DEFAULT 1,
  extra_config JSON,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- PAGES (landing page metadata)
-- ============================================================
CREATE TABLE pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(100) NOT NULL UNIQUE,
  title VARCHAR(255) NOT NULL,
  meta_description VARCHAR(500),
  og_image VARCHAR(500),
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- PAGE SECTIONS (editable content blocks per page)
-- ============================================================
CREATE TABLE page_sections (
  id INT AUTO_INCREMENT PRIMARY KEY,
  page_id INT NOT NULL,
  section_key VARCHAR(100) NOT NULL,
  section_type ENUM('hero','features','benefits','cta','testimonial','faq','custom') DEFAULT 'custom',
  content JSON NOT NULL,
  sort_order INT DEFAULT 0,
  is_visible TINYINT(1) DEFAULT 1,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE,
  UNIQUE KEY unique_page_section (page_id, section_key)
) ENGINE=InnoDB;

-- ============================================================
-- LEADS (form submissions)
-- ============================================================
CREATE TABLE leads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  source_page VARCHAR(100),
  lead_type ENUM('consumer','business','ambassador') DEFAULT 'consumer',
  segment VARCHAR(100),
  tags JSON,
  mailchimp_id VARCHAR(100),
  mailchimp_synced TINYINT(1) DEFAULT 0,
  utm_source VARCHAR(255),
  utm_medium VARCHAR(255),
  utm_campaign VARCHAR(255),
  utm_content VARCHAR(255),
  utm_term VARCHAR(255),
  referrer VARCHAR(500),
  ip_address VARCHAR(45),
  user_agent VARCHAR(500),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_source (source_page),
  INDEX idx_type (lead_type),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- ============================================================
-- LEAD EVENTS (tracking actions)
-- ============================================================
CREATE TABLE lead_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  event_type VARCHAR(50) NOT NULL,
  event_data JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  INDEX idx_lead_events (lead_id, event_type)
) ENGINE=InnoDB;

-- ============================================================
-- EMAIL SEQUENCES
-- ============================================================
CREATE TABLE email_sequences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  target_segment VARCHAR(100),
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ============================================================
-- EMAIL MESSAGES (individual emails in a sequence)
-- ============================================================
CREATE TABLE email_messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sequence_id INT NOT NULL,
  subject VARCHAR(500) NOT NULL,
  preview_text VARCHAR(255),
  body_html LONGTEXT NOT NULL,
  body_text TEXT,
  delay_days INT DEFAULT 0,
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (sequence_id) REFERENCES email_sequences(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ============================================================
-- EMAIL SEND LOG
-- ============================================================
CREATE TABLE email_sends (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lead_id INT NOT NULL,
  message_id INT NOT NULL,
  status ENUM('queued','sent','failed','opened','clicked') DEFAULT 'queued',
  sent_at TIMESTAMP NULL,
  opened_at TIMESTAMP NULL,
  clicked_at TIMESTAMP NULL,
  error_message TEXT,
  FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE,
  FOREIGN KEY (message_id) REFERENCES email_messages(id) ON DELETE CASCADE,
  INDEX idx_send_status (status),
  INDEX idx_send_lead (lead_id)
) ENGINE=InnoDB;

-- ============================================================
-- PDF LEAD MAGNETS
-- ============================================================
CREATE TABLE pdfs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  filename VARCHAR(255) NOT NULL,
  file_path VARCHAR(500) NOT NULL,
  linked_page VARCHAR(100),
  download_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Tracking pixels (disabled by default — enter IDs in admin)
INSERT INTO tracking_pixels (platform, pixel_id, enabled) VALUES
('ga4', '', 0),
('meta', '', 0),
('pinterest', '', 0),
('google_ads', '', 0),
('linkedin', '', 0),
('tiktok', '', 0);

-- Site settings
INSERT INTO settings (setting_key, setting_value, setting_group) VALUES
('site_name', 'Maison Palo Santo', 'general'),
('site_url', 'https://app.maisonpalosanto.com', 'general'),
('mailchimp_api_key', '', 'mailchimp'),
('mailchimp_server', '', 'mailchimp'),
('mailchimp_list_id', '', 'mailchimp'),
('smtp_host', '', 'email'),
('smtp_port', '587', 'email'),
('smtp_user', '', 'email'),
('smtp_pass', '', 'email'),
('smtp_from_name', 'Mimi from Maison Palo Santo', 'email'),
('smtp_from_email', '', 'email'),
('brand_primary', '#86BA53', 'brand'),
('brand_forest', '#2D5016', 'brand'),
('brand_verdant', '#4A8C2A', 'brand'),
('brand_gold', '#C9A96E', 'brand'),
('brand_navy', '#303E4F', 'brand'),
('brand_light_bg', '#F4F9EE', 'brand');

-- Landing pages
INSERT INTO pages (slug, title, meta_description) VALUES
('authenticity', 'The Palo Santo Authenticity Kit', 'Your 5-point test to verify real Palo Santo. Free guide from Maison Palo Santo.'),
('no-woo', 'The No-Woo Wellness Kit', 'A practical guide to wellness rituals without the woo. Science-backed, culturally grounded.'),
('wellness-business', 'The Wellness Business Starter Kit', 'Turn Palo Santo into a revenue stream. Ambassador program, wholesale pricing, client scripts.');

-- Authenticity Kit sections
INSERT INTO page_sections (page_id, section_key, section_type, content, sort_order) VALUES
(1, 'hero', 'hero', '{"headline":"Is Your Palo Santo Even Real?","subheadline":"90% of what''s sold online isn''t. Get the 5-point test that tells you in 30 seconds.","cta_text":"Send Me the Free Kit","image":"/images/hero-authenticity.jpg"}', 1),
(1, 'features', 'features', '{"title":"What''s Inside Your Free Kit","items":[{"icon":"shield-check","title":"5-Point Authenticity Test","desc":"Verify any Palo Santo in 30 seconds flat"},{"icon":"globe","title":"Ecuador Sourcing Story","desc":"How we source directly from Manabí Province"},{"icon":"heart","title":"Cultural Respect Guide","desc":"Honor the tradition without appropriating it"},{"icon":"leaf","title":"3 Simple Rituals","desc":"Morning, evening, and space-clearing practices"},{"icon":"tree","title":"Sustainability Facts","desc":"160 miles of reforestation and counting"},{"icon":"tag","title":"15% Off First Order","desc":"Code AUTHENTIC15 at checkout"}]}', 2),
(1, 'form', 'cta', '{"headline":"Get Your Free Authenticity Kit","subheadline":"Enter your email and we''ll send it right over. No spam, no woo.","button_text":"Send My Free Kit","pdf_slug":"authenticity-kit"}', 3);

-- No-Woo Kit sections
INSERT INTO page_sections (page_id, section_key, section_type, content, sort_order) VALUES
(2, 'hero', 'hero', '{"headline":"Wellness Without the Woo","subheadline":"A practical guide to daily rituals that actually work. Science-backed. Culturally grounded. No crystals required.","cta_text":"Send Me the Free Kit","image":"/images/hero-nowoo.jpg"}', 1),
(2, 'features', 'features', '{"title":"What''s Inside Your Free Kit","items":[{"icon":"sunrise","title":"5-Minute Morning Reset","desc":"A grounding practice that takes less time than coffee"},{"icon":"moon","title":"Evening Wind-Down","desc":"Release the day without a 90-minute yoga session"},{"icon":"flame","title":"Candle Pairing Guide","desc":"Match scents to moods — because science"},{"icon":"microscope","title":"Real vs. BS Breakdown","desc":"What the research actually says about Palo Santo"},{"icon":"compass","title":"Sacred Space Setup","desc":"Create calm in any room, no altar required"},{"icon":"tag","title":"15% Off First Order","desc":"Code NOWOO15 at checkout"}]}', 2),
(2, 'form', 'cta', '{"headline":"Get Your Free No-Woo Kit","subheadline":"Practical wellness delivered to your inbox. Zero spam, zero pseudoscience.","button_text":"Send My Free Kit","pdf_slug":"no-woo-kit"}', 3);

-- Business Kit sections
INSERT INTO page_sections (page_id, section_key, section_type, content, sort_order) VALUES
(3, 'hero', 'hero', '{"headline":"Turn Palo Santo Into a Revenue Stream","subheadline":"The complete business starter kit for wellness professionals. Real numbers, real margins, real clients.","cta_text":"Send Me the Free Kit","image":"/images/hero-business.jpg"}', 1),
(3, 'features', 'features', '{"title":"What''s Inside Your Free Kit","items":[{"icon":"dollar-sign","title":"Revenue Calculator","desc":"See how $250 turns into $2K/month"},{"icon":"clipboard-check","title":"8-Point Sourcing Checklist","desc":"Never get burned by fake suppliers"},{"icon":"calculator","title":"Pricing Framework","desc":"The 2.5–3x rule that protects your margins"},{"icon":"users","title":"3 Integration Methods","desc":"Homeplay, in-session, and display models"},{"icon":"mic","title":"90-Second Client Script","desc":"Introduce Palo Santo without losing credibility"},{"icon":"award","title":"Ambassador Program","desc":"Start with a $250 box, everything included"}]}', 2),
(3, 'form', 'cta', '{"headline":"Get Your Free Business Starter Kit","subheadline":"Everything you need to add Palo Santo to your practice. No pitch, just numbers.","button_text":"Send My Free Kit","pdf_slug":"business-kit"}', 3);
