-- Printful fulfillment integration: per-site connection credentials (dim_web_sites), a manual
-- variant->Printful mapping (dim_web_product_variants), and per-order fulfillment tracking
-- (fact_web_orders). Structure only — no data backfilled.
-- Idempotent — safe to re-run.
-- Deploy (local): mysql -u root -p db_abos_v0.1 < db/add_printful_integration.sql

USE db_abos_v0.1;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'printful_api_key_encrypted');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE dim_web_sites ADD COLUMN printful_api_key_encrypted TEXT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'printful_store_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE dim_web_sites ADD COLUMN printful_store_id VARCHAR(50) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'printful_last_error');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE dim_web_sites ADD COLUMN printful_last_error TEXT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_product_variants' AND COLUMN_NAME = 'printful_variant_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE dim_web_product_variants ADD COLUMN printful_variant_id VARCHAR(50) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_web_orders' AND COLUMN_NAME = 'printful_order_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_web_orders ADD COLUMN printful_order_id BIGINT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_web_orders' AND COLUMN_NAME = 'printful_status');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_web_orders ADD COLUMN printful_status VARCHAR(30) NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_web_orders' AND COLUMN_NAME = 'printful_last_error');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE fact_web_orders ADD COLUMN printful_last_error TEXT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;