323 lines
15 KiB
SQL
323 lines
15 KiB
SQL
-- Migration 032: Dutchie GraphQL Schema Alignment
|
|
-- Aligns product tables with actual Dutchie GraphQL FilteredProducts response
|
|
-- This is the CANONICAL product schema - all views derive from this
|
|
|
|
-- =====================================================
|
|
-- GRAPHQL FIELD → DB COLUMN MAPPING
|
|
-- =====================================================
|
|
-- GraphQL Field | DB Column | Notes
|
|
-- ---------------------------|----------------------------|---------------------------
|
|
-- _id / id | external_id | Dutchie's product ID
|
|
-- Name | name | Product name
|
|
-- cName | slug | URL slug
|
|
-- enterpriseProductId | enterprise_product_id | Cross-dispensary ID
|
|
-- DispensaryID | (via dispensary_id FK) | Store reference
|
|
--
|
|
-- BRAND:
|
|
-- brand.id / brandId | brand_external_id | Dutchie brand ID
|
|
-- brand.name / brandName | brand_name | Brand name
|
|
-- brand.description | (stored in raw_data) | Brand description
|
|
-- brand.imageUrl / brandLogo | brand_logo_url | Brand logo image
|
|
--
|
|
-- CATEGORY/TYPE:
|
|
-- type | category | e.g., "Edible", "Flower"
|
|
-- subcategory | subcategory | e.g., "gummies", "pre-rolls"
|
|
-- strainType | strain_type | Indica/Sativa/Hybrid/N/A
|
|
-- POSMetaData.canonicalCategory | canonical_category | e.g., "Edibles | Gummies and Candy"
|
|
--
|
|
-- PRICING (arrays - first element is primary):
|
|
-- Prices[0] | price | Current price
|
|
-- recPrices[0] | rec_price | Recreational price
|
|
-- medicalPrices[0] | med_price | Medical price
|
|
-- recSpecialPrices[0] | rec_special_price | Sale price (rec)
|
|
-- medicalSpecialPrices[0] | med_special_price | Sale price (med)
|
|
--
|
|
-- SPECIALS:
|
|
-- special | is_on_special | Boolean flag
|
|
-- specialData | special_data | JSONB with full special info
|
|
-- specialData.saleSpecials[0].specialName | special_name | Name of active special
|
|
-- specialData.saleSpecials[0].discount | discount_percent | Discount percentage
|
|
--
|
|
-- INVENTORY (from POSMetaData.children):
|
|
-- children[0].quantity | inventory_quantity | Total inventory
|
|
-- children[0].quantityAvailable | inventory_available | Available online
|
|
-- Status | status | "Active" or "Inactive"
|
|
-- isBelowThreshold | is_below_threshold | Low stock flag
|
|
--
|
|
-- POTENCY:
|
|
-- THCContent.range[0] | thc_percent | THC percentage
|
|
-- CBDContent.range[0] | cbd_percent | CBD percentage
|
|
-- THCContent.unit | (always PERCENTAGE) | Usually "PERCENTAGE"
|
|
-- cannabinoidsV2 | cannabinoids | JSONB array of all cannabinoids
|
|
--
|
|
-- PACKAGING/WEIGHT:
|
|
-- Options | options | TEXT[] of size options
|
|
-- rawOptions | raw_options | TEXT[] original options
|
|
-- weight | weight_mg | Weight in mg (numeric)
|
|
-- measurements.netWeight.values[0] | net_weight_value | Net weight value
|
|
-- measurements.netWeight.unit | net_weight_unit | e.g., "MILLIGRAMS"
|
|
-- POSMetaData.canonicalSKU | sku | Product SKU
|
|
--
|
|
-- IMAGES:
|
|
-- Image | image_url | Primary image URL
|
|
-- images | additional_images | TEXT[] of additional images
|
|
--
|
|
-- FLAGS:
|
|
-- featured | is_featured | Featured product
|
|
-- medicalOnly | medical_only | Medical only flag
|
|
-- recOnly | rec_only | Recreational only flag
|
|
--
|
|
-- TIMESTAMPS:
|
|
-- createdAt | source_created_at | When created in Dutchie
|
|
-- updatedAt | source_updated_at | Last update in Dutchie
|
|
--
|
|
-- RAW DATA:
|
|
-- (full response) | raw_data | Complete GraphQL response
|
|
|
|
-- =====================================================
|
|
-- ADD external_id TO products FIRST (needed for upsert)
|
|
-- =====================================================
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS external_id VARCHAR(255);
|
|
CREATE INDEX IF NOT EXISTS idx_products_external_id ON products(external_id) WHERE external_id IS NOT NULL;
|
|
|
|
-- =====================================================
|
|
-- ADD NEW COLUMNS TO product_snapshots (if table exists)
|
|
-- =====================================================
|
|
|
|
-- External IDs
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS enterprise_product_id VARCHAR(255);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS sku VARCHAR(100);
|
|
|
|
-- Brand details
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS brand_external_id VARCHAR(255);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS brand_logo_url TEXT;
|
|
|
|
-- Canonical category from POS
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS canonical_category VARCHAR(255);
|
|
|
|
-- Pricing - multi-tier
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS rec_price NUMERIC(10,2);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS med_price NUMERIC(10,2);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS rec_special_price NUMERIC(10,2);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS med_special_price NUMERIC(10,2);
|
|
|
|
-- Special details
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS is_on_special BOOLEAN DEFAULT false;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS special_name TEXT;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS discount_percent NUMERIC(5,2);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS special_data JSONB;
|
|
|
|
-- Inventory from POSMetaData
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS inventory_quantity INTEGER;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS inventory_available INTEGER;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS is_below_threshold BOOLEAN DEFAULT false;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS status VARCHAR(20);
|
|
|
|
-- Cannabinoids (full data, not just THC/CBD)
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS cannabinoids JSONB;
|
|
|
|
-- Weight and packaging
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS weight_mg INTEGER;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS net_weight_value NUMERIC(10,2);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS net_weight_unit VARCHAR(20);
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS options TEXT[];
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS raw_options TEXT[];
|
|
|
|
-- Additional images
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS additional_images TEXT[];
|
|
|
|
-- Flags
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS is_featured BOOLEAN DEFAULT false;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS medical_only BOOLEAN DEFAULT false;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS rec_only BOOLEAN DEFAULT false;
|
|
|
|
-- Source timestamps
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS source_created_at TIMESTAMPTZ;
|
|
ALTER TABLE product_snapshots ADD COLUMN IF NOT EXISTS source_updated_at TIMESTAMPTZ;
|
|
|
|
-- Rename existing column if needed (brand -> brand_name for clarity)
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = 'product_snapshots' AND column_name = 'brand') THEN
|
|
ALTER TABLE product_snapshots RENAME COLUMN brand TO brand_name;
|
|
END IF;
|
|
EXCEPTION WHEN OTHERS THEN
|
|
-- Column already named brand_name or doesn't exist
|
|
NULL;
|
|
END $$;
|
|
|
|
-- =====================================================
|
|
-- UPDATE products TABLE (canonical/current state)
|
|
-- =====================================================
|
|
|
|
-- External IDs
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS enterprise_product_id VARCHAR(255);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS sku VARCHAR(100);
|
|
|
|
-- Brand details
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS brand_external_id VARCHAR(255);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS brand_logo_url TEXT;
|
|
|
|
-- Category details
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS subcategory VARCHAR(100);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS canonical_category VARCHAR(255);
|
|
|
|
-- Pricing - multi-tier
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS rec_price NUMERIC(10,2);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS med_price NUMERIC(10,2);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS rec_special_price NUMERIC(10,2);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS med_special_price NUMERIC(10,2);
|
|
|
|
-- Special details
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS is_on_special BOOLEAN DEFAULT false;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS special_name TEXT;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS discount_percent NUMERIC(5,2);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS special_data JSONB;
|
|
|
|
-- Inventory
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS inventory_quantity INTEGER;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS inventory_available INTEGER;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS is_below_threshold BOOLEAN DEFAULT false;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'Active';
|
|
|
|
-- Cannabinoids (full data)
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS cannabinoids JSONB;
|
|
|
|
-- Weight and packaging
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS weight_mg INTEGER;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS net_weight_value NUMERIC(10,2);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS net_weight_unit VARCHAR(20);
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS options TEXT[];
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS raw_options TEXT[];
|
|
|
|
-- Additional images
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS additional_images TEXT[];
|
|
|
|
-- Flags
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS is_featured BOOLEAN DEFAULT false;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS medical_only BOOLEAN DEFAULT false;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS rec_only BOOLEAN DEFAULT false;
|
|
|
|
-- Source timestamps
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS source_created_at TIMESTAMPTZ;
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS source_updated_at TIMESTAMPTZ;
|
|
|
|
-- Raw GraphQL data for debugging/reference
|
|
ALTER TABLE products ADD COLUMN IF NOT EXISTS raw_data JSONB;
|
|
|
|
-- =====================================================
|
|
-- INDEXES for new columns
|
|
-- =====================================================
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_products_brand_external ON products(brand_external_id);
|
|
CREATE INDEX IF NOT EXISTS idx_products_enterprise ON products(enterprise_product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);
|
|
CREATE INDEX IF NOT EXISTS idx_products_is_special ON products(is_on_special);
|
|
CREATE INDEX IF NOT EXISTS idx_products_subcategory ON products(subcategory);
|
|
CREATE INDEX IF NOT EXISTS idx_products_status ON products(status);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_snapshots_brand_external ON product_snapshots(brand_external_id);
|
|
CREATE INDEX IF NOT EXISTS idx_snapshots_enterprise ON product_snapshots(enterprise_product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_snapshots_is_special ON product_snapshots(is_on_special);
|
|
CREATE INDEX IF NOT EXISTS idx_snapshots_status ON product_snapshots(status);
|
|
|
|
-- =====================================================
|
|
-- UPDATED VIEWS
|
|
-- =====================================================
|
|
|
|
-- View: Current specials (products on sale)
|
|
CREATE OR REPLACE VIEW current_specials AS
|
|
SELECT
|
|
p.id,
|
|
p.store_id,
|
|
p.name,
|
|
p.brand,
|
|
p.subcategory,
|
|
p.strain_type,
|
|
p.rec_price,
|
|
p.rec_special_price,
|
|
p.discount_percent,
|
|
p.special_name,
|
|
p.image_url,
|
|
p.slug,
|
|
p.thc_percentage,
|
|
p.cbd_percentage,
|
|
s.name AS store_name,
|
|
s.slug AS store_slug
|
|
FROM products p
|
|
JOIN stores s ON s.id = p.store_id
|
|
WHERE p.is_on_special = true
|
|
AND p.status = 'Active'
|
|
AND p.rec_special_price IS NOT NULL;
|
|
|
|
-- View: Current brands (derived from products)
|
|
CREATE OR REPLACE VIEW derived_brands AS
|
|
SELECT
|
|
p.store_id,
|
|
p.brand AS brand_name,
|
|
p.brand_external_id,
|
|
MAX(p.brand_logo_url) AS brand_logo_url,
|
|
COUNT(*) AS product_count,
|
|
COUNT(*) FILTER (WHERE p.status = 'Active') AS active_count,
|
|
COUNT(*) FILTER (WHERE p.is_on_special) AS special_count,
|
|
MIN(p.rec_price) AS min_price,
|
|
MAX(p.rec_price) AS max_price,
|
|
AVG(p.rec_price) AS avg_price,
|
|
ARRAY_AGG(DISTINCT p.subcategory) FILTER (WHERE p.subcategory IS NOT NULL) AS categories,
|
|
MAX(p.updated_at) AS last_updated
|
|
FROM products p
|
|
WHERE p.brand IS NOT NULL
|
|
GROUP BY p.store_id, p.brand, p.brand_external_id;
|
|
|
|
-- View: Current categories (derived from products)
|
|
CREATE OR REPLACE VIEW derived_categories AS
|
|
SELECT
|
|
p.store_id,
|
|
p.subcategory AS category_name,
|
|
COUNT(*) AS product_count,
|
|
COUNT(*) FILTER (WHERE p.status = 'Active') AS active_count,
|
|
COUNT(*) FILTER (WHERE p.is_on_special) AS special_count,
|
|
MIN(p.rec_price) AS min_price,
|
|
MAX(p.rec_price) AS max_price,
|
|
ARRAY_AGG(DISTINCT p.brand) FILTER (WHERE p.brand IS NOT NULL) AS brands,
|
|
MAX(p.updated_at) AS last_updated
|
|
FROM products p
|
|
WHERE p.subcategory IS NOT NULL
|
|
GROUP BY p.store_id, p.subcategory;
|
|
|
|
-- =====================================================
|
|
-- COMMENTS
|
|
-- =====================================================
|
|
|
|
COMMENT ON COLUMN products.external_id IS 'Dutchie _id / id field - the product unique identifier in Dutchie';
|
|
COMMENT ON COLUMN products.enterprise_product_id IS 'Dutchie enterpriseProductId - shared across dispensaries';
|
|
COMMENT ON COLUMN products.brand_external_id IS 'Dutchie brand.id / brandId field';
|
|
COMMENT ON COLUMN products.canonical_category IS 'Dutchie POSMetaData.canonicalCategory - pipe-separated category path';
|
|
COMMENT ON COLUMN products.rec_special_price IS 'Dutchie recSpecialPrices[0] - discounted recreational price';
|
|
COMMENT ON COLUMN products.special_data IS 'Full Dutchie specialData JSONB including all active specials';
|
|
COMMENT ON COLUMN products.cannabinoids IS 'Full Dutchie cannabinoidsV2 array as JSONB';
|
|
COMMENT ON COLUMN products.raw_data IS 'Complete Dutchie GraphQL response for this product';
|
|
|
|
-- Grant permissions
|
|
GRANT SELECT ON current_specials TO scraper;
|
|
GRANT SELECT ON derived_brands TO scraper;
|
|
GRANT SELECT ON derived_categories TO scraper;
|
|
-- ============================================================
|
|
-- Brand history view (track brand count per dispensary over time)
|
|
-- ============================================================
|
|
DROP VIEW IF EXISTS public.v_brand_history CASCADE;
|
|
CREATE OR REPLACE VIEW public.v_brand_history AS
|
|
SELECT
|
|
d.id AS dispensary_id,
|
|
COALESCE(d.dba_name, d.name) AS dispensary_name,
|
|
d.city,
|
|
d.state,
|
|
COUNT(DISTINCT p.brand_name) AS brand_count,
|
|
MIN(p.created_at) AS first_seen_at,
|
|
MAX(p.updated_at) AS last_seen_at
|
|
FROM public.dutchie_products p
|
|
JOIN public.dispensaries d ON d.id = p.dispensary_id
|
|
WHERE p.brand_name IS NOT NULL
|
|
GROUP BY d.id, d.dba_name, d.name, d.city, d.state;
|