cannaiq/backend/migrations/060_consumer_verification_notifications.sql

-- Migration: Consumer verification and notification tracking
-- Adds email/SMS verification columns and notification history table

-- Add verification columns to users table
ALTER TABLE users
ADD COLUMN IF NOT EXISTS email_verified BOOLEAN DEFAULT false,
ADD COLUMN IF NOT EXISTS email_verification_token VARCHAR(64),
ADD COLUMN IF NOT EXISTS email_verification_sent_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS phone_verified BOOLEAN DEFAULT false,
ADD COLUMN IF NOT EXISTS phone_verification_code VARCHAR(6),
ADD COLUMN IF NOT EXISTS phone_verification_sent_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS notification_preference VARCHAR(20) DEFAULT 'email'; -- email, sms, both

-- Add city/state to users (for notification filtering)
ALTER TABLE users
ADD COLUMN IF NOT EXISTS city VARCHAR(100),
ADD COLUMN IF NOT EXISTS state VARCHAR(50);

-- Create notification tracking table
CREATE TABLE IF NOT EXISTS consumer_notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    alert_id INTEGER, -- References findagram_alerts or findadispo saved search
    alert_source VARCHAR(20), -- 'findagram' or 'findadispo'
    notification_type VARCHAR(20) NOT NULL, -- 'email' or 'sms'
    -- What triggered this notification
    trigger_type VARCHAR(50) NOT NULL, -- 'price_drop', 'back_in_stock', 'product_on_special', 'deal_alert'
    product_id INTEGER,
    dispensary_id INTEGER,
    -- Content
    subject VARCHAR(255),
    message_content TEXT,
    -- Tracking
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivered_at TIMESTAMP,
    opened_at TIMESTAMP,
    clicked_at TIMESTAMP,
    completed_at TIMESTAMP, -- When user has "seen" it or we mark it done
    -- External IDs (for SMS gateway tracking)
    external_message_id VARCHAR(100),
    -- Status
    status VARCHAR(20) DEFAULT 'pending', -- pending, sent, delivered, failed
    error_message TEXT
);

-- Create findadispo_favorites table (mirroring findagram_favorites)
CREATE TABLE IF NOT EXISTS findadispo_favorites (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    dispensary_id INTEGER, -- References dispensaries table
    -- Dispensary snapshot at time of save
    dispensary_name VARCHAR(255),
    dispensary_city VARCHAR(100),
    dispensary_state VARCHAR(50),
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, dispensary_id)
);

-- Create findadispo_alerts table
CREATE TABLE IF NOT EXISTS findadispo_alerts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    alert_type VARCHAR(50) NOT NULL, -- 'new_dispensary', 'deal_available'
    -- Target
    dispensary_id INTEGER,
    city VARCHAR(100),
    state VARCHAR(50),
    -- Status
    is_active BOOLEAN DEFAULT true,
    last_triggered_at TIMESTAMP,
    trigger_count INTEGER DEFAULT 0,
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create findadispo_saved_searches table
CREATE TABLE IF NOT EXISTS findadispo_saved_searches (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    -- Search criteria
    query TEXT,
    city VARCHAR(100),
    state VARCHAR(50),
    min_rating DECIMAL(3, 2),
    max_distance INTEGER,
    amenities TEXT[], -- Array of amenity filters
    -- Notification settings
    notify_on_new_dispensary BOOLEAN DEFAULT false,
    notify_on_deals BOOLEAN DEFAULT false,
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_users_email_verified ON users(email_verified);
CREATE INDEX IF NOT EXISTS idx_users_phone_verified ON users(phone_verified);
CREATE INDEX IF NOT EXISTS idx_users_city_state ON users(city, state);
CREATE INDEX IF NOT EXISTS idx_consumer_notifications_user_id ON consumer_notifications(user_id);
CREATE INDEX IF NOT EXISTS idx_consumer_notifications_status ON consumer_notifications(status);
CREATE INDEX IF NOT EXISTS idx_consumer_notifications_sent_at ON consumer_notifications(sent_at);
CREATE INDEX IF NOT EXISTS idx_findadispo_favorites_user_id ON findadispo_favorites(user_id);
CREATE INDEX IF NOT EXISTS idx_findadispo_alerts_user_id ON findadispo_alerts(user_id);
CREATE INDEX IF NOT EXISTS idx_findadispo_alerts_active ON findadispo_alerts(is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_findadispo_saved_searches_user_id ON findadispo_saved_searches(user_id);