-- =============================================
-- Quiet Beauty Spa - Database Schema
-- =============================================
-- Run via: curl "https://quietbeauty.privateclubmarketing.com/api/run-migration.php?key=quietbeauty_migrate_2026"

-- =============================================
-- PROJECT MANAGEMENT TABLES (Rockspring pattern)
-- =============================================

-- USERS & AUTHENTICATION
CREATE TABLE IF NOT EXISTS qb_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    role ENUM('admin', 'manager', 'client') DEFAULT 'client',
    status ENUM('active', 'inactive', 'pending') DEFAULT 'active',
    profile_photo VARCHAR(500),
    phone VARCHAR(50),
    company VARCHAR(255),
    job_title VARCHAR(255),
    timezone VARCHAR(50) DEFAULT 'America/Los_Angeles',
    last_login DATETIME,
    login_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PROJECT PHASES
CREATE TABLE IF NOT EXISTS qb_project_phases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_number INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('not_started', 'in_progress', 'completed', 'on_hold') DEFAULT 'not_started',
    start_date DATE,
    end_date DATE,
    actual_end_date DATE,
    progress_percent INT DEFAULT 0,
    client_visible TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_number),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- DELIVERABLES (extended with blocked status and assigned_to)
CREATE TABLE IF NOT EXISTS qb_deliverables (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_id INT,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    category ENUM('website', 'design', 'email', 'direct_mail', 'strategy', 'content', 'integration', 'analytics', 'report', 'other') DEFAULT 'other',
    status ENUM('not_started', 'in_progress', 'in_review', 'revision', 'completed', 'delivered', 'blocked') DEFAULT 'not_started',
    blocked_reason VARCHAR(500),
    assigned_to VARCHAR(255),
    file_url VARCHAR(1000),
    file_name VARCHAR(500),
    file_type VARCHAR(50),
    file_size INT,
    version INT DEFAULT 1,
    client_visible TINYINT(1) DEFAULT 0,
    client_notes TEXT,
    internal_notes TEXT,
    delivered_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_id),
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_client_visible (client_visible),
    INDEX idx_assigned (assigned_to),
    FOREIGN KEY (phase_id) REFERENCES qb_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MILESTONES
CREATE TABLE IF NOT EXISTS qb_milestones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    due_date DATE,
    completed_date DATE,
    status ENUM('upcoming', 'in_progress', 'completed', 'overdue') DEFAULT 'upcoming',
    phase_id INT,
    client_visible TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_due (due_date),
    FOREIGN KEY (phase_id) REFERENCES qb_project_phases(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- PROJECT NOTES (adds blocking note type)
CREATE TABLE IF NOT EXISTS qb_project_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_id INT,
    author_id INT,
    author_name VARCHAR(255),
    content TEXT NOT NULL,
    note_type ENUM('internal', 'client_update', 'meeting_notes', 'decision', 'blocking') DEFAULT 'internal',
    client_visible TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_id),
    INDEX idx_type (note_type),
    INDEX idx_visible (client_visible)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- TIME ENTRIES
CREATE TABLE IF NOT EXISTS qb_time_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phase_id INT,
    deliverable_id INT,
    user_id INT,
    user_name VARCHAR(255),
    hours DECIMAL(5,2) NOT NULL,
    description TEXT,
    entry_date DATE NOT NULL,
    billable TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phase (phase_id),
    INDEX idx_date (entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- AUDIT LOG
CREATE TABLE IF NOT EXISTS qb_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    user_email VARCHAR(255),
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    details JSON,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- CRM / MARKETING TABLES (BRC pattern)
-- =============================================

-- CONSUMERS (10K UHNW database)
CREATE TABLE IF NOT EXISTS qb_consumers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255),
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    name VARCHAR(255),
    phone VARCHAR(50),
    address VARCHAR(500),
    city VARCHAR(100),
    state VARCHAR(50),
    zip VARCHAR(20),
    country VARCHAR(50) DEFAULT 'US',
    segment ENUM('super_target', 'ideal_skincare', 'local_vip', 'gift_card_buyer', 'luxury_lifestyle', 'general') DEFAULT 'general',
    distance_tier ENUM('0-3mi', '3-5mi', '5-10mi', '10-25mi', '25mi+') DEFAULT NULL,
    estimated_income VARCHAR(50),
    age_range VARCHAR(20),
    gender VARCHAR(20),
    interest_skincare TINYINT(1) DEFAULT 0,
    interest_wellness TINYINT(1) DEFAULT 0,
    interest_luxury TINYINT(1) DEFAULT 0,
    interest_fitness TINYINT(1) DEFAULT 0,
    lifestyle_data JSON,
    source VARCHAR(100),
    source_detail VARCHAR(255),
    boulevard_id VARCHAR(100),
    shopify_id VARCHAR(100),
    status ENUM('active', 'unsubscribed', 'bounced', 'inactive') DEFAULT 'active',
    last_visit DATE,
    visit_count INT DEFAULT 0,
    total_spend DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_segment (segment),
    INDEX idx_city (city),
    INDEX idx_status (status),
    INDEX idx_distance (distance_tier)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- LISTS (audience segments)
