-- Multi-Vertical LoudCannabis Platform Database Schema
-- Version 3.0 - Industry-Agnostic Affiliate Conversion Engine
-- Created: November 25, 2025

-- =====================================================
-- TENANT SYSTEM (Multi-tenancy/SaaS Support)
-- =====================================================

CREATE TABLE tenants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    domain VARCHAR(255) UNIQUE,
    subdomain VARCHAR(100),
    logo_url VARCHAR(500),
    brand_colors JSON, -- {"primary": "#e91e63", "secondary": "#ffd700", "accent": "#00d9ff"}
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_domain (domain),
    INDEX idx_active (is_active)
);

-- =====================================================
-- VERTICALS SYSTEM (Industry Configurations)
-- =====================================================

CREATE TABLE verticals (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT DEFAULT 1, -- Default tenant for single deployment
    name VARCHAR(100) NOT NULL, -- "Cannabis", "Real Estate", "Finance", etc.
    slug VARCHAR(100) NOT NULL UNIQUE, -- "cannabis", "real-estate", "finance"
    description TEXT,
    icon VARCHAR(100), -- FontAwesome icon class
    color_primary VARCHAR(20) DEFAULT '#e91e63',
    color_secondary VARCHAR(20) DEFAULT '#ffd700',
    color_accent VARCHAR(20) DEFAULT '#00d9ff',

    -- AI Configuration
    system_prompt TEXT NOT NULL, -- "You are a cannabis industry expert..."
    fallback_responses JSON, -- Alternative responses for API failures

    -- RAG Configuration
    rag_collection VARCHAR(255), -- Path to RAG knowledge base
    embedding_model VARCHAR(255) DEFAULT 'BAAI/bge-small-en-v1.5',

    -- Survey Configuration
    survey_schema JSON NOT NULL, -- Complete survey structure as JSON

    -- Monetization Rules
    monetization_rules JSON, -- Targeting rules based on survey answers

    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    INDEX idx_tenant (tenant_id),
    INDEX idx_slug (slug),
    INDEX idx_active (is_active),
    INDEX idx_sort (sort_order)
);

-- =====================================================
-- PAYMENT & SUBSCRIPTION SYSTEM
-- =====================================================

CREATE TABLE payment_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    price_cents INT NOT NULL, -- Price in cents (e.g., 1999 = $19.99)
    currency VARCHAR(3) DEFAULT 'USD',
    interval VARCHAR(20) NOT NULL, -- 'monthly', 'yearly', 'lifetime', 'one-time'
    stripe_price_id VARCHAR(255), -- Stripe Price ID
    features JSON, -- Array of features included
    is_active BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    trial_days INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_slug (slug),
    INDEX idx_active (is_active),
    INDEX idx_sort (sort_order)
);

CREATE TABLE subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    plan_id INT NOT NULL,
    stripe_subscription_id VARCHAR(255), -- Stripe Subscription ID
    stripe_customer_id VARCHAR(255), -- Stripe Customer ID
    status ENUM('active', 'canceled', 'past_due', 'incomplete', 'trialing') DEFAULT 'active',
    current_period_start TIMESTAMP NULL,
    current_period_end TIMESTAMP NULL,
    trial_start TIMESTAMP NULL,
    trial_end TIMESTAMP NULL,
    cancel_at TIMESTAMP NULL,
    canceled_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES payment_plans(id),
    INDEX idx_user (user_id),
    INDEX idx_plan (plan_id),
    INDEX idx_status (status),
    INDEX idx_stripe_sub (stripe_subscription_id),
    INDEX idx_stripe_customer (stripe_customer_id)
);

CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    subscription_id INT NULL,
    stripe_payment_intent_id VARCHAR(255), -- Stripe Payment Intent ID
    stripe_charge_id VARCHAR(255), -- Stripe Charge ID
    amount_cents INT NOT NULL,
    currency VARCHAR(3) DEFAULT 'USD',
    status ENUM('pending', 'succeeded', 'failed', 'canceled', 'refunded') DEFAULT 'pending',
    payment_method VARCHAR(50), -- 'stripe', 'paypal', etc.
    description VARCHAR(255),
    metadata JSON, -- Additional payment data
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_subscription (subscription_id),
    INDEX idx_status (status),
    INDEX idx_stripe_payment (stripe_payment_intent_id),
    INDEX idx_stripe_charge (stripe_charge_id)
);

CREATE TABLE user_access_levels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    access_level VARCHAR(50) NOT NULL, -- 'free', 'premium', 'admin', etc.
    granted_by INT NULL, -- User ID who granted access (for admin grants)
    granted_reason VARCHAR(255),
    expires_at TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_level (access_level),
    INDEX idx_active (is_active),
    INDEX idx_expires (expires_at)
);

-- =====================================================
-- DYNAMIC SURVEYS (JSON-Based Survey Engine)
-- =====================================================