CREATE TABLE IF NOT EXISTS qb_lists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type ENUM('static', 'dynamic', 'import') DEFAULT 'static',
    segment_criteria JSON,
    member_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- LIST MEMBERS
CREATE TABLE IF NOT EXISTS qb_list_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    list_id INT NOT NULL,
    consumer_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_list (list_id),
    INDEX idx_consumer (consumer_id),
    UNIQUE KEY uk_list_consumer (list_id, consumer_id),
    FOREIGN KEY (list_id) REFERENCES qb_lists(id) ON DELETE CASCADE,
    FOREIGN KEY (consumer_id) REFERENCES qb_consumers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CAMPAIGNS (email + direct mail + geofenced ads)
CREATE TABLE IF NOT EXISTS qb_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(500) NOT NULL,
    subject VARCHAR(500),
    from_name VARCHAR(255),
    from_email VARCHAR(255),
    type ENUM('email', 'direct_mail', 'geofenced_ad', 'sms') DEFAULT 'email',
    status ENUM('draft', 'scheduled', 'sending', 'sent', 'paused', 'cancelled') DEFAULT 'draft',
    list_id INT,
    html_content LONGTEXT,
    text_content TEXT,
    ab_test TINYINT(1) DEFAULT 0,
    ab_variant CHAR(1),
    ab_parent_id INT,
    ab_winner_criteria VARCHAR(50),
    scheduled_at DATETIME,
    sent_at DATETIME,
    completed_at DATETIME,
    total_recipients INT DEFAULT 0,
    total_sent INT DEFAULT 0,
    total_opened INT DEFAULT 0,
    total_clicked INT DEFAULT 0,
    total_bounced INT DEFAULT 0,
    total_unsubscribed INT DEFAULT 0,
    client_visible TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_type (type),
    INDEX idx_list (list_id),
    INDEX idx_ab_parent (ab_parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- EMAIL QUEUE
CREATE TABLE IF NOT EXISTS qb_email_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT,
    consumer_id INT,
    email VARCHAR(255) NOT NULL,
    status ENUM('queued', 'sent', 'opened', 'clicked', 'bounced', 'failed') DEFAULT 'queued',
    sent_at DATETIME,
    opened_at DATETIME,
    clicked_at DATETIME,
    bounce_type VARCHAR(20),
    bounce_reason TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign (campaign_id),
    INDEX idx_consumer (consumer_id),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- EMAIL CLICKS
CREATE TABLE IF NOT EXISTS qb_email_clicks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    queue_id INT,
    campaign_id INT,
    consumer_id INT,
    email VARCHAR(255),
    clicked_url VARCHAR(1000),
    ip_address VARCHAR(45),
    user_agent TEXT,
    city VARCHAR(100),
    state VARCHAR(50),
    country VARCHAR(50),
    latitude DECIMAL(10,7),
    longitude DECIMAL(10,7),
    clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_campaign (campaign_id),
    INDEX idx_consumer (consumer_id),
    INDEX idx_url (clicked_url(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =============================================
-- SEED DATA
-- =============================================
-- Note: User seed data is inserted by run-migration.php with proper password hashing

-- Project Phases (from LAUNCH-PLAN.md 60-day timeline)
INSERT INTO qb_project_phases (phase_number, name, description, status, progress_percent, display_order, start_date, end_date)
VALUES
    (1, 'Foundation', 'Brand & technical setup, integrations, content gathering. Logo finalization, domain setup, Boulevard/Shopify rebrand, content updates.', 'in_progress', 75, 1, '2026-01-06', '2026-01-19'),
    (2, 'Build & Refine', 'Website completion, testing, pre-launch prep. Boulevard booking, Shopify product feed, analytics, newsletter integration, client review.', 'in_progress', 40, 2, '2026-01-20', '2026-02-09'),
    (3, 'Launch', 'Soft launch and public launch. Deploy to production, VIP preview, announcement emails, social media launch, redirect setup.', 'not_started', 0, 3, '2026-02-10', '2026-02-19'),
    (4, 'Post-Launch', 'Optimization and growth. Analytics monitoring, content calendar, membership campaign, retargeting, SEO optimization.', 'not_started', 0, 4, '2026-02-20', '2026-03-06')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Phase 1 Deliverables (completed items from PUNCH-LIST.md)
INSERT INTO qb_deliverables (phase_id, title, description, category, status, assigned_to, client_visible, delivered_at)
VALUES
    (1, 'Website Build (10 Pages)', 'Homepage, Offerings, Facials, Lashes & Brows, Body, Membership, About, Contact, The Regimen, Shop', 'website', 'delivered', 'Zack', 1, '2026-01-15'),
    (1, 'Mobile Responsive Design', 'Cross-browser and mobile testing (iOS Safari, Android Chrome, desktop browsers)', 'website', 'delivered', 'Zack', 1, '2026-01-14'),
    (1, 'SEO Optimization', 'Meta tags, structured data (LocalBusiness schema), sitemap.xml, robots.txt', 'website', 'delivered', 'Zack', 1, '2026-01-14'),
    (1, 'Membership Email Template', 'HTML email template for membership launch announcement', 'email', 'delivered', 'Zack', 1, '2026-01-12'),
    (1, 'Weekly Newsletter Template', 'HTML newsletter template matching Quiet Beauty brand', 'email', 'delivered', 'Zack', 1, '2026-01-12'),
    (1, 'Direct Mail Design & Copy', '7x9 postage-ready mailer for 3,500 Newport Beach households', 'direct_mail', 'delivered', 'Zack', 1, '2026-01-10'),
    (1, 'Audience Report', '10,000 UHNW consumer database analysis with segmentation strategy', 'report', 'delivered', 'Zack', 1, '2026-01-08'),
    (1, 'Pricing Updates', 'Service pricing updates per Joanna review, booking CTA language update', 'website', 'delivered', 'Zack', 1, '2026-01-14'),
    (1, 'FTP Upload to Staging', 'All updated pages uploaded to privateclubmarketing.com/clients/quiet-beauty/', 'website', 'delivered', 'Zack', 1, '2026-01-15')
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 2 Deliverables (in progress + blocked)
INSERT INTO qb_deliverables (phase_id, title, description, category, status, blocked_reason, assigned_to, client_visible)
VALUES
    (2, 'Website Pricing Update', 'Update all service pricing from Miranda pricing document', 'website', 'blocked', 'Waiting on Miranda pricing Word doc', 'Zack', 0),
    (2, 'CMS Setup (Decap CMS)', 'Content management system for blog and page editing', 'integration', 'in_progress', NULL, 'Zack', 0),
    (2, 'Shopify Integration', 'Product feeds, Buy Button embedding, store branding update', 'integration', 'in_progress', NULL, 'Zack', 0),
    (2, 'Boulevard Booking Widget', 'Embed booking widget on contact page, Book Now buttons throughout site', 'integration', 'in_progress', NULL, 'Zack', 0),
    (2, 'Favicon & App Icons', 'Create from final logo files (16x16, 32x32, 180x180 Apple)', 'design', 'blocked', 'Waiting on final logo files from Joanna', 'Zack', 0),
    (2, 'Google Analytics Setup', 'GA4 tracking code (GT-NCGSX98X) on production domain', 'analytics', 'blocked', 'Need site on production domain first', 'Zack', 0),
    (2, 'Google Tag Manager', 'GTM container (GTM-NWKQDR6M) transfer to new site', 'analytics', 'blocked', 'Need site on production domain first', 'Zack', 0),
    (2, 'Newsletter Form Integration', 'Email signup form connected to Klaviyo or Mailchimp', 'integration', 'not_started', NULL, 'Zack', 0),
    (2, 'Contact Form Backend', 'Server-side contact form processing and email notifications', 'integration', 'not_started', NULL, 'Zack', 0),
    (2, 'Blog Migration', 'Export past blog articles from old site, upload to The Regimen section', 'content', 'not_started', NULL, 'Zack', 0),
    (2, 'Email A/B Test Setup', '250 total (125 letter-style + 125 promotional), track opens/clicks', 'email', 'in_progress', NULL, 'Zack', 0),
    (2, 'Direct Mail Creative Finalization', '2-3 versions for Joanna review, 7x9 postage-ready format', 'direct_mail', 'in_progress', NULL, 'Zack', 0)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 3 Deliverables (upcoming)
INSERT INTO qb_deliverables (phase_id, title, description, category, status, assigned_to, client_visible)
VALUES
    (3, 'Production Deployment', 'Deploy website to quietbeautyspa.com with SSL', 'website', 'not_started', 'Zack', 1),
    (3, '301 Redirects', 'Redirect joannayoungskin.com to quietbeautyspa.com', 'website', 'not_started', 'Zack', 0),
    (3, 'Launch Announcement Email', 'Official announcement to full list (winning A/B version)', 'email', 'not_started', 'Zack', 1),
    (3, 'Social Media Launch Posts', 'Instagram + Facebook launch announcement (carousel + stories)', 'content', 'not_started', 'Zack', 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Phase 4 Deliverables (future)
INSERT INTO qb_deliverables (phase_id, title, description, category, status, assigned_to, client_visible)
VALUES
    (4, 'Analytics Monitoring Setup', 'Search Console sitemap, ranking monitoring, traffic dashboard', 'analytics', 'not_started', 'Zack', 0),
    (4, 'Content Calendar', 'Ongoing blog/social content plan for The Regimen', 'strategy', 'not_started', 'Zack', 1),
    (4, 'Membership Promotion Campaign', 'Targeted membership drive with email sequence', 'email', 'not_started', 'Zack', 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Milestones (from LAUNCH-PLAN.md)
INSERT INTO qb_milestones (title, description, due_date, status, phase_id, client_visible)
VALUES
    ('Website Draft Review', 'Client reviews website draft and provides feedback', '2026-01-14', 'completed', 1, 1),
    ('Brand Assets Finalized', 'Final logo files, brand guidelines, and visual identity complete', '2026-01-25', 'in_progress', 1, 1),
    ('Email A/B Test Launch', 'Initial 250-recipient A/B test send (letter vs promotional)', '2026-01-28', 'upcoming', 2, 1),
    ('Client Final Approval', 'Joanna approves website for production deployment', '2026-02-07', 'upcoming', 2, 1),
    ('Soft Launch', 'Deploy to quietbeautyspa.com, VIP preview', '2026-02-12', 'upcoming', 3, 1),
    ('Public Launch', 'Official announcement, redirects live, social media launch', '2026-02-17', 'upcoming', 3, 1),
    ('Target Opening', 'Grand opening of Quiet Beauty Spa (March 2026)', '2026-03-01', 'upcoming', 4, 1)
ON DUPLICATE KEY UPDATE title = VALUES(title);

-- Default Audience Lists
INSERT INTO qb_lists (name, description, type, member_count)
VALUES
    ('Super Targets', 'Top 440 high-value prospects: UHNW, proximity, lifestyle match', 'static', 440),
    ('Ideal Skincare Client', '1,005 consumers matching ideal client profile', 'static', 1005),
    ('Local VIP', '768 Newport Beach area luxury consumers within 3mi', 'static', 768),
    ('Gift Card Buyers', '341 consumers with gift-giving purchase history', 'static', 341),
    ('Luxury Lifestyle', '247 ultra-high-net-worth lifestyle-matched consumers', 'static', 247),
    ('Full Database', 'Complete 10K UHNW consumer database', 'static', 10000),
    ('Direct Mail 3,500', 'Newport Beach households for 7x9 mailer campaign', 'static', 3500),
    ('Email A/B Test 250', 'Initial test segment: 125 letter-style + 125 promotional', 'static', 250)
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Seed Notes
INSERT INTO qb_project_notes (phase_id, author_id, author_name, content, note_type, client_visible)
VALUES
    (1, 1, 'Zack Bates', 'Website build complete (10 pages). All pages uploaded to staging at privateclubmarketing.com/clients/quiet-beauty/. Mobile responsive and SEO optimized. Awaiting client review.', 'client_update', 1),
    (1, 1, 'Zack Bates', 'Blocking items: Miranda pricing Word doc (blocks website pricing update), final logo files from Joanna (blocks favicon, Boulevard/Shopify rebrand), GoDaddy admin access from Saul (blocks email domain setup).', 'blocking', 0),
    (2, 1, 'Zack Bates', 'Email A/B test plan: 250 total (125 letter-style from Joanna, 125 promotional). Winner rolls out to full 2,500 list staggered 250/day over 10 days. Target: 20% open rate.', 'internal', 0)
ON DUPLICATE KEY UPDATE content = VALUES(content);