CREATE TABLE survey_schemas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    vertical_id INT NOT NULL,
    tenant_id INT DEFAULT 1,
    name VARCHAR(255) NOT NULL,
    version VARCHAR(20) DEFAULT '1.0',
    schema_json JSON NOT NULL, -- Complete survey structure

    -- Example schema_json structure:
    -- {
    --   "title": "Cannabis Interest Survey",
    --   "description": "Help us understand your cannabis needs",
    --   "questions": [
    --     {
    --       "id": "usage_type",
    --       "type": "radio",
    --       "question": "How do you primarily use cannabis?",
    --       "options": ["Medical", "Recreational", "Both"],
    --       "required": true
    --     }
    --   ]
    -- }

    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (vertical_id) REFERENCES verticals(id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    INDEX idx_vertical (vertical_id),
    INDEX idx_tenant (tenant_id),
    INDEX idx_active (is_active)
);

-- =====================================================
-- ENHANCED USERS (Multi-tenant support)
-- =====================================================

ALTER TABLE tbl_users
ADD COLUMN tenant_id INT DEFAULT 1 AFTER id,
ADD COLUMN vertical_id INT DEFAULT 1 AFTER tenant_id,
ADD COLUMN profile_data JSON, -- Extended user profile
ADD COLUMN preferences JSON, -- User preferences and settings
ADD COLUMN last_vertical_accessed INT,
ADD INDEX idx_tenant (tenant_id),
ADD INDEX idx_vertical (vertical_id),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id),
ADD FOREIGN KEY (vertical_id) REFERENCES verticals(id);

-- =====================================================
-- ENHANCED SURVEYS (Vertical-aware)
-- =====================================================

ALTER TABLE tbl_survey
ADD COLUMN tenant_id INT DEFAULT 1 AFTER id,
ADD COLUMN vertical_id INT NOT NULL DEFAULT 1 AFTER tenant_id,
ADD COLUMN survey_schema_version VARCHAR(20),
ADD INDEX idx_tenant (tenant_id),
ADD INDEX idx_vertical (vertical_id),
ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id),
ADD FOREIGN KEY (vertical_id) REFERENCES verticals(id);

-- =====================================================
-- ENHANCED OFFERS (Multi-vertical offer management)
-- =====================================================

CREATE TABLE offers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT DEFAULT 1,
    vertical_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    cta_text VARCHAR(100) DEFAULT 'Learn More',
    cta_url VARCHAR(500) NOT NULL,
    offer_type ENUM('affiliate', 'service', 'product', 'consultation') DEFAULT 'affiliate',

    -- Targeting Rules
    target_demographics JSON, -- Age ranges, locations, etc.
    target_survey_answers JSON, -- Survey responses that trigger this offer
    targeting_score DECIMAL(3,2) DEFAULT 0.5, -- 0.0 to 1.0 relevance score

    -- Monetization
    commission_rate DECIMAL(5,2), -- Percentage or flat rate
    payout_type ENUM('percentage', 'flat', 'cpa') DEFAULT 'percentage',

    -- Display Settings
    display_priority INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    start_date DATETIME,
    end_date DATETIME,

    -- Analytics
    impressions INT DEFAULT 0,
    clicks INT DEFAULT 0,
    conversions INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    FOREIGN KEY (vertical_id) REFERENCES verticals(id),
    INDEX idx_vertical (vertical_id),
    INDEX idx_tenant (tenant_id),
    INDEX idx_active (is_active),
    INDEX idx_priority (display_priority),
    INDEX idx_targeting (targeting_score)
);

-- =====================================================
-- USER DASHBOARD & RETENTION
-- =====================================================

CREATE TABLE user_dashboards (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    tenant_id INT DEFAULT 1,

    -- Saved Offers
    saved_offers JSON, -- Array of offer IDs user saved

    -- Chat History
    chat_sessions JSON, -- Recent chat session summaries

    -- Progress Tracking
    completed_offers JSON, -- Offers user has engaged with
    survey_completions JSON, -- Completed survey data

    -- Preferences
    notification_settings JSON, -- Email preferences, etc.
    theme_preference VARCHAR(50) DEFAULT 'default',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES tbl_users(id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    INDEX idx_user (user_id),
    INDEX idx_tenant (tenant_id)
);

CREATE TABLE ab_tests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tenant_id INT DEFAULT 1,
    vertical_id INT,
    test_name VARCHAR(255) NOT NULL,
    test_type ENUM('prompt', 'offer', 'survey', 'ui') NOT NULL,

    -- Test Variants
    variant_a JSON NOT NULL, -- Original version
    variant_b JSON NOT NULL, -- Test version

    -- Test Settings
    traffic_split DECIMAL(3,2) DEFAULT 0.5, -- 50/50 split
    is_active BOOLEAN DEFAULT FALSE,
    start_date DATETIME,
    end_date DATETIME,

    -- Results
    variant_a_conversions INT DEFAULT 0,
    variant_b_conversions INT DEFAULT 0,
    variant_a_impressions INT DEFAULT 0,
    variant_b_impressions INT DEFAULT 0,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    FOREIGN KEY (vertical_id) REFERENCES verticals(id),
    INDEX idx_tenant (tenant_id),
    INDEX idx_vertical (vertical_id),
    INDEX idx_active (is_active)
);

-- =====================================================
-- INITIAL DATA SEEDING
-- =====================================================

-- Default tenant
INSERT INTO tenants (name, domain, brand_colors) VALUES
('LoudCannabis', 'deepleafai.com', '{"primary": "#e91e63", "secondary": "#ffd700", "accent": "#00d9ff"}');

-- Cannabis vertical (our current implementation)
INSERT INTO verticals (
    tenant_id, name, slug, description, icon,
    color_primary, color_secondary, color_accent,
    system_prompt, survey_schema, monetization_rules
) VALUES (
    1,
    'Cannabis',
    'cannabis',
    'AI-powered cannabis consultation and lead generation platform',
    'fas fa-leaf',
    '#e91e63',
    '#ffd700',
    '#00d9ff',
    'You are an expert cannabis industry consultant with deep knowledge of strains, cultivation, business, legal compliance, and market opportunities. You help users find personalized cannabis solutions and monetization opportunities.',
    '{
        "title": "Cannabis Interest Survey",
        "description": "Help us understand your cannabis needs and interests",
        "questions": [
            {
                "id": "usage_type",
                "type": "radio",
                "question": "How do you primarily use cannabis?",
                "options": ["Medical", "Recreational", "Both"],
                "required": true
            },
            {
                "id": "frequency",
                "type": "radio",
                "question": "How often do you consume cannabis?",
                "options": ["Daily", "Weekly", "Monthly", "Occasionally"],
                "required": true
            },
            {
                "id": "supplements",
                "type": "radio",
                "question": "Are you interested in other herbal supplements?",
                "options": ["Yes", "No"],
                "required": true
            },
            {
                "id": "financial",
                "type": "radio",
                "question": "What's your current financial situation?",
                "options": ["Challenging", "Comfortable", "Very Comfortable"],
                "required": true
            },
            {
                "id": "investing",
                "type": "radio",
                "question": "Are you interested in cannabis investments?",
                "options": ["Yes", "No"],
                "required": true
            },
            {
                "id": "business_owner",
                "type": "radio",
                "question": "Are you a business owner?",
                "options": ["Yes", "No"],
                "required": true
            },
            {
                "id": "age_group",
                "type": "radio",
                "question": "What's your age group?",
                "options": ["18-24", "25-34", "35-44", "45-54", "55+"],
                "required": true
            },
            {
                "id": "shopping_pref",
                "type": "radio",
                "question": "Do you prefer online or in-person shopping?",
                "options": ["Online", "In-person", "Both"],
                "required": true
            }
        ]
    }',
    '{
        "rules": [
            {
                "condition": {"usage_type": "Medical"},
                "offers": ["medical_dispensary", "cbd_products", "therapeutic_guides"],
                "priority": "high"
            },
            {
                "condition": {"business_owner": "Yes"},
                "offers": ["business_consulting", "licensing_help", "supplier_networks"],
                "priority": "high"
            },
            {
                "condition": {"investing": "Yes"},
                "offers": ["investment_opportunities", "market_analysis", "financial_planning"],
                "priority": "medium"
            },
            {
                "condition": {"frequency": "Daily"},
                "offers": ["subscription_services", "bulk_discounts", "loyalty_programs"],
                "priority": "medium"
            }
        ]
    }'
);

-- Sample offers for Cannabis vertical
INSERT INTO offers (
    tenant_id, vertical_id, title, description, cta_text, cta_url,
    offer_type, target_demographics, target_survey_answers, targeting_score
) VALUES
(1, 1, 'Premium Cannabis Dispensary', '20% off first purchase at our medical-grade dispensary', 'Get 20% Off', 'https://example.com/dispensary?ref=aiwelcom20', 'affiliate', '{"age_groups": ["25-34", "35-44"]}', '{"usage_type": "Medical"}', 0.9),
(1, 1, 'Business Consulting', 'Expert cannabis business consulting and licensing help', 'Book Consultation', 'https://example.com/consulting?ref=aiplatform', 'service', '{"business_owners": true}', '{"business_owner": "Yes"}', 0.95),
(1, 1, 'Investment Opportunities', 'Curated cannabis investment opportunities and market analysis', 'Explore Investments', 'https://example.com/investments?ref=aiplatform', 'service', '{"income_levels": ["comfortable", "very_comfortable"]}', '{"investing": "Yes"}', 0.85);

-- =====================================================
-- MIGRATION COMPLETE
-- =====================================================

COMMIT;
