import mysql, { Pool } from 'mysql2/promise'; import { ShopRecord, MetaConnectionRecord, MetaPixelRecord, CatalogSyncRecord, CatalogProductRecord, CapiEventRecord, WebhookEventRecord, AuditLogRecord, PricingPlan, FeatureDefinition, CatalogRecord, CatalogSelectionRecord, CatalogItemRecord, CatalogSyncLogRecord, AppConfigurationRecord, AdminUserRecord, AppInstallationRecord, SubscriptionRecord, BillingEventRecord, StoreActivityLogRecord, SystemBrandingRecord, } from '@/types'; import { env } from '@/lib/config/env'; import { encrypt } from '@/lib/crypto/encryption'; import { SHOPIFY_CONFIG } from '@/config/shopify'; import { normalizeAssetUrl } from '@/lib/utils/asset'; export const DEFAULT_PLANS: Record = { basic: { id: 'basic', name: 'Free Plan', description: 'Essential Meta Pixel & basic catalog sync for new stores', price: 0, yearly_price: 0, currency: 'USD', interval: 'month', yearly_discount_label: 'Free Forever', display_order: 1, is_visible: true, sku_limit: 100, campaigns_limit: 1, trial_days: 0, capi_enabled: true, supplementary_feeds: false, custom_titles: false, website_audit: false, dedicated_manager: false, support: '24/7 Support', features: [ '1 Meta Pixel, 1 Conversion API', '5 orders per month quota', '1 catalog feed, weekly sync', 'Up to 100 SKUs feed upload', 'Order reconciliation & report', 'Advanced tracking rules (COD / Subs)', 'Pixel Analytics & Health (Basic)', 'Layer 1 Bot Protection', 'Audience report: who buys, who returns', '24/7 Support', ], badge: 'Free', is_active: true, }, growth: { id: 'growth', name: 'Growth Plan', description: 'Unlimited pixels, daily feed sync, and conversions API for growing brands', price: 13, yearly_price: 124.80, currency: 'USD', interval: 'month', yearly_discount_label: 'Save 20%', display_order: 2, is_visible: true, sku_limit: 5000, campaigns_limit: 3, trial_days: 14, capi_enabled: true, supplementary_feeds: true, custom_titles: true, website_audit: false, dedicated_manager: false, support: '24/7 Priority Chat & Email', features: [ 'Everything in Free Plan', 'Unlimited Meta Pixels', 'Conversions API (1 Dataset included, +$5.99/mo per extra)', 'Unlimited Order Tracking Quota', '3 Catalog Feeds (XML / CSV)', '100 – 5,000 SKUs feed upload', 'Standard (Daily) feed sync frequency', 'Advanced Pixel Analytics & Health', 'Live Event Stream & Activities', 'Real-Time Ads Report & UTM Attribution', 'Layer 1 + Layer 2 Bot Protection Blocking', '24/7 Priority Chat & Email Support', ], badge: 'Most Popular', is_active: true, }, scale: { id: 'scale', name: 'Scale Plan', description: 'Full bot protection, audience sync, market routing, and real-time sync for scaling stores', price: 99, yearly_price: 950.40, currency: 'USD', interval: 'month', yearly_discount_label: 'Save 20%', display_order: 3, is_visible: true, sku_limit: null, // Unlimited campaigns_limit: null, // Unlimited trial_days: 14, capi_enabled: true, supplementary_feeds: true, custom_titles: true, website_audit: true, dedicated_manager: true, support: 'Highest Priority 24/7 + VIP Community', features: [ 'Everything in Growth Plan', 'Unlimited Meta Pixels & Unlimited CAPI Datasets', 'Unlimited Orders & Unlimited Catalog Feeds', 'Unlimited SKUs upload to Meta', 'Specific-Market Pixel Routing', 'Advanced (Daily + Real-time) Feed Sync', 'Full Bot Protection + Manual Resend Queue', 'Restricted Mode (Consent Policy / GDPR / CCPA)', 'Meta Audiences Sync (Lookalikes & Retargeting)', 'Highest Priority 24/7 + VIP Community Support', ], badge: 'Scale & Enterprise', is_active: true, }, accelerate: { id: 'accelerate', name: 'Scale Plan', description: 'Full bot protection, audience sync, market routing, and real-time sync for scaling stores', price: 99, yearly_price: 950.40, currency: 'USD', interval: 'month', yearly_discount_label: 'Save 20%', display_order: 4, is_visible: true, sku_limit: null, campaigns_limit: null, trial_days: 14, capi_enabled: true, supplementary_feeds: true, custom_titles: true, website_audit: true, dedicated_manager: true, support: 'Highest Priority 24/7 + VIP Community', features: [ 'Everything in Growth Plan', 'Unlimited Meta Pixels & Unlimited CAPI Datasets', 'Unlimited Orders & Unlimited Catalog Feeds', 'Unlimited SKUs upload to Meta', 'Specific-Market Pixel Routing', 'Advanced (Daily + Real-time) Feed Sync', 'Full Bot Protection + Manual Resend Queue', 'Restricted Mode (Consent Policy / GDPR / CCPA)', 'Meta Audiences Sync (Lookalikes & Retargeting)', 'Highest Priority 24/7 + VIP Community Support', ], badge: 'Scale & Enterprise', is_active: true, }, }; export const DEFAULT_FEATURE_DEFINITIONS: FeatureDefinition[] = [ { id: 'meta.pixel_count', name: 'Meta Pixel Count', description: 'Number of active Facebook pixels that can be installed on your Shopify storefront.', display_order: 1, is_visible: true, category: 'Tracking & Pixels', basic_value: '1 Pixel', growth_value: 'Unlimited', scale_value: 'Unlimited', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: 1, growth_limit: null, scale_limit: null, }, { id: 'meta.capi_tracking', name: 'Conversions API (CAPI)', description: 'Direct server-to-server event dispatching to Meta bypassing ad blockers and iOS 14+ restrictions.', display_order: 2, is_visible: true, category: 'Tracking & Pixels', basic_value: '1 Dataset', growth_value: '1 Dataset (+$5.99/mo per extra)', scale_value: 'Unlimited', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: 1, growth_limit: 1, scale_limit: null, }, { id: 'meta.order_quota', name: 'Order Tracking Quota', description: 'Monthly volume of purchases and checkout transactions captured and sent to Meta.', display_order: 3, is_visible: true, category: 'Tracking & Pixels', basic_value: '5 orders / month', growth_value: 'Unlimited', scale_value: 'Unlimited', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: 5, growth_limit: null, scale_limit: null, }, { id: 'meta.advanced_tracking_rules', name: 'Advanced Tracking Rules (COD / Subs / Bundles)', description: 'Granular server-side filters for Cash on Delivery, subscription renewals, and bundle products.', display_order: 4, is_visible: true, category: 'Tracking & Pixels', basic_value: 'Yes', growth_value: 'Yes', scale_value: 'Yes', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: null, growth_limit: null, scale_limit: null, }, { id: 'meta.specific_market_routing', name: 'Specific-Market Pixel Routing', description: 'Route store buyers from specific international markets/countries to dedicated Meta Pixels.', display_order: 5, is_visible: true, category: 'Advanced Routing', basic_value: 'No', growth_value: 'No', scale_value: 'Yes', basic_enabled: false, growth_enabled: false, scale_enabled: true, basic_limit: 0, growth_limit: 0, scale_limit: null, }, { id: 'meta.catalog_feeds', name: 'Catalog Feeds (XML / CSV)', description: 'Automated catalog feed generation formatted for Meta Commerce Manager and Instagram Shopping.', display_order: 6, is_visible: true, category: 'Catalog & Products', basic_value: '1 Feed', growth_value: '3 Feeds', scale_value: 'Unlimited', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: 1, growth_limit: 3, scale_limit: null, }, { id: 'meta.sku_limit', name: 'Products for Catalog Feed', description: 'Maximum catalog SKU count synchronized between Shopify and Meta Commerce Manager.', display_order: 7, is_visible: true, category: 'Catalog & Products', basic_value: 'Up to 100 SKUs', growth_value: '100 – 5,000 SKUs', scale_value: 'Unlimited', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: 100, growth_limit: 5000, scale_limit: null, }, { id: 'meta.sync_frequency', name: 'Catalog Feed Sync Frequency', description: 'Rate at which product inventory, pricing, and variants are synchronized with Meta.', display_order: 8, is_visible: true, category: 'Catalog & Products', basic_value: 'Basic (Weekly)', growth_value: 'Standard (Daily)', scale_value: 'Advanced (Daily + Real-time)', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: null, growth_limit: null, scale_limit: null, }, { id: 'meta.order_reconciliation', name: 'Order Reconciliation & Report', description: 'Comprehensive audit reporting comparing Shopify confirmed orders against Meta attribution.', display_order: 9, is_visible: true, category: 'Reports & Analytics', basic_value: 'Yes', growth_value: 'Yes', scale_value: 'Yes', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: null, growth_limit: null, scale_limit: null, }, { id: 'meta.pixel_analytics', name: 'Pixel Analytics & Health Monitoring', description: 'Real-time pixel health scores, Event Match Quality (EMQ), diagnostic alerts, and delivery telemetry.', display_order: 10, is_visible: true, category: 'Reports & Analytics', basic_value: 'Basic', growth_value: 'Advanced', scale_value: 'Advanced', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: null, growth_limit: null, scale_limit: null, }, { id: 'meta.live_event_stream', name: 'Live Event Stream & Activities', description: 'Real-time event inspector showing live PageView, AddToCart, InitiateCheckout, and Purchase payloads.', display_order: 11, is_visible: true, category: 'Reports & Analytics', basic_value: 'No', growth_value: 'Yes', scale_value: 'Yes', basic_enabled: false, growth_enabled: true, scale_enabled: true, basic_limit: 0, growth_limit: null, scale_limit: null, }, { id: 'meta.realtime_ads_report', name: 'Real-Time Ads Report & UTM Attribution', description: 'Live campaign ROAS, UTM source attribution, conversion rates, and revenue performance analytics.', display_order: 12, is_visible: true, category: 'Reports & Analytics', basic_value: 'No', growth_value: 'Yes', scale_value: 'Yes', basic_enabled: false, growth_enabled: true, scale_enabled: true, basic_limit: 0, growth_limit: null, scale_limit: null, }, { id: 'meta.bot_protection', name: 'Bot & Fraud Protection', description: 'Multi-layer server-side bot filtering and fraudulent traffic suppression before dispatch to Meta.', display_order: 13, is_visible: true, category: 'Security & Compliance', basic_value: 'Layer 1 (Verified Bots)', growth_value: 'Layer 1 + Layer 2 Blocking', scale_value: 'Full Protection + Manual Resend', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: 1, growth_limit: 2, scale_limit: 3, }, { id: 'meta.restricted_mode', name: 'Restricted Mode (Consent Policy / GDPR)', description: 'Strict adherence to GDPR, CCPA, and Shopify Customer Privacy API consent frameworks.', display_order: 14, is_visible: true, category: 'Security & Compliance', basic_value: 'No', growth_value: 'No', scale_value: 'Yes', basic_enabled: false, growth_enabled: false, scale_enabled: true, basic_limit: 0, growth_limit: 0, scale_limit: null, }, { id: 'meta.audience_report', name: 'Audience Report (Who Buys, Who Returns)', description: 'Behavioral reports analyzing buyer demographics, recurring purchasing patterns, and return rates.', display_order: 15, is_visible: true, category: 'Audiences & Sync', basic_value: 'Yes', growth_value: 'Yes', scale_value: 'Yes', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: null, growth_limit: null, scale_limit: null, }, { id: 'meta.meta_audiences_sync', name: 'Meta Audiences Sync (Lookalikes & Retargeting)', description: 'Automatic sync of high-intent custom audience segments directly into Meta Ads Manager.', display_order: 16, is_visible: true, category: 'Audiences & Sync', basic_value: 'No', growth_value: 'No', scale_value: 'Yes', basic_enabled: false, growth_enabled: false, scale_enabled: true, basic_limit: 0, growth_limit: 0, scale_limit: null, }, { id: 'meta.support_level', name: 'Customer Support Service Level', description: 'Dedicated support channel, guaranteed response SLAs, and configuration assistance.', display_order: 17, is_visible: true, category: 'Support & Services', basic_value: '24/7 Support', growth_value: '24/7 Priority Chat & Email', scale_value: 'Highest Priority 24/7 + VIP Community', basic_enabled: true, growth_enabled: true, scale_enabled: true, basic_limit: null, growth_limit: null, scale_limit: null, }, ]; export class MySQLDatabaseStore { private pool: Pool | null = null; private initPromise: Promise | null = null; private getPool(): Pool { if (!this.pool) { this.pool = mysql.createPool({ host: env.database.host, port: env.database.port, user: env.database.user, password: env.database.password, database: env.database.database, waitForConnections: true, connectionLimit: 10, queueLimit: 0, }); } return this.pool; } private async getInitializedPool(): Promise { await this.initialize(); return this.getPool(); } private async ensureColumnExists(pool: Pool, table: string, column: string, definition: string): Promise { try { const [cols]: [any[], any] = await pool.query( `SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND COLUMN_NAME = ?`, [env.database.database, table, column] ); if (cols.length === 0) { await pool.query(`ALTER TABLE \`${table}\` ADD COLUMN \`${column}\` ${definition}`); } } catch { // ignore } } public async initialize(): Promise { if (this.initPromise) { return this.initPromise; } this.initPromise = (async () => { try { // 1. Ensure database exists const rootConn = await mysql.createConnection({ host: env.database.host, port: env.database.port, user: env.database.user, password: env.database.password, }); await rootConn.query(`CREATE DATABASE IF NOT EXISTS \`${env.database.database}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`); await rootConn.end(); const pool = this.getPool(); // 2. Create tables with created_at and updated_at await pool.query(` CREATE TABLE IF NOT EXISTS shops ( id VARCHAR(128) PRIMARY KEY, shopify_domain VARCHAR(255) UNIQUE NOT NULL, shopify_shop_id VARCHAR(255), access_token_encrypted TEXT, scopes JSON, installed_at VARCHAR(64), uninstalled_at VARCHAR(64), status VARCHAR(64) DEFAULT 'installed', plan VARCHAR(64) DEFAULT 'growth', plan_custom_sku_limit INT NULL, subscription_id VARCHAR(255) NULL, subscription_status VARCHAR(64) NULL, trial_days INT DEFAULT 0, trial_starts_at VARCHAR(64) NULL, trial_ends_at VARCHAR(64) NULL, current_period_end VARCHAR(64) NULL, created_at VARCHAR(64), updated_at VARCHAR(64) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS app_configurations ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, shopify_domain VARCHAR(255) NOT NULL, shopify_access_token_encrypted TEXT, shopify_api_key VARCHAR(255), shopify_api_secret_encrypted TEXT, shopify_api_version VARCHAR(64) DEFAULT '2024-07', meta_business_id VARCHAR(255), meta_business_name VARCHAR(255), meta_catalog_id VARCHAR(255), meta_catalog_name VARCHAR(255), meta_pixel_id VARCHAR(255), meta_pixel_name VARCHAR(255), meta_access_token_encrypted TEXT, meta_test_event_code VARCHAR(255), created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_config_shop (shop_id), INDEX idx_config_domain (shopify_domain) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS meta_connections ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, business_id VARCHAR(255), business_name VARCHAR(255), catalog_id VARCHAR(255), catalog_name VARCHAR(255), pixel_id VARCHAR(255), pixel_name VARCHAR(255), access_token_encrypted TEXT, test_event_code VARCHAR(255), token_expires_at VARCHAR(64), status VARCHAR(64) DEFAULT 'connected', created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_meta_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS catalog_products ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, shopify_product_id VARCHAR(255), shopify_variant_id VARCHAR(255), meta_product_id VARCHAR(255), sku VARCHAR(255), barcode VARCHAR(255), title VARCHAR(512), variant_title VARCHAR(512), description LONGTEXT, price DECIMAL(10,2) DEFAULT 0.00, compare_at_price DECIMAL(10,2) NULL, currency VARCHAR(16) DEFAULT 'USD', availability VARCHAR(64) DEFAULT 'in stock', inventory_quantity INT DEFAULT 0, image_url TEXT, additional_images JSON, product_url TEXT, google_product_category VARCHAR(255), fb_product_category VARCHAR(255), brand VARCHAR(255), \`condition\` VARCHAR(64) DEFAULT 'new', custom_label_0 VARCHAR(255), custom_label_1 VARCHAR(255), custom_label_2 VARCHAR(255), custom_label_3 VARCHAR(255), custom_label_4 VARCHAR(255), status VARCHAR(64) DEFAULT 'synced', sync_error TEXT, last_synced_at VARCHAR(64), created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_prod_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS catalog_syncs ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, sync_type VARCHAR(64) DEFAULT 'batch_api', status VARCHAR(64) DEFAULT 'completed', total_products INT DEFAULT 0, processed_products INT DEFAULT 0, failed_products INT DEFAULT 0, warning_count INT DEFAULT 0, started_at VARCHAR(64), completed_at VARCHAR(64), error_message TEXT, details JSON, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_sync_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS capi_events ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, event_id VARCHAR(255), event_name VARCHAR(128), event_source_url TEXT, shopify_order_id VARCHAR(255), payload_hash VARCHAR(255), user_data_masked JSON, custom_data JSON, status VARCHAR(64) DEFAULT 'sent', meta_event_id VARCHAR(255), response_code INT DEFAULT 200, response_body JSON, error_message TEXT, duration_ms INT DEFAULT 0, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_capi_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS webhook_events ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128), topic VARCHAR(255), shopify_webhook_id VARCHAR(255), payload_json LONGTEXT, status VARCHAR(64) DEFAULT 'queued', attempts INT DEFAULT 0, error_message TEXT, created_at VARCHAR(64), processed_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_webhook_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS audit_logs ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, action VARCHAR(255), actor VARCHAR(64) DEFAULT 'system', metadata JSON, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_audit_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS pricing_plans ( id VARCHAR(128) PRIMARY KEY, name VARCHAR(255), price DECIMAL(10,2) DEFAULT 0.00, \`interval\` VARCHAR(32) DEFAULT 'month', sku_limit INT NULL, campaigns_limit INT NULL, trial_days INT DEFAULT 0, capi_enabled BOOLEAN DEFAULT FALSE, supplementary_feeds BOOLEAN DEFAULT FALSE, custom_titles BOOLEAN DEFAULT FALSE, website_audit BOOLEAN DEFAULT FALSE, dedicated_manager BOOLEAN DEFAULT FALSE, support VARCHAR(255), features JSON, badge VARCHAR(255), is_active BOOLEAN DEFAULT TRUE, created_at VARCHAR(64), updated_at VARCHAR(64) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS meta_pixels ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, pixel_name VARCHAR(255) NOT NULL, pixel_id VARCHAR(255) NOT NULL, pixel_type VARCHAR(64) DEFAULT 'master', target_collection VARCHAR(255) DEFAULT 'all', target_tag VARCHAR(255) DEFAULT NULL, access_token_encrypted TEXT, test_event_code VARCHAR(128) DEFAULT NULL, capi_enabled BOOLEAN DEFAULT TRUE, emq_score DECIMAL(3,1) DEFAULT 8.6, events_count_24h INT DEFAULT 0, status VARCHAR(64) DEFAULT 'active', app_embed_active BOOLEAN DEFAULT TRUE, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_pix_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS catalogs ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, fb_catalog_id VARCHAR(128) NOT NULL, fb_business_id VARCHAR(128) NOT NULL, fb_pixel_id VARCHAR(128) NULL, name VARCHAR(255) NOT NULL, sync_scope VARCHAR(64) DEFAULT 'all', variant_mode VARCHAR(64) DEFAULT 'all_separate', google_category_id VARCHAR(128) NOT NULL, google_category_name VARCHAR(255) NOT NULL, additional_images BOOLEAN DEFAULT TRUE, link_domain VARCHAR(64) DEFAULT 'myshopify', custom_domain VARCHAR(255) NULL, exclude_out_of_stock BOOLEAN DEFAULT FALSE, price_mode VARCHAR(64) DEFAULT 'compare_at_and_price', schedule_type VARCHAR(64) DEFAULT 'weekly', schedule_day VARCHAR(64) DEFAULT 'Every Monday', schedule_hour INT DEFAULT 2, schedule_period VARCHAR(16) DEFAULT 'AM', schedule_tz VARCHAR(128) DEFAULT 'UTC', auto_sync BOOLEAN DEFAULT TRUE, pixel_tracking BOOLEAN DEFAULT TRUE, status VARCHAR(64) DEFAULT 'synced', last_synced_at VARCHAR(64) NULL, next_sync_at VARCHAR(64) NULL, last_error TEXT NULL, fb_catalog_url TEXT NULL, product_count INT DEFAULT 0, variant_count INT DEFAULT 0, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_cat_shop (shop_id), INDEX idx_cat_fbid (fb_catalog_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS catalog_selections ( id VARCHAR(128) PRIMARY KEY, catalog_id VARCHAR(128) NOT NULL, type VARCHAR(64) NOT NULL, shopify_gid VARCHAR(255) NOT NULL, value VARCHAR(255) NOT NULL, is_exclude BOOLEAN DEFAULT FALSE, INDEX idx_sel_cat (catalog_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS catalog_items ( id VARCHAR(128) PRIMARY KEY, catalog_id VARCHAR(128) NOT NULL, shopify_product_id VARCHAR(128) NOT NULL, shopify_variant_id VARCHAR(128) NOT NULL, retailer_id VARCHAR(255) NOT NULL, payload_hash VARCHAR(128) NOT NULL, fb_status VARCHAR(64) DEFAULT 'synced', last_error TEXT NULL, synced_at VARCHAR(64), INDEX idx_item_cat (catalog_id), INDEX idx_item_ret (retailer_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS catalog_sync_logs ( id VARCHAR(128) PRIMARY KEY, catalog_id VARCHAR(128) NOT NULL, \`trigger\` VARCHAR(64) DEFAULT 'manual', status VARCHAR(64) DEFAULT 'completed', created_count INT DEFAULT 0, updated_count INT DEFAULT 0, deleted_count INT DEFAULT 0, failed_count INT DEFAULT 0, batch_handles JSON NULL, error_details TEXT NULL, started_at VARCHAR(64), finished_at VARCHAR(64), INDEX idx_log_cat (catalog_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS admin_users ( id VARCHAR(128) PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, role VARCHAR(64) DEFAULT 'super_admin', avatar_url VARCHAR(512) NULL, is_active BOOLEAN DEFAULT TRUE, last_login_at VARCHAR(64) NULL, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_admin_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS app_installations ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, shopify_domain VARCHAR(255) NOT NULL, event_type VARCHAR(64) NOT NULL, plan_at_time VARCHAR(64) DEFAULT 'growth', metadata JSON NULL, occurred_at VARCHAR(64) NOT NULL, created_at VARCHAR(64), INDEX idx_inst_shop (shop_id), INDEX idx_inst_domain (shopify_domain), INDEX idx_inst_event (event_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS subscriptions ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, shopify_domain VARCHAR(255) NOT NULL, shopify_subscription_id VARCHAR(255) NULL, plan_id VARCHAR(64) NOT NULL, plan_name VARCHAR(255) NOT NULL, status VARCHAR(64) DEFAULT 'active', billing_cycle VARCHAR(32) DEFAULT 'monthly', price DECIMAL(10,2) DEFAULT 0.00, currency VARCHAR(16) DEFAULT 'USD', trial_days INT DEFAULT 0, trial_starts_at VARCHAR(64) NULL, trial_ends_at VARCHAR(64) NULL, current_period_start VARCHAR(64) NULL, current_period_end VARCHAR(64) NULL, cancelled_at VARCHAR(64) NULL, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_sub_shop (shop_id), INDEX idx_sub_domain (shopify_domain), INDEX idx_sub_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS billing_events ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, shopify_domain VARCHAR(255) NOT NULL, subscription_id VARCHAR(128) NULL, event_type VARCHAR(64) NOT NULL, amount DECIMAL(10,2) DEFAULT 0.00, currency VARCHAR(16) DEFAULT 'USD', status VARCHAR(64) DEFAULT 'verified', shopify_charge_id VARCHAR(255) NULL, details JSON NULL, billing_date VARCHAR(64) NOT NULL, created_at VARCHAR(64), INDEX idx_bill_shop (shop_id), INDEX idx_bill_sub (subscription_id), INDEX idx_bill_event (event_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS store_activity_logs ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, shopify_domain VARCHAR(255) NOT NULL, action VARCHAR(128) NOT NULL, actor VARCHAR(64) DEFAULT 'system', description TEXT NULL, metadata JSON NULL, created_at VARCHAR(64), INDEX idx_act_shop (shop_id), INDEX idx_act_domain (shopify_domain), INDEX idx_act_action (action) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS system_branding ( id VARCHAR(64) PRIMARY KEY, app_name VARCHAR(255) NOT NULL, logo_url TEXT NULL, favicon_url TEXT NULL, created_at VARCHAR(64), updated_at VARCHAR(64), updated_by VARCHAR(128) DEFAULT 'system' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` INSERT IGNORE INTO system_branding (id, app_name, logo_url, favicon_url, created_at, updated_at, updated_by) VALUES ('default_branding', 'DigiPixora — Meta Pixel & Ads', '/meta-connector/images/logo.png', '/meta-connector/favicon.ico', NOW(), NOW(), 'system'); `); await pool.query(` UPDATE system_branding SET logo_url = '/meta-connector/images/logo.png' WHERE logo_url = '/images/logo.png' OR logo_url IS NULL; `); await pool.query(` UPDATE system_branding SET favicon_url = '/meta-connector/favicon.ico' WHERE favicon_url = '/favicon.ico' OR favicon_url IS NULL; `); await pool.query(` CREATE TABLE IF NOT EXISTS ad_campaigns ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, name VARCHAR(255) NOT NULL, status VARCHAR(32) NOT NULL DEFAULT 'ACTIVE', budget DECIMAL(10,2) NOT NULL DEFAULT 25.00, spend DECIMAL(10,2) NOT NULL DEFAULT 0.00, impressions INT NOT NULL DEFAULT 0, clicks INT NOT NULL DEFAULT 0, ctr DECIMAL(5,2) NOT NULL DEFAULT 0.00, cpc DECIMAL(10,2) NOT NULL DEFAULT 0.00, purchases INT NOT NULL DEFAULT 0, revenue DECIMAL(10,2) NOT NULL DEFAULT 0.00, roas DECIMAL(5,2) NOT NULL DEFAULT 0.00, created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_campaigns_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await pool.query(` CREATE TABLE IF NOT EXISTS ad_audiences ( id VARCHAR(128) PRIMARY KEY, shop_id VARCHAR(128) NOT NULL, name VARCHAR(255) NOT NULL, type VARCHAR(64) NOT NULL, rule TEXT NOT NULL, size INT NOT NULL DEFAULT 0, match_rate VARCHAR(32) NOT NULL DEFAULT '—', last_synced VARCHAR(64) NOT NULL DEFAULT 'Awaiting sync', status VARCHAR(32) NOT NULL DEFAULT 'READY', created_at VARCHAR(64), updated_at VARCHAR(64), INDEX idx_audiences_shop (shop_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); // 3. Ensure updated_at & created_at exist on all tables (migration) const tables = [ 'shops', 'meta_connections', 'meta_pixels', 'catalog_products', 'catalog_syncs', 'capi_events', 'webhook_events', 'audit_logs', 'pricing_plans', 'catalogs', 'admin_users', 'subscriptions', ]; for (const t of tables) { await this.ensureColumnExists(pool, t, 'created_at', 'VARCHAR(64)'); await this.ensureColumnExists(pool, t, 'updated_at', 'VARCHAR(64)'); } await this.ensureColumnExists(pool, 'shops', 'trial_days', 'INT DEFAULT 0'); await this.ensureColumnExists(pool, 'shops', 'trial_starts_at', 'VARCHAR(64) NULL'); await this.ensureColumnExists(pool, 'shops', 'trial_ends_at', 'VARCHAR(64) NULL'); await this.ensureColumnExists(pool, 'shops', 'shop_name', 'VARCHAR(255) NULL'); await this.ensureColumnExists(pool, 'shops', 'owner_email', 'VARCHAR(255) NULL'); await this.ensureColumnExists(pool, 'shops', 'owner_name', 'VARCHAR(255) NULL'); await this.ensureColumnExists(pool, 'shops', 'country', "VARCHAR(64) DEFAULT 'US'"); await this.ensureColumnExists(pool, 'shops', 'currency', "VARCHAR(16) DEFAULT 'USD'"); await this.ensureColumnExists(pool, 'shops', 'billing_cycle', "VARCHAR(32) DEFAULT 'monthly'"); await this.ensureColumnExists(pool, 'shops', 'monthly_revenue', 'DECIMAL(10,2) DEFAULT 0.00'); await this.ensureColumnExists(pool, 'shops', 'last_active_at', 'VARCHAR(64) NULL'); await this.ensureColumnExists(pool, 'shops', 'refresh_token_encrypted', 'TEXT NULL'); await this.ensureColumnExists(pool, 'shops', 'token_expires_at', 'VARCHAR(64) NULL'); await this.ensureColumnExists(pool, 'shops', 'refresh_token_expires_at', 'VARCHAR(64) NULL'); await this.ensureColumnExists(pool, 'shops', 'token_updated_at', 'VARCHAR(64) NULL'); await this.ensureColumnExists(pool, 'pricing_plans', 'description', 'TEXT NULL'); await this.ensureColumnExists(pool, 'pricing_plans', 'yearly_price', 'DECIMAL(10,2) DEFAULT 0.00'); await this.ensureColumnExists(pool, 'pricing_plans', 'currency', "VARCHAR(16) DEFAULT 'USD'"); await this.ensureColumnExists(pool, 'pricing_plans', 'yearly_discount_label', "VARCHAR(255) DEFAULT 'Save 20%'"); await this.ensureColumnExists(pool, 'pricing_plans', 'display_order', 'INT DEFAULT 0'); await this.ensureColumnExists(pool, 'pricing_plans', 'is_visible', 'BOOLEAN DEFAULT TRUE'); await this.ensureColumnExists(pool, 'pricing_plans', 'feature_flags', 'JSON NULL'); await this.ensureColumnExists(pool, 'pricing_plans', 'feature_limits', 'JSON NULL'); await this.ensureColumnExists(pool, 'pricing_plans', 'trial_days', 'INT DEFAULT 0'); await this.ensureColumnExists(pool, 'pricing_plans', 'is_active', 'BOOLEAN DEFAULT TRUE'); await pool.query(` CREATE TABLE IF NOT EXISTS feature_definitions ( id VARCHAR(128) PRIMARY KEY, name VARCHAR(255) NOT NULL, description TEXT NULL, display_order INT DEFAULT 0, is_visible BOOLEAN DEFAULT TRUE, category VARCHAR(64) DEFAULT 'General', basic_value VARCHAR(255) DEFAULT '', growth_value VARCHAR(255) DEFAULT '', scale_value VARCHAR(255) DEFAULT '', basic_enabled BOOLEAN DEFAULT FALSE, growth_enabled BOOLEAN DEFAULT TRUE, scale_enabled BOOLEAN DEFAULT TRUE, basic_limit INT NULL, growth_limit INT NULL, scale_limit INT NULL, created_at VARCHAR(64), updated_at VARCHAR(64) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; `); await this.ensureColumnExists(pool, 'catalog_products', 'is_excluded', 'BOOLEAN DEFAULT FALSE'); await this.ensureColumnExists(pool, 'catalog_products', 'video_url', 'TEXT NULL'); await this.ensureColumnExists(pool, 'catalog_products', 'weight', 'DECIMAL(10,2) NULL'); await this.ensureColumnExists(pool, 'catalog_products', 'weight_unit', 'VARCHAR(32) NULL'); await this.ensureColumnExists(pool, 'catalog_products', 'body_html', 'LONGTEXT NULL'); await this.ensureColumnExists(pool, 'catalog_products', 'tags', 'TEXT NULL'); await this.ensureColumnExists(pool, 'catalog_products', 'collections', 'TEXT NULL'); await this.ensureColumnExists(pool, 'catalog_products', 'collection_ids', 'TEXT NULL'); await this.ensureColumnExists(pool, 'meta_connections', 'onboarding_completed', 'BOOLEAN DEFAULT FALSE'); await this.ensureColumnExists(pool, 'meta_connections', 'fb_user_id', 'VARCHAR(128) NULL'); await this.ensureColumnExists(pool, 'meta_connections', 'fb_user_name', 'VARCHAR(255) NULL'); await this.ensureColumnExists(pool, 'meta_connections', 'connected_at', 'VARCHAR(64) NULL'); await this.ensureColumnExists(pool, 'meta_pixels', 'source', "VARCHAR(32) DEFAULT 'manual'"); await this.ensureColumnExists(pool, 'meta_pixels', 'facebook_account_id', 'VARCHAR(128) NULL'); await this.ensureColumnExists(pool, 'meta_pixels', 'pages_config', "VARCHAR(255) DEFAULT 'all'"); await this.ensureColumnExists(pool, 'meta_pixels', 'capi_status', "VARCHAR(64) DEFAULT 'not_configured'"); await this.ensureColumnExists(pool, 'meta_pixels', 'verification_status', "VARCHAR(32) DEFAULT 'unverified'"); await this.ensureColumnExists(pool, 'meta_pixels', 'is_active', 'TINYINT(1) DEFAULT 1'); await this.ensureColumnExists(pool, 'catalogs', 'exclude_out_of_stock', 'BOOLEAN DEFAULT FALSE'); await this.ensureColumnExists(pool, 'catalogs', 'custom_domain', 'VARCHAR(255) NULL'); await this.ensureColumnExists(pool, 'catalogs', 'facebook_account_id', 'VARCHAR(128) NULL'); await this.ensureColumnExists(pool, 'catalogs', 'sync_rules', 'JSON NULL'); try { const [uqIdx]: [any[], any] = await pool.query("SHOW INDEX FROM meta_pixels WHERE Key_name = 'uq_shop_pixel'"); if (uqIdx.length === 0) { await pool.query('ALTER TABLE meta_pixels ADD UNIQUE INDEX uq_shop_pixel (shop_id, pixel_id)'); } } catch {} // 4. Seed Initial Data directly await this.seedDataDirect(pool); } catch (err) { console.error('MySQL database initialization failed:', err); this.initPromise = null; throw err; } })(); return this.initPromise; } private async seedDataDirect(pool: Pool): Promise { const now = new Date().toISOString(); const liveDomain = SHOPIFY_CONFIG.defaultShopDomain; // 1. Seed Pricing Plans (INSERT ... ON DUPLICATE KEY UPDATE) for (const plan of Object.values(DEFAULT_PLANS)) { await pool.query( `INSERT INTO pricing_plans (id, name, description, price, yearly_price, currency, \`interval\`, yearly_discount_label, display_order, is_visible, sku_limit, campaigns_limit, trial_days, capi_enabled, supplementary_feeds, custom_titles, website_audit, dedicated_manager, support, features, badge, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), description = IF(description IS NULL, VALUES(description), description), price = IF(price = 0 AND VALUES(price) > 0, VALUES(price), price), yearly_price = IF(yearly_price = 0 AND VALUES(yearly_price) > 0, VALUES(yearly_price), yearly_price), currency = IF(currency IS NULL, VALUES(currency), currency), yearly_discount_label = IF(yearly_discount_label IS NULL, VALUES(yearly_discount_label), yearly_discount_label), display_order = IF(display_order = 0, VALUES(display_order), display_order), is_visible = IF(is_visible IS NULL, VALUES(is_visible), is_visible), capi_enabled = VALUES(capi_enabled), support = VALUES(support), features = IF(features IS NULL OR JSON_LENGTH(features) = 0, VALUES(features), features), badge = VALUES(badge), updated_at = VALUES(updated_at)`, [ plan.id, plan.name, plan.description || null, plan.price, plan.yearly_price ?? (plan.price * 12 * 0.8), plan.currency || 'USD', plan.interval || 'month', plan.yearly_discount_label || 'Save 20%', plan.display_order ?? 0, plan.is_visible !== false, plan.sku_limit, plan.campaigns_limit, plan.trial_days || 0, plan.capi_enabled, plan.supplementary_feeds, plan.custom_titles, plan.website_audit, plan.dedicated_manager, plan.support, JSON.stringify(plan.features), plan.badge || null, plan.is_active, now, now, ] ); } // 1b. Seed Feature Definitions (INSERT ... ON DUPLICATE KEY UPDATE) for (const feat of DEFAULT_FEATURE_DEFINITIONS) { await pool.query( `INSERT INTO feature_definitions (id, name, description, display_order, is_visible, category, basic_value, growth_value, scale_value, basic_enabled, growth_enabled, scale_enabled, basic_limit, growth_limit, scale_limit, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), display_order = VALUES(display_order), is_visible = VALUES(is_visible), category = VALUES(category), basic_value = VALUES(basic_value), growth_value = VALUES(growth_value), scale_value = VALUES(scale_value), basic_enabled = VALUES(basic_enabled), growth_enabled = VALUES(growth_enabled), scale_enabled = VALUES(scale_enabled), basic_limit = VALUES(basic_limit), growth_limit = VALUES(growth_limit), scale_limit = VALUES(scale_limit), updated_at = VALUES(updated_at)`, [ feat.id, feat.name, feat.description || null, feat.display_order, feat.is_visible, feat.category || 'General', feat.basic_value, feat.growth_value, feat.scale_value, feat.basic_enabled, feat.growth_enabled, feat.scale_enabled, feat.basic_limit ?? null, feat.growth_limit ?? null, feat.scale_limit ?? null, now, now, ] ); } // 2. Default stores are created dynamically when genuine merchants install via Shopify OAuth // 3. Seed Default Super Admin User const adminEmail = (env.admin?.email || process.env.ADMIN_EMAIL || 'admin@meta-connector.app').trim().toLowerCase(); const adminPassword = env.admin?.password || process.env.ADMIN_PASSWORD || 'admin1234'; const adminName = env.admin?.name || process.env.ADMIN_NAME || 'Sarah Jenkins (SaaS Owner)'; const adminRole = env.admin?.role || process.env.ADMIN_ROLE || 'super_admin'; const [adminRows]: [any[], any] = await pool.query('SELECT * FROM admin_users WHERE email = ?', [adminEmail]); if (adminRows.length === 0) { const salt = 'a8f4c9e2b107d394'; const crypto = await import('crypto'); const hash = crypto.pbkdf2Sync(adminPassword, salt, 10000, 64, 'sha512').toString('hex'); const passwordHash = `${salt}:${hash}`; await pool.query( `INSERT INTO admin_users (id, email, password_hash, name, role, avatar_url, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ 'admin_root_1', adminEmail, passwordHash, adminName, adminRole, 'https://images.unsplash.com/photo-1494790108377-be9c29b29330?w=150', 1, now, now, ] ); } } // --- Shops --- async getAllShops(): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM shops'); return rows.map(this.mapShopRow); } async getShopByDomain(domain: string): Promise { await this.initialize(); const clean = domain.trim().toLowerCase().replace(/^https?:\/\//, '').replace(/\/.*$/, ''); const cleanWithoutSuffix = clean.replace('.myshopify.com', ''); const cleanWithSuffix = cleanWithoutSuffix + '.myshopify.com'; const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM shops WHERE LOWER(shopify_domain) = LOWER(?) OR LOWER(shopify_domain) = LOWER(?) LIMIT 1', [cleanWithSuffix, cleanWithoutSuffix] ); if (rows.length === 0) return null; return this.mapShopRow(rows[0]); } async getShopById(id: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM shops WHERE id = ?', [id]); if (rows.length === 0) return null; return this.mapShopRow(rows[0]); } async getShop(idOrDomain: string): Promise { await this.initialize(); const shop = await this.getShopById(idOrDomain); if (shop) return shop; return this.getShopByDomain(idOrDomain); } async getDefaultShop(): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM shops LIMIT 1'); if (rows.length > 0) return this.mapShopRow(rows[0]); // Automatically initialize active store record for the configured Shopify shop const defaultDomain = SHOPIFY_CONFIG.defaultShopDomain || 'quickstart-1a79b82e.myshopify.com'; const cleanDomain = defaultDomain.toLowerCase().replace(/^https?:\/\//, '').replace(/\/.*$/, ''); const now = new Date().toISOString(); const defaultShop: ShopRecord = { id: 'shop_live_main', shopify_domain: cleanDomain, shopify_shop_id: 'gid://shopify/Shop/dev_active', shop_name: cleanDomain.split('.')[0] || 'My Store', owner_email: `merchant@${cleanDomain}`, owner_name: 'Store Owner', country: 'US', currency: 'USD', billing_cycle: 'monthly', plan: 'basic', status: 'installed', access_token_encrypted: '', scopes: ['read_products', 'write_products', 'read_inventory', 'read_customer_events'], monthly_revenue: 0.0, installed_at: now, created_at: now, updated_at: now, }; await this.upsertShop(defaultShop); return defaultShop; } async upsertShop(shop: ShopRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const updated = { ...shop, updated_at: now, created_at: shop.created_at || now }; await this.getPool().query( `INSERT INTO shops (id, shopify_domain, shopify_shop_id, shop_name, owner_email, owner_name, country, currency, billing_cycle, monthly_revenue, last_active_at, access_token_encrypted, refresh_token_encrypted, token_expires_at, refresh_token_expires_at, token_updated_at, scopes, installed_at, uninstalled_at, status, plan, plan_custom_sku_limit, subscription_id, subscription_status, trial_days, trial_starts_at, trial_ends_at, current_period_end, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE shopify_domain = VALUES(shopify_domain), shopify_shop_id = VALUES(shopify_shop_id), shop_name = COALESCE(VALUES(shop_name), shop_name), owner_email = COALESCE(VALUES(owner_email), owner_email), owner_name = COALESCE(VALUES(owner_name), owner_name), country = COALESCE(VALUES(country), country), currency = COALESCE(VALUES(currency), currency), billing_cycle = COALESCE(VALUES(billing_cycle), billing_cycle), monthly_revenue = VALUES(monthly_revenue), last_active_at = COALESCE(VALUES(last_active_at), last_active_at), access_token_encrypted = VALUES(access_token_encrypted), refresh_token_encrypted = COALESCE(VALUES(refresh_token_encrypted), refresh_token_encrypted), token_expires_at = COALESCE(VALUES(token_expires_at), token_expires_at), refresh_token_expires_at = COALESCE(VALUES(refresh_token_expires_at), refresh_token_expires_at), token_updated_at = COALESCE(VALUES(token_updated_at), token_updated_at), scopes = VALUES(scopes), installed_at = VALUES(installed_at), uninstalled_at = VALUES(uninstalled_at), status = VALUES(status), plan = VALUES(plan), plan_custom_sku_limit = VALUES(plan_custom_sku_limit), subscription_id = VALUES(subscription_id), subscription_status = VALUES(subscription_status), trial_days = VALUES(trial_days), trial_starts_at = VALUES(trial_starts_at), trial_ends_at = VALUES(trial_ends_at), current_period_end = VALUES(current_period_end), updated_at = VALUES(updated_at)`, [ updated.id, updated.shopify_domain, updated.shopify_shop_id || null, updated.shop_name || null, updated.owner_email || null, updated.owner_name || null, updated.country || 'US', updated.currency || 'USD', updated.billing_cycle || 'monthly', updated.monthly_revenue != null ? updated.monthly_revenue : 0, updated.last_active_at || null, updated.access_token_encrypted, updated.refresh_token_encrypted || null, updated.token_expires_at || null, updated.refresh_token_expires_at || null, updated.token_updated_at || updated.updated_at || now, JSON.stringify(updated.scopes || []), updated.installed_at, updated.uninstalled_at || null, updated.status, updated.plan, updated.plan_custom_sku_limit ?? null, updated.subscription_id || null, updated.subscription_status || null, updated.trial_days || 0, updated.trial_starts_at || null, updated.trial_ends_at || null, updated.current_period_end || null, updated.created_at, updated.updated_at, ] ); return updated; } async updateShopPlan(shopId: string, plan: string): Promise { await this.initialize(); const now = new Date().toISOString(); await this.getPool().query( 'UPDATE shops SET plan = ?, updated_at = ? WHERE id = ? OR shopify_domain = ?', [plan, now, shopId, shopId] ); } async deleteShop(shopId: string): Promise { await this.initialize(); await this.getPool().query('DELETE FROM app_installations WHERE shop_id = ? OR shopify_domain = ?', [shopId, shopId]); await this.getPool().query('DELETE FROM subscriptions WHERE shop_id = ? OR shopify_domain = ?', [shopId, shopId]); await this.getPool().query('DELETE FROM billing_events WHERE shop_id = ? OR shopify_domain = ?', [shopId, shopId]); await this.getPool().query('DELETE FROM store_activity_logs WHERE shop_id = ? OR shopify_domain = ?', [shopId, shopId]); await this.getPool().query('DELETE FROM capi_events WHERE shop_id = ?', [shopId]); await this.getPool().query('DELETE FROM catalog_products WHERE shop_id = ?', [shopId]); await this.getPool().query('DELETE FROM catalog_syncs WHERE shop_id = ?', [shopId]); await this.getPool().query('DELETE FROM catalog_items WHERE catalog_id IN (SELECT id FROM catalogs WHERE shop_id = ?)', [shopId]); await this.getPool().query('DELETE FROM catalog_sync_logs WHERE catalog_id IN (SELECT id FROM catalogs WHERE shop_id = ?)', [shopId]); await this.getPool().query('DELETE FROM catalogs WHERE shop_id = ?', [shopId]); await this.getPool().query('DELETE FROM meta_connections WHERE shop_id = ?', [shopId]); await this.getPool().query('DELETE FROM meta_pixels WHERE shop_id = ?', [shopId]); await this.getPool().query('DELETE FROM app_configurations WHERE shop_id = ? OR shopify_domain = ?', [shopId, shopId]); await this.getPool().query('DELETE FROM audit_logs WHERE shop_id = ?', [shopId]); await this.getPool().query('DELETE FROM shops WHERE id = ? OR shopify_domain = ?', [shopId, shopId]); } // --- Meta Connections --- async getMetaConnectionByShopId(shopId: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM meta_connections WHERE shop_id = ? ORDER BY updated_at DESC LIMIT 1', [shopId] ); if (rows.length === 0) return null; return rows[0] as MetaConnectionRecord; } async getMetaConnection(shopId: string): Promise { return this.getMetaConnectionByShopId(shopId); } async upsertMetaConnection(meta: MetaConnectionRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const updated = { ...meta, updated_at: now, created_at: meta.created_at || now }; const [existingRows]: [any[], any] = await this.getPool().query( 'SELECT id, created_at FROM meta_connections WHERE id = ? OR (shop_id = ? AND shop_id != "") LIMIT 1', [updated.id, updated.shop_id] ); if (existingRows.length > 0) { const targetId = existingRows[0].id; updated.id = targetId; updated.created_at = existingRows[0].created_at || updated.created_at; await this.getPool().query( `UPDATE meta_connections SET shop_id = ?, business_id = ?, business_name = ?, catalog_id = ?, catalog_name = ?, pixel_id = ?, pixel_name = ?, access_token_encrypted = COALESCE(?, access_token_encrypted), test_event_code = COALESCE(?, test_event_code), token_expires_at = COALESCE(?, token_expires_at), status = ?, updated_at = ? WHERE id = ?`, [ updated.shop_id, updated.business_id || null, updated.business_name || null, updated.catalog_id || null, updated.catalog_name || null, updated.pixel_id || null, updated.pixel_name || null, updated.access_token_encrypted, updated.test_event_code || null, updated.token_expires_at || null, updated.status, updated.updated_at, targetId, ] ); } else { await this.getPool().query( `INSERT INTO meta_connections ( id, shop_id, business_id, business_name, catalog_id, catalog_name, pixel_id, pixel_name, access_token_encrypted, test_event_code, token_expires_at, status, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ updated.id, updated.shop_id, updated.business_id || null, updated.business_name || null, updated.catalog_id || null, updated.catalog_name || null, updated.pixel_id || null, updated.pixel_name || null, updated.access_token_encrypted, updated.test_event_code || null, updated.token_expires_at || null, updated.status, updated.created_at, updated.updated_at, ] ); } try { if (updated.shop_id) { await this.getPool().query( 'DELETE FROM meta_connections WHERE shop_id = ? AND id != ?', [updated.shop_id, updated.id] ); } } catch {} return updated; } // --- App Configurations --- async getAppConfiguration(shopId: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM app_configurations WHERE shop_id = ? LIMIT 1', [shopId] ); if (rows.length === 0) return null; return this.mapAppConfigurationRow(rows[0]); } async getAppConfigurationByDomain(domain: string): Promise { await this.initialize(); const clean = domain.trim().toLowerCase().replace(/^https?:\/\//, '').replace(/\/.*$/, ''); const cleanWithoutSuffix = clean.replace('.myshopify.com', ''); const cleanWithSuffix = cleanWithoutSuffix + '.myshopify.com'; const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM app_configurations WHERE LOWER(shopify_domain) = LOWER(?) OR LOWER(shopify_domain) = LOWER(?) LIMIT 1', [cleanWithSuffix, cleanWithoutSuffix] ); if (rows.length === 0) return null; return this.mapAppConfigurationRow(rows[0]); } async upsertAppConfiguration(config: AppConfigurationRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const updated = { ...config, created_at: config.created_at || now, updated_at: now, }; await this.getPool().query( `INSERT INTO app_configurations ( id, shop_id, shopify_domain, shopify_access_token_encrypted, shopify_api_key, shopify_api_secret_encrypted, shopify_api_version, meta_business_id, meta_business_name, meta_catalog_id, meta_catalog_name, meta_pixel_id, meta_pixel_name, meta_access_token_encrypted, meta_test_event_code, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE shop_id = VALUES(shop_id), shopify_domain = VALUES(shopify_domain), shopify_access_token_encrypted = VALUES(shopify_access_token_encrypted), shopify_api_key = VALUES(shopify_api_key), shopify_api_secret_encrypted = VALUES(shopify_api_secret_encrypted), shopify_api_version = VALUES(shopify_api_version), meta_business_id = VALUES(meta_business_id), meta_business_name = VALUES(meta_business_name), meta_catalog_id = VALUES(meta_catalog_id), meta_catalog_name = VALUES(meta_catalog_name), meta_pixel_id = VALUES(meta_pixel_id), meta_pixel_name = VALUES(meta_pixel_name), meta_access_token_encrypted = VALUES(meta_access_token_encrypted), meta_test_event_code = VALUES(meta_test_event_code), updated_at = VALUES(updated_at)`, [ updated.id, updated.shop_id, updated.shopify_domain, updated.shopify_access_token_encrypted || null, updated.shopify_api_key || null, updated.shopify_api_secret_encrypted || null, updated.shopify_api_version || SHOPIFY_CONFIG.apiVersion, updated.meta_business_id || null, updated.meta_business_name || null, updated.meta_catalog_id || null, updated.meta_catalog_name || null, updated.meta_pixel_id || null, updated.meta_pixel_name || null, updated.meta_access_token_encrypted || null, updated.meta_test_event_code || null, updated.created_at, updated.updated_at, ] ); return updated; } private mapAppConfigurationRow(r: any): AppConfigurationRecord { return { id: r.id, shop_id: r.shop_id, shopify_domain: r.shopify_domain, shopify_access_token_encrypted: r.shopify_access_token_encrypted, shopify_api_key: r.shopify_api_key, shopify_api_secret_encrypted: r.shopify_api_secret_encrypted, shopify_api_version: r.shopify_api_version || SHOPIFY_CONFIG.apiVersion, meta_business_id: r.meta_business_id, meta_business_name: r.meta_business_name, meta_catalog_id: r.meta_catalog_id, meta_catalog_name: r.meta_catalog_name, meta_pixel_id: r.meta_pixel_id, meta_pixel_name: r.meta_pixel_name, meta_access_token_encrypted: r.meta_access_token_encrypted, meta_test_event_code: r.meta_test_event_code, created_at: r.created_at, updated_at: r.updated_at, }; } // --- Meta Pixels (Meta Commerce Multi-Pixel Support) --- async getPixelsByShopId(shopId: string, facebookAccountId?: string | null): Promise { await this.initialize(); let query = 'SELECT * FROM meta_pixels WHERE shop_id = ?'; const params: any[] = [shopId]; if (facebookAccountId) { query += " AND (facebook_account_id IS NULL OR facebook_account_id = '' OR facebook_account_id = ?)"; params.push(facebookAccountId); } query += ' ORDER BY created_at ASC'; const [rows]: [any[], any] = await this.getPool().query(query, params); if (rows.length > 0) { // Deduplicate by pixel_id keeping the most complete / active record const pixelMap = new Map(); for (const r of rows) { const key = String(r.pixel_id || '').trim(); const existing = pixelMap.get(key); if (!existing) { pixelMap.set(key, r); } else if (r.access_token_encrypted && !existing.access_token_encrypted) { pixelMap.set(key, r); } else if (r.status === 'active' && existing.status !== 'active') { pixelMap.set(key, r); } } // Proactively clean up duplicate rows in MySQL table so they never reappear if (rows.length > pixelMap.size) { const keepIds = new Set(Array.from(pixelMap.values()).map((r) => r.id)); for (const r of rows) { if (!keepIds.has(r.id)) { this.getPool().query('DELETE FROM meta_pixels WHERE id = ? AND shop_id = ?', [r.id, shopId]).catch(() => {}); } } } return Array.from(pixelMap.values()).map((r) => ({ ...r, source: (r.source || (r.facebook_account_id ? 'auto' : 'manual')) as 'manual' | 'auto', facebook_account_id: r.facebook_account_id || null, pages_config: r.pages_config || (r.target_collection === 'all' ? 'all' : (r.target_collection || 'all')), capi_status: (r.capi_status || (r.capi_enabled ? 'active' : 'not_configured')) as any, is_active: r.is_active !== undefined && r.is_active !== null ? Boolean(r.is_active) : (r.status === 'active'), verification_status: r.verification_status || 'unverified', capi_enabled: Boolean(r.capi_enabled), app_embed_active: Boolean(r.app_embed_active), emq_score: Number(r.emq_score || 8.6), events_count_24h: Number(r.events_count_24h || 0), })); } return []; } async getPixelById(id: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM meta_pixels WHERE id = ? OR pixel_id = ? LIMIT 1', [id, id] ); if (rows.length === 0) return null; const r = rows[0]; return { ...r, source: (r.source || (r.facebook_account_id ? 'auto' : 'manual')) as 'manual' | 'auto', facebook_account_id: r.facebook_account_id || null, pages_config: r.pages_config || (r.target_collection === 'all' ? 'all' : (r.target_collection || 'all')), capi_status: (r.capi_status || (r.capi_enabled ? 'active' : 'not_configured')) as any, is_active: r.is_active !== undefined && r.is_active !== null ? Boolean(r.is_active) : (r.status === 'active'), verification_status: r.verification_status || 'unverified', capi_enabled: Boolean(r.capi_enabled), app_embed_active: Boolean(r.app_embed_active), emq_score: Number(r.emq_score || 8.6), events_count_24h: Number(r.events_count_24h || 0), }; } async upsertPixel(pixel: MetaPixelRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const updated: MetaPixelRecord = { ...pixel, updated_at: now, created_at: pixel.created_at || now, }; // Find any existing row matching id OR (shop_id AND pixel_id) const [existingRows]: [any[], any] = await this.getPool().query( 'SELECT id, created_at, verification_status, source, facebook_account_id, pages_config, capi_status FROM meta_pixels WHERE id = ? OR (shop_id = ? AND pixel_id = ?) LIMIT 1', [updated.id, updated.shop_id, updated.pixel_id] ); const finalVerificationStatus = updated.verification_status || (existingRows.length > 0 ? existingRows[0].verification_status : 'unverified') || 'unverified'; const finalSource = updated.source || (existingRows.length > 0 ? existingRows[0].source : 'manual') || 'manual'; const finalFbAccountId = updated.facebook_account_id !== undefined ? updated.facebook_account_id : (existingRows.length > 0 ? existingRows[0].facebook_account_id : null); const finalPagesConfig = updated.pages_config || (existingRows.length > 0 ? existingRows[0].pages_config : (updated.target_collection || 'all')) || 'all'; const finalCapiStatus = updated.capi_status || (existingRows.length > 0 ? existingRows[0].capi_status : (updated.capi_enabled ? 'active' : 'not_configured')) || 'not_configured'; const finalIsActive = updated.is_active !== undefined ? (updated.is_active ? 1 : 0) : (updated.status === 'active' ? 1 : 0); if (existingRows.length > 0) { const targetId = existingRows[0].id; updated.id = targetId; updated.created_at = existingRows[0].created_at || updated.created_at; await this.getPool().query( `UPDATE meta_pixels SET shop_id = ?, pixel_name = ?, pixel_id = ?, pixel_type = ?, source = ?, facebook_account_id = ?, pages_config = ?, target_collection = ?, target_tag = ?, access_token_encrypted = COALESCE(?, access_token_encrypted), test_event_code = COALESCE(?, test_event_code), capi_enabled = ?, capi_status = ?, emq_score = ?, events_count_24h = ?, status = ?, verification_status = ?, is_active = ?, app_embed_active = ?, updated_at = ? WHERE id = ?`, [ updated.shop_id, updated.pixel_name, updated.pixel_id, updated.pixel_type || 'master', finalSource, finalFbAccountId, finalPagesConfig, updated.target_collection || 'all', updated.target_tag || null, updated.access_token_encrypted || null, updated.test_event_code || null, updated.capi_enabled ? 1 : 0, finalCapiStatus, updated.emq_score || 8.6, updated.events_count_24h || 0, updated.status || 'active', finalVerificationStatus, finalIsActive, updated.app_embed_active ? 1 : 0, updated.updated_at, targetId, ] ); } else { await this.getPool().query( `INSERT INTO meta_pixels ( id, shop_id, pixel_name, pixel_id, pixel_type, source, facebook_account_id, pages_config, target_collection, target_tag, access_token_encrypted, test_event_code, capi_enabled, capi_status, emq_score, events_count_24h, status, verification_status, is_active, app_embed_active, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE shop_id = VALUES(shop_id), pixel_name = VALUES(pixel_name), pixel_id = VALUES(pixel_id), pixel_type = VALUES(pixel_type), source = VALUES(source), facebook_account_id = COALESCE(VALUES(facebook_account_id), facebook_account_id), pages_config = VALUES(pages_config), target_collection = VALUES(target_collection), target_tag = VALUES(target_tag), access_token_encrypted = COALESCE(VALUES(access_token_encrypted), access_token_encrypted), test_event_code = COALESCE(VALUES(test_event_code), test_event_code), capi_enabled = VALUES(capi_enabled), capi_status = VALUES(capi_status), emq_score = VALUES(emq_score), events_count_24h = VALUES(events_count_24h), status = VALUES(status), verification_status = VALUES(verification_status), is_active = VALUES(is_active), app_embed_active = VALUES(app_embed_active), updated_at = VALUES(updated_at)`, [ updated.id, updated.shop_id, updated.pixel_name, updated.pixel_id, updated.pixel_type || 'master', finalSource, finalFbAccountId, finalPagesConfig, updated.target_collection || 'all', updated.target_tag || null, updated.access_token_encrypted || null, updated.test_event_code || null, updated.capi_enabled ? 1 : 0, finalCapiStatus, updated.emq_score || 8.6, updated.events_count_24h || 0, updated.status || 'active', finalVerificationStatus, finalIsActive, updated.app_embed_active ? 1 : 0, updated.created_at, updated.updated_at, ] ); } // Prune any duplicate rows for the same shop_id and pixel_id try { if (updated.shop_id && updated.pixel_id) { await this.getPool().query( 'DELETE FROM meta_pixels WHERE shop_id = ? AND pixel_id = ? AND id != ?', [updated.shop_id, updated.pixel_id, updated.id] ); } } catch {} return { ...updated, source: finalSource as any, facebook_account_id: finalFbAccountId, pages_config: finalPagesConfig, capi_status: finalCapiStatus as any, is_active: Boolean(finalIsActive), verification_status: finalVerificationStatus as any, }; } async deletePixel(id: string, shopId: string): Promise { await this.initialize(); const cleanId = id.replace(/^pix_/, ''); const [result]: [any, any] = await this.getPool().query( 'DELETE FROM meta_pixels WHERE (id = ? OR id = ? OR pixel_id = ? OR pixel_id = ?) AND shop_id = ?', [id, `pix_${cleanId}`, id, cleanId, shopId] ); try { await this.getPool().query( 'UPDATE meta_connections SET pixel_id = NULL, pixel_name = NULL WHERE shop_id = ? AND (pixel_id = ? OR pixel_id = ?)', [shopId, id, cleanId] ); await this.getPool().query( 'UPDATE app_configurations SET meta_pixel_id = NULL, meta_pixel_name = NULL WHERE shop_id = ? AND (meta_pixel_id = ? OR meta_pixel_id = ?)', [shopId, id, cleanId] ); } catch {} return result.affectedRows > 0; } async setAppEmbedActiveForShop(shopId: string, active = true): Promise { await this.initialize(); await this.getPool().query('UPDATE meta_pixels SET app_embed_active = ? WHERE shop_id = ?', [active ? 1 : 0, shopId]); } // --- Products --- async getProductsByShopId(shopId: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM catalog_products WHERE shop_id = ?', [shopId]); return rows.map(this.mapProductRow); } async getProducts(shopId: string, limit?: number): Promise { await this.initialize(); let query = 'SELECT * FROM catalog_products WHERE shop_id = ?'; const params: any[] = [shopId]; if (limit) { query += ' LIMIT ?'; params.push(limit); } const [rows]: [any[], any] = await this.getPool().query(query, params); return rows.map(this.mapProductRow); } async upsertProduct(prod: CatalogProductRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const updated = { ...prod, created_at: prod.created_at || now, updated_at: now, last_synced_at: prod.last_synced_at || now, }; const tagsJson = updated.tags ? (Array.isArray(updated.tags) ? JSON.stringify(updated.tags) : String(updated.tags)) : null; const collectionsJson = updated.collections ? (Array.isArray(updated.collections) ? JSON.stringify(updated.collections) : String(updated.collections)) : null; const collectionIdsJson = updated.collection_ids ? (Array.isArray(updated.collection_ids) ? JSON.stringify(updated.collection_ids) : String(updated.collection_ids)) : null; await this.getPool().query( `INSERT INTO catalog_products (id, shop_id, shopify_product_id, shopify_variant_id, meta_product_id, sku, barcode, title, variant_title, description, body_html, price, compare_at_price, currency, availability, inventory_quantity, image_url, additional_images, video_url, weight, weight_unit, product_url, google_product_category, fb_product_category, brand, \`condition\`, custom_label_0, custom_label_1, custom_label_2, custom_label_3, custom_label_4, tags, collections, collection_ids, status, sync_error, last_synced_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE shop_id = VALUES(shop_id), shopify_product_id = VALUES(shopify_product_id), shopify_variant_id = VALUES(shopify_variant_id), meta_product_id = VALUES(meta_product_id), sku = VALUES(sku), barcode = VALUES(barcode), title = VALUES(title), variant_title = VALUES(variant_title), description = VALUES(description), body_html = VALUES(body_html), price = VALUES(price), compare_at_price = VALUES(compare_at_price), currency = VALUES(currency), availability = VALUES(availability), inventory_quantity = VALUES(inventory_quantity), image_url = VALUES(image_url), additional_images = VALUES(additional_images), video_url = VALUES(video_url), weight = VALUES(weight), weight_unit = VALUES(weight_unit), product_url = VALUES(product_url), google_product_category = VALUES(google_product_category), fb_product_category = VALUES(fb_product_category), brand = VALUES(brand), \`condition\` = VALUES(\`condition\`), custom_label_0 = VALUES(custom_label_0), custom_label_1 = VALUES(custom_label_1), custom_label_2 = VALUES(custom_label_2), custom_label_3 = VALUES(custom_label_3), custom_label_4 = VALUES(custom_label_4), tags = VALUES(tags), collections = VALUES(collections), collection_ids = VALUES(collection_ids), status = VALUES(status), sync_error = VALUES(sync_error), last_synced_at = VALUES(last_synced_at), updated_at = VALUES(updated_at)`, [ updated.id, updated.shop_id, updated.shopify_product_id, updated.shopify_variant_id, updated.meta_product_id || null, updated.sku || null, updated.barcode || null, updated.title, updated.variant_title || null, updated.description, updated.body_html || null, updated.price, updated.compare_at_price ?? null, updated.currency, updated.availability, updated.inventory_quantity, updated.image_url, JSON.stringify(updated.additional_images || []), updated.video_url || null, updated.weight ?? null, updated.weight_unit || null, updated.product_url, updated.google_product_category || null, updated.fb_product_category || null, updated.brand, updated.condition, updated.custom_label_0 || null, updated.custom_label_1 || null, updated.custom_label_2 || null, updated.custom_label_3 || null, updated.custom_label_4 || null, tagsJson, collectionsJson, collectionIdsJson, updated.status, updated.sync_error || null, updated.last_synced_at, updated.created_at, updated.updated_at, ] ); return updated; } async deleteProduct(id: string): Promise { await this.initialize(); await this.getPool().query('DELETE FROM catalog_products WHERE id = ?', [id]); } async clearProductsByShopId(shopId: string): Promise { await this.initialize(); await this.getPool().query('DELETE FROM catalog_products WHERE shop_id = ?', [shopId]); } // --- Syncs --- async createSync(sync: CatalogSyncRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const record = { ...sync, created_at: sync.created_at || now, updated_at: now, }; await this.getPool().query( `INSERT INTO catalog_syncs (id, shop_id, sync_type, status, total_products, processed_products, failed_products, warning_count, started_at, completed_at, error_message, details, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ record.id, record.shop_id, record.sync_type, record.status, record.total_products, record.processed_products, record.failed_products, record.warning_count, record.started_at, record.completed_at || null, record.error_message || null, JSON.stringify(record.details || {}), record.created_at, record.updated_at, ] ); return record; } async getLatestSync(shopId: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM catalog_syncs WHERE shop_id = ? ORDER BY started_at DESC LIMIT 1', [shopId] ); if (rows.length === 0) return null; return this.mapSyncRow(rows[0]); } async updateSync(id: string, updates: Partial): Promise { await this.initialize(); const now = new Date().toISOString(); const setClauses: string[] = ['`updated_at` = ?']; const values: any[] = [now]; for (const [key, val] of Object.entries(updates)) { if (key === 'updated_at') continue; if (key === 'details') { setClauses.push('`details` = ?'); values.push(JSON.stringify(val)); } else { setClauses.push(`\`${key}\` = ?`); values.push(val); } } values.push(id); await this.getPool().query(`UPDATE catalog_syncs SET ${setClauses.join(', ')} WHERE id = ?`, values); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM catalog_syncs WHERE id = ?', [id]); if (rows.length === 0) return null; return this.mapSyncRow(rows[0]); } // --- CAPI Events --- async recordCapiEvent(event: CapiEventRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const record = { ...event, created_at: event.created_at || now, updated_at: now, }; await this.getPool().query( `INSERT INTO capi_events (id, shop_id, event_id, event_name, event_source_url, shopify_order_id, payload_hash, user_data_masked, custom_data, status, meta_event_id, response_code, response_body, error_message, duration_ms, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE shop_id = VALUES(shop_id), event_id = VALUES(event_id), event_name = VALUES(event_name), event_source_url = VALUES(event_source_url), shopify_order_id = VALUES(shopify_order_id), payload_hash = VALUES(payload_hash), user_data_masked = VALUES(user_data_masked), custom_data = VALUES(custom_data), status = VALUES(status), meta_event_id = VALUES(meta_event_id), response_code = VALUES(response_code), response_body = VALUES(response_body), error_message = VALUES(error_message), duration_ms = VALUES(duration_ms), updated_at = VALUES(updated_at)`, [ record.id, record.shop_id, record.event_id, record.event_name, record.event_source_url || null, record.shopify_order_id || null, record.payload_hash, JSON.stringify(record.user_data_masked || {}), JSON.stringify(record.custom_data || {}), record.status, record.meta_event_id || null, record.response_code, JSON.stringify(record.response_body || {}), record.error_message || null, record.duration_ms, record.created_at, record.updated_at, ] ); return record; } async getCapiEvents(shopId: string, limit = 50): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM capi_events WHERE shop_id = ? ORDER BY created_at DESC LIMIT ?', [shopId, limit] ); return rows.map(this.mapCapiRow); } // --- Webhook Events --- async recordWebhook(webhook: WebhookEventRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const record = { ...webhook, created_at: webhook.created_at || now, updated_at: now, }; await this.getPool().query( `INSERT INTO webhook_events (id, shop_id, topic, shopify_webhook_id, payload_json, status, attempts, error_message, created_at, processed_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ record.id, record.shop_id || null, record.topic, record.shopify_webhook_id, record.payload_json, record.status, record.attempts, record.error_message || null, record.created_at, record.processed_at || null, record.updated_at, ] ); return record; } // --- Audit Logs --- async recordAuditLog(log: AuditLogRecord): Promise { await this.initialize(); const now = new Date().toISOString(); const record = { ...log, created_at: log.created_at || now, updated_at: now, }; await this.getPool().query( `INSERT INTO audit_logs (id, shop_id, action, actor, metadata, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)`, [ record.id, record.shop_id, record.action, record.actor, JSON.stringify(record.metadata || {}), record.created_at, record.updated_at, ] ); return record; } async getAuditLogs(shopId: string, limit = 50): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM audit_logs WHERE shop_id = ? ORDER BY created_at DESC LIMIT ?', [shopId, limit] ); return rows.map((r: any) => ({ ...r, metadata: typeof r.metadata === 'string' ? JSON.parse(r.metadata) : r.metadata || {}, })); } // --- Pricing Plans --- async getPlans(): Promise { try { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM pricing_plans ORDER BY display_order ASC, price ASC'); if (rows && rows.length > 0) { return rows.map(this.mapPlanRow); } return Object.values(DEFAULT_PLANS); } catch (err) { console.warn('MySQL getPlans query note, falling back to DEFAULT_PLANS:', err); return Object.values(DEFAULT_PLANS); } } async getPlan(id: string): Promise { try { await this.initialize(); const cleanId = id.trim().toLowerCase(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM pricing_plans WHERE id = ?', [cleanId]); if (rows.length === 0) { // Check alias mapping if (cleanId === 'scale') { const [accRows]: [any[], any] = await this.getPool().query('SELECT * FROM pricing_plans WHERE id = "accelerate"'); if (accRows.length > 0) return { ...this.mapPlanRow(accRows[0]), id: 'scale', name: 'Scale Plan' }; } else if (cleanId === 'accelerate') { const [scaleRows]: [any[], any] = await this.getPool().query('SELECT * FROM pricing_plans WHERE id = "scale"'); if (scaleRows.length > 0) return { ...this.mapPlanRow(scaleRows[0]), id: 'accelerate' }; } return DEFAULT_PLANS[cleanId] || null; } return this.mapPlanRow(rows[0]); } catch (err) { console.warn('MySQL getPlan query note, falling back to DEFAULT_PLANS:', err); return DEFAULT_PLANS[id] || null; } } async updatePlan(id: string, updates: Partial): Promise { await this.initialize(); const now = new Date().toISOString(); const existing = await this.getPlan(id); const updatedPlan: PricingPlan = { ...(existing || DEFAULT_PLANS[id] || { id, name: id, description: null, price: 0, yearly_price: 0, currency: 'USD', interval: 'month', yearly_discount_label: 'Save 20%', display_order: 0, is_visible: true, sku_limit: null, campaigns_limit: null, capi_enabled: false, supplementary_feeds: false, custom_titles: false, website_audit: false, dedicated_manager: false, support: 'Email Support', features: [], badge: null, is_active: true, }), ...updates, created_at: existing?.created_at || now, updated_at: now, }; await this.getPool().query( `INSERT INTO pricing_plans (id, name, description, price, yearly_price, currency, \`interval\`, yearly_discount_label, display_order, is_visible, sku_limit, campaigns_limit, trial_days, capi_enabled, supplementary_feeds, custom_titles, website_audit, dedicated_manager, support, features, badge, is_active, feature_flags, feature_limits, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), price = VALUES(price), yearly_price = VALUES(yearly_price), currency = VALUES(currency), \`interval\` = VALUES(\`interval\`), yearly_discount_label = VALUES(yearly_discount_label), display_order = VALUES(display_order), is_visible = VALUES(is_visible), sku_limit = VALUES(sku_limit), campaigns_limit = VALUES(campaigns_limit), trial_days = VALUES(trial_days), capi_enabled = VALUES(capi_enabled), supplementary_feeds = VALUES(supplementary_feeds), custom_titles = VALUES(custom_titles), website_audit = VALUES(website_audit), dedicated_manager = VALUES(dedicated_manager), support = VALUES(support), features = VALUES(features), badge = VALUES(badge), is_active = VALUES(is_active), feature_flags = VALUES(feature_flags), feature_limits = VALUES(feature_limits), updated_at = VALUES(updated_at)`, [ updatedPlan.id, updatedPlan.name, updatedPlan.description || null, updatedPlan.price, updatedPlan.yearly_price ?? (updatedPlan.price * 12 * 0.8), updatedPlan.currency || 'USD', updatedPlan.interval || 'month', updatedPlan.yearly_discount_label || 'Save 20%', updatedPlan.display_order ?? 0, updatedPlan.is_visible !== false, updatedPlan.sku_limit, updatedPlan.campaigns_limit, updatedPlan.trial_days || 0, updatedPlan.capi_enabled, updatedPlan.supplementary_feeds, updatedPlan.custom_titles, updatedPlan.website_audit, updatedPlan.dedicated_manager, updatedPlan.support, JSON.stringify(updatedPlan.features || []), updatedPlan.badge || null, updatedPlan.is_active !== false, updatedPlan.feature_flags ? JSON.stringify(updatedPlan.feature_flags) : null, updatedPlan.feature_limits ? JSON.stringify(updatedPlan.feature_limits) : null, updatedPlan.created_at, updatedPlan.updated_at, ] ); return updatedPlan; } async resetDefaultPlans(): Promise { await this.initialize(); for (const plan of Object.values(DEFAULT_PLANS)) { await this.updatePlan(plan.id, plan); } return this.getPlans(); } async setShopPlan( shopId: string, planId: string, details?: { subscription_id?: string; subscription_status?: 'active' | 'pending' | 'cancelled' | 'trial' | 'free'; plan_custom_sku_limit?: number | null; trial_days?: number; trial_starts_at?: string | null; trial_ends_at?: string | null; } ): Promise { await this.initialize(); const shop = await this.getShopById(shopId); if (!shop) return null; shop.plan = planId; if (details?.subscription_id !== undefined) shop.subscription_id = details.subscription_id; if (details?.subscription_status !== undefined) shop.subscription_status = details.subscription_status; if (details?.plan_custom_sku_limit !== undefined) shop.plan_custom_sku_limit = details.plan_custom_sku_limit; if (details?.trial_days !== undefined) shop.trial_days = details.trial_days; if (details?.trial_starts_at !== undefined) shop.trial_starts_at = details.trial_starts_at; if (details?.trial_ends_at !== undefined) shop.trial_ends_at = details.trial_ends_at; shop.updated_at = new Date().toISOString(); return this.upsertShop(shop); } // --- Strict Store-Level Dashboard Metrics --- async getStoreDashboardMetrics(shopId: string) { await this.initialize(); const pool = this.getPool(); // 1. Store products stats (strictly WHERE shop_id = ?) const [prodStats]: [any[], any] = await pool.query( `SELECT COUNT(*) as totalProducts, COALESCE(SUM(CASE WHEN status = 'synced' THEN 1 ELSE 0 END), 0) as syncedProducts, COALESCE(SUM(CASE WHEN status = 'warning' THEN 1 ELSE 0 END), 0) as warningProducts, COALESCE(SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END), 0) as errorProducts, COALESCE(SUM(inventory_quantity), 0) as totalInventory, COALESCE(SUM(price * inventory_quantity), 0) as totalCatalogValue FROM catalog_products WHERE shop_id = ?`, [shopId] ); // 2. Store CAPI stats (strictly WHERE shop_id = ?) const [capiStats]: [any[], any] = await pool.query( `SELECT COUNT(*) as totalEvents, COALESCE(SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END), 0) as sentEvents, COALESCE(SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END), 0) as failedEvents, COALESCE(SUM(CASE WHEN status = 'deduplicated' THEN 1 ELSE 0 END), 0) as deduplicatedEvents, COALESCE(AVG(duration_ms), 0) as avgLatencyMs FROM capi_events WHERE shop_id = ?`, [shopId] ); // 3. Store meta connection (strictly WHERE shop_id = ?) const meta = await this.getMetaConnectionByShopId(shopId); // 4. Store latest sync (strictly WHERE shop_id = ?) const latestSync = await this.getLatestSync(shopId); // 5. Store recent events (strictly WHERE shop_id = ?) const recentEvents = await this.getCapiEvents(shopId, 10); // 6. Store recent products (strictly WHERE shop_id = ?) const recentProducts = await this.getProducts(shopId, 6); // 7. Store recent audit activity (strictly WHERE shop_id = ?) const recentLogs = await this.getAuditLogs(shopId, 5); return { products: { total: Number(prodStats[0]?.totalProducts || 0), synced: Number(prodStats[0]?.syncedProducts || 0), warning: Number(prodStats[0]?.warningProducts || 0), error: Number(prodStats[0]?.errorProducts || 0), inventory: Number(prodStats[0]?.totalInventory || 0), catalogValue: Number(prodStats[0]?.totalCatalogValue || 0), }, capi: { total: Number(capiStats[0]?.totalEvents || 0), sent: Number(capiStats[0]?.sentEvents || 0), failed: Number(capiStats[0]?.failedEvents || 0), deduplicated: Number(capiStats[0]?.deduplicatedEvents || 0), avgLatencyMs: Math.round(Number(capiStats[0]?.avgLatencyMs || 0)), }, meta: { connected: meta?.status === 'connected', businessName: meta?.business_name || null, catalogName: meta?.catalog_name || null, catalogId: meta?.catalog_id || null, pixelName: meta?.pixel_name || null, pixelId: meta?.pixel_id || null, status: meta?.status || 'disconnected', hasToken: !!meta?.access_token_encrypted, }, latestSync: latestSync ? { id: latestSync.id, status: latestSync.status, totalProducts: latestSync.total_products, processedProducts: latestSync.processed_products, failedProducts: latestSync.failed_products, startedAt: latestSync.started_at, completedAt: latestSync.completed_at, } : null, recentEvents, recentProducts, recentLogs, }; } // --- Row Mappers --- private mapShopRow(r: any): ShopRecord { let parsedScopes: string[] = []; if (Array.isArray(r.scopes)) { parsedScopes = r.scopes; } else if (typeof r.scopes === 'string' && r.scopes.trim().length > 0) { try { const parsed = JSON.parse(r.scopes); parsedScopes = Array.isArray(parsed) ? parsed : [parsed]; } catch { parsedScopes = r.scopes.split(',').map((s: string) => s.trim()).filter(Boolean); } } return { ...r, monthly_revenue: Number(r.monthly_revenue || 0), plan_custom_sku_limit: r.plan_custom_sku_limit != null ? Number(r.plan_custom_sku_limit) : null, trial_days: Number(r.trial_days || 0), trial_starts_at: r.trial_starts_at || null, trial_ends_at: r.trial_ends_at || null, scopes: parsedScopes, refresh_token_encrypted: r.refresh_token_encrypted || null, token_expires_at: r.token_expires_at || null, refresh_token_expires_at: r.refresh_token_expires_at || null, token_updated_at: r.token_updated_at || r.updated_at || null, }; } private mapProductRow(r: any): CatalogProductRecord { const parseJsonOrArray = (val: any): string[] => { if (!val) return []; if (Array.isArray(val)) return val; if (typeof val === 'string') { try { const parsed = JSON.parse(val); if (Array.isArray(parsed)) return parsed; } catch {} return val.split(',').map((s: string) => s.trim()).filter(Boolean); } return []; }; return { ...r, price: Number(r.price), compare_at_price: r.compare_at_price != null ? Number(r.compare_at_price) : null, additional_images: typeof r.additional_images === 'string' ? JSON.parse(r.additional_images) : r.additional_images || [], video_url: r.video_url || null, weight: r.weight != null ? Number(r.weight) : null, weight_unit: r.weight_unit || null, body_html: r.body_html || null, tags: r.tags ? parseJsonOrArray(r.tags) : (r.custom_label_1 ? r.custom_label_1.split(',').map((s: string) => s.trim()).filter(Boolean) : []), collections: parseJsonOrArray(r.collections), collection_ids: parseJsonOrArray(r.collection_ids), }; } private mapSyncRow(r: any): CatalogSyncRecord { return { ...r, details: typeof r.details === 'string' ? JSON.parse(r.details) : r.details || {}, }; } private mapCapiRow(r: any): CapiEventRecord { return { ...r, user_data_masked: typeof r.user_data_masked === 'string' ? JSON.parse(r.user_data_masked) : r.user_data_masked || {}, custom_data: typeof r.custom_data === 'string' ? JSON.parse(r.custom_data) : r.custom_data || {}, response_body: typeof r.response_body === 'string' ? JSON.parse(r.response_body) : r.response_body || {}, }; } private mapPlanRow(r: any): PricingPlan { const rawPrice = Number(r.price) || 0; return { ...r, description: r.description || null, price: rawPrice, yearly_price: r.yearly_price != null ? Number(r.yearly_price) : rawPrice * 12 * 0.8, currency: r.currency || 'USD', interval: r.interval || 'month', yearly_discount_label: r.yearly_discount_label || 'Save 20%', display_order: r.display_order != null ? Number(r.display_order) : 0, is_visible: r.is_visible !== undefined ? Boolean(r.is_visible) : true, sku_limit: r.sku_limit != null ? Number(r.sku_limit) : null, campaigns_limit: r.campaigns_limit != null ? Number(r.campaigns_limit) : null, trial_days: Number(r.trial_days || 0), capi_enabled: Boolean(r.capi_enabled), supplementary_feeds: Boolean(r.supplementary_feeds), custom_titles: Boolean(r.custom_titles), website_audit: Boolean(r.website_audit), dedicated_manager: Boolean(r.dedicated_manager), badge: r.badge || null, is_active: r.is_active !== undefined ? Boolean(r.is_active) : true, features: typeof r.features === 'string' ? JSON.parse(r.features) : r.features || [], feature_flags: typeof r.feature_flags === 'string' ? JSON.parse(r.feature_flags) : r.feature_flags || undefined, feature_limits: typeof r.feature_limits === 'string' ? JSON.parse(r.feature_limits) : r.feature_limits || undefined, }; } // --- Feature Definitions Methods --- async getFeatureDefinitions(): Promise { try { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM feature_definitions ORDER BY display_order ASC'); if (rows && rows.length > 0) { return rows.map(this.mapFeatureRow); } return DEFAULT_FEATURE_DEFINITIONS; } catch (err) { console.warn('MySQL getFeatureDefinitions warning, fallback to defaults:', err); return DEFAULT_FEATURE_DEFINITIONS; } } async getFeatureDefinition(id: string): Promise { try { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM feature_definitions WHERE id = ?', [id]); if (rows.length === 0) { return DEFAULT_FEATURE_DEFINITIONS.find((f) => f.id === id) || null; } return this.mapFeatureRow(rows[0]); } catch (err) { console.warn('MySQL getFeatureDefinition warning, fallback to defaults:', err); return DEFAULT_FEATURE_DEFINITIONS.find((f) => f.id === id) || null; } } async updateFeatureDefinition(id: string, updates: Partial): Promise { await this.initialize(); const now = new Date().toISOString(); const existing = await this.getFeatureDefinition(id); const updated: FeatureDefinition = { ...(existing || { id, name: updates.name || id, description: null, display_order: 99, is_visible: true, category: 'General', basic_value: '', growth_value: '', scale_value: '', basic_enabled: false, growth_enabled: true, scale_enabled: true, basic_limit: null, growth_limit: null, scale_limit: null, }), ...updates, updated_at: now, }; await this.getPool().query( `INSERT INTO feature_definitions (id, name, description, display_order, is_visible, category, basic_value, growth_value, scale_value, basic_enabled, growth_enabled, scale_enabled, basic_limit, growth_limit, scale_limit, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), display_order = VALUES(display_order), is_visible = VALUES(is_visible), category = VALUES(category), basic_value = VALUES(basic_value), growth_value = VALUES(growth_value), scale_value = VALUES(scale_value), basic_enabled = VALUES(basic_enabled), growth_enabled = VALUES(growth_enabled), scale_enabled = VALUES(scale_enabled), basic_limit = VALUES(basic_limit), growth_limit = VALUES(growth_limit), scale_limit = VALUES(scale_limit), updated_at = VALUES(updated_at)`, [ updated.id, updated.name, updated.description || null, updated.display_order ?? 0, updated.is_visible !== false, updated.category || 'General', updated.basic_value || '', updated.growth_value || '', updated.scale_value || '', Boolean(updated.basic_enabled), Boolean(updated.growth_enabled), Boolean(updated.scale_enabled), updated.basic_limit ?? null, updated.growth_limit ?? null, updated.scale_limit ?? null, updated.created_at || now, now, ] ); return updated; } async deleteFeatureDefinition(id: string): Promise { try { await this.initialize(); await this.getPool().query('DELETE FROM feature_definitions WHERE id = ?', [id]); return true; } catch { return false; } } private mapFeatureRow(r: any): FeatureDefinition { return { ...r, description: r.description || null, display_order: Number(r.display_order || 0), is_visible: Boolean(r.is_visible), basic_enabled: Boolean(r.basic_enabled), growth_enabled: Boolean(r.growth_enabled), scale_enabled: Boolean(r.scale_enabled), basic_limit: r.basic_limit != null ? Number(r.basic_limit) : null, growth_limit: r.growth_limit != null ? Number(r.growth_limit) : null, scale_limit: r.scale_limit != null ? Number(r.scale_limit) : null, }; } // --- Catalog & Feed Methods --- async getCatalogs(shopId: string, facebookAccountId?: string | null): Promise { const pool = await this.getInitializedPool(); let query = 'SELECT * FROM catalogs WHERE shop_id = ?'; const params: any[] = [shopId]; if (facebookAccountId !== undefined) { if (facebookAccountId) { query += ' AND facebook_account_id = ?'; params.push(facebookAccountId); } else { return []; } } query += ' ORDER BY created_at DESC'; const [rows]: any = await pool.query(query, params); return rows.map((r: any) => this.mapCatalogRow(r)); } async getCatalogById(id: string): Promise { const pool = await this.getInitializedPool(); const [rows]: any = await pool.query( `SELECT * FROM catalogs WHERE id = ? LIMIT 1`, [id] ); if (!rows || rows.length === 0) return null; return this.mapCatalogRow(rows[0]); } async getCatalogByFbId(fbCatalogId: string): Promise { const pool = await this.getInitializedPool(); const [rows]: any = await pool.query( `SELECT * FROM catalogs WHERE fb_catalog_id = ? LIMIT 1`, [fbCatalogId] ); if (!rows || rows.length === 0) return null; return this.mapCatalogRow(rows[0]); } async createCatalog(cat: CatalogRecord): Promise { const pool = await this.getInitializedPool(); await pool.query( `INSERT INTO catalogs ( id, shop_id, facebook_account_id, sync_rules, fb_catalog_id, fb_business_id, fb_pixel_id, name, sync_scope, variant_mode, google_category_id, google_category_name, additional_images, link_domain, custom_domain, exclude_out_of_stock, price_mode, schedule_type, schedule_day, schedule_hour, schedule_period, schedule_tz, auto_sync, pixel_tracking, status, last_synced_at, next_sync_at, last_error, fb_catalog_url, product_count, variant_count, created_at, updated_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ cat.id, cat.shop_id, cat.facebook_account_id || null, cat.sync_rules ? JSON.stringify(cat.sync_rules) : null, cat.fb_catalog_id, cat.fb_business_id, cat.fb_pixel_id || null, cat.name, cat.sync_scope || 'all', cat.variant_mode || 'all_separate', cat.google_category_id, cat.google_category_name, cat.additional_images ? 1 : 0, cat.link_domain || 'myshopify', cat.custom_domain || null, cat.exclude_out_of_stock ? 1 : 0, cat.price_mode || 'compare_at_and_price', cat.schedule_type || 'weekly', cat.schedule_day || 'Every Monday', cat.schedule_hour || 2, cat.schedule_period || 'AM', cat.schedule_tz || 'UTC', cat.auto_sync !== false ? 1 : 0, cat.pixel_tracking !== false ? 1 : 0, cat.status || 'synced', cat.last_synced_at || null, cat.next_sync_at || null, cat.last_error || null, cat.fb_catalog_url || null, cat.product_count || 0, cat.variant_count || 0, cat.created_at || new Date().toISOString(), cat.updated_at || new Date().toISOString(), ] ); } async updateCatalog(id: string, updateData: Partial): Promise { const pool = await this.getInitializedPool(); const fields: string[] = []; const values: any[] = []; const allowedFields = [ 'facebook_account_id', 'sync_rules', 'fb_catalog_id', 'fb_business_id', 'fb_pixel_id', 'name', 'sync_scope', 'variant_mode', 'google_category_id', 'google_category_name', 'additional_images', 'link_domain', 'custom_domain', 'exclude_out_of_stock', 'price_mode', 'schedule_type', 'schedule_day', 'schedule_hour', 'schedule_period', 'schedule_tz', 'auto_sync', 'pixel_tracking', 'status', 'last_synced_at', 'next_sync_at', 'last_error', 'fb_catalog_url', 'product_count', 'variant_count', 'updated_at', ]; for (const key of allowedFields) { if (key in updateData) { fields.push(`\`${key}\` = ?`); let val = (updateData as any)[key]; if (key === 'sync_rules' && typeof val === 'object' && val !== null) { val = JSON.stringify(val); } else if (typeof val === 'boolean') { val = val ? 1 : 0; } values.push(val); } } if (fields.length === 0) return; fields.push('`updated_at` = ?'); values.push(new Date().toISOString()); values.push(id); await pool.query(`UPDATE catalogs SET ${fields.join(', ')} WHERE id = ?`, values); } async deleteCatalog(id: string): Promise { const pool = await this.getInitializedPool(); await pool.query(`DELETE FROM catalog_selections WHERE catalog_id = ?`, [id]); await pool.query(`DELETE FROM catalog_items WHERE catalog_id = ?`, [id]); await pool.query(`DELETE FROM catalog_sync_logs WHERE catalog_id = ?`, [id]); await pool.query(`DELETE FROM catalogs WHERE id = ?`, [id]); } async toggleCatalogAutoSync(id: string, autoSync: boolean): Promise { const pool = await this.getInitializedPool(); await pool.query( `UPDATE catalogs SET auto_sync = ?, updated_at = ? WHERE id = ?`, [autoSync ? 1 : 0, new Date().toISOString(), id] ); } async toggleCatalogPixelTracking(id: string, pixelTracking: boolean): Promise { const pool = await this.getInitializedPool(); await pool.query( `UPDATE catalogs SET pixel_tracking = ?, updated_at = ? WHERE id = ?`, [pixelTracking ? 1 : 0, new Date().toISOString(), id] ); } async setCatalogSelections( catalogId: string, selections: Array> ): Promise { const pool = await this.getInitializedPool(); await pool.query(`DELETE FROM catalog_selections WHERE catalog_id = ?`, [catalogId]); if (selections.length === 0) return; const values = selections.map((s, idx) => [ `sel_${catalogId}_${idx}_${Date.now()}`, catalogId, s.type, s.shopify_gid, s.value, s.is_exclude ? 1 : 0, ]); await pool.query( `INSERT INTO catalog_selections (id, catalog_id, type, shopify_gid, value, is_exclude) VALUES ?`, [values] ); } async getCatalogSelections(catalogId: string): Promise { const pool = await this.getInitializedPool(); const [rows]: any = await pool.query( `SELECT * FROM catalog_selections WHERE catalog_id = ?`, [catalogId] ); return rows.map((r: any) => ({ ...r, is_exclude: Boolean(r.is_exclude), })); } async getCatalogItems(catalogId: string): Promise { const pool = await this.getInitializedPool(); const [rows]: any = await pool.query( `SELECT * FROM catalog_items WHERE catalog_id = ?`, [catalogId] ); return rows; } async upsertCatalogItems(catalogId: string, items: CatalogItemRecord[]): Promise { const pool = await this.getInitializedPool(); if (items.length === 0) return; for (const item of items) { await pool.query( `INSERT INTO catalog_items (id, catalog_id, shopify_product_id, shopify_variant_id, retailer_id, payload_hash, fb_status, last_error, synced_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE payload_hash = VALUES(payload_hash), fb_status = VALUES(fb_status), last_error = VALUES(last_error), synced_at = VALUES(synced_at)`, [ item.id, catalogId, item.shopify_product_id, item.shopify_variant_id, item.retailer_id, item.payload_hash, item.fb_status || 'synced', item.last_error || null, item.synced_at || new Date().toISOString(), ] ); } } async deleteCatalogItems(catalogId: string, retailerIds: string[]): Promise { const pool = await this.getInitializedPool(); if (retailerIds.length === 0) return; await pool.query( `DELETE FROM catalog_items WHERE catalog_id = ? AND retailer_id IN (?)`, [catalogId, retailerIds] ); } async createSyncLog(log: CatalogSyncLogRecord): Promise { const pool = await this.getInitializedPool(); await pool.query( `INSERT INTO catalog_sync_logs ( id, catalog_id, \`trigger\`, status, created_count, updated_count, deleted_count, failed_count, batch_handles, error_details, started_at, finished_at ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ log.id, log.catalog_id, log.trigger || 'manual', log.status || 'running', log.created_count || 0, log.updated_count || 0, log.deleted_count || 0, log.failed_count || 0, log.batch_handles ? JSON.stringify(log.batch_handles) : null, log.error_details || null, log.started_at, log.finished_at || null, ] ); } async updateSyncLog(id: string, updateData: Partial): Promise { const pool = await this.getInitializedPool(); const fields: string[] = []; const values: any[] = []; if ('status' in updateData) { fields.push('`status` = ?'); values.push(updateData.status); } if ('created_count' in updateData) { fields.push('`created_count` = ?'); values.push(updateData.created_count); } if ('updated_count' in updateData) { fields.push('`updated_count` = ?'); values.push(updateData.updated_count); } if ('deleted_count' in updateData) { fields.push('`deleted_count` = ?'); values.push(updateData.deleted_count); } if ('failed_count' in updateData) { fields.push('`failed_count` = ?'); values.push(updateData.failed_count); } if ('batch_handles' in updateData) { fields.push('`batch_handles` = ?'); values.push(updateData.batch_handles ? JSON.stringify(updateData.batch_handles) : null); } if ('error_details' in updateData) { fields.push('`error_details` = ?'); values.push(updateData.error_details); } if ('finished_at' in updateData) { fields.push('`finished_at` = ?'); values.push(updateData.finished_at); } if (fields.length === 0) return; values.push(id); await pool.query(`UPDATE catalog_sync_logs SET ${fields.join(', ')} WHERE id = ?`, values); } async getDueScheduledCatalogs(): Promise { const pool = await this.getInitializedPool(); const nowIso = new Date().toISOString(); const [rows]: any = await pool.query( `SELECT * FROM catalogs WHERE auto_sync = 1 AND next_sync_at IS NOT NULL AND next_sync_at <= ? AND status != 'syncing'`, [nowIso] ); return rows.map((r: any) => this.mapCatalogRow(r)); } async disconnectMetaConnection(shopId: string): Promise { const pool = await this.getInitializedPool(); const now = new Date().toISOString(); await pool.query( `UPDATE meta_connections SET status = 'disconnected', access_token_encrypted = NULL, updated_at = ? WHERE shop_id = ?`, [now, shopId] ); } private mapCatalogRow(r: any): CatalogRecord { return { ...r, facebook_account_id: r.facebook_account_id || null, sync_rules: typeof r.sync_rules === 'string' ? JSON.parse(r.sync_rules) : (r.sync_rules || null), exclude_out_of_stock: Boolean(r.exclude_out_of_stock), additional_images: Boolean(r.additional_images), auto_sync: Boolean(r.auto_sync), pixel_tracking: Boolean(r.pixel_tracking), schedule_hour: Number(r.schedule_hour || 2), product_count: Number(r.product_count || 0), variant_count: Number(r.variant_count || 0), }; } // ============================================================================== // SAAS ADMIN: ADMIN USERS // ============================================================================== async getAdminUserByEmail(email: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM admin_users WHERE LOWER(email) = LOWER(?)', [email.trim()]); if (rows.length === 0) return null; return this.mapAdminUserRow(rows[0]); } async getAdminUserById(id: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM admin_users WHERE id = ?', [id]); if (rows.length === 0) return null; return this.mapAdminUserRow(rows[0]); } async createAdminUser(user: AdminUserRecord): Promise { await this.initialize(); await this.getPool().query( `INSERT INTO admin_users (id, email, password_hash, name, role, avatar_url, is_active, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE name = VALUES(name), role = VALUES(role), avatar_url = VALUES(avatar_url), is_active = VALUES(is_active), updated_at = VALUES(updated_at)`, [user.id, user.email, user.password_hash, user.name, user.role, user.avatar_url || null, user.is_active ? 1 : 0, user.created_at, user.updated_at] ); } async getAllAdminUsers(): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query('SELECT * FROM admin_users ORDER BY created_at ASC'); return rows.map((r: any) => this.mapAdminUserRow(r)); } async updateAdminUserLastLogin(id: string): Promise { await this.initialize(); const now = new Date().toISOString(); await this.getPool().query('UPDATE admin_users SET last_login_at = ?, updated_at = ? WHERE id = ?', [now, now, id]); } // ============================================================================== // SAAS ADMIN: APP INSTALLATIONS TRACKING // ============================================================================== async recordAppInstallation(record: AppInstallationRecord): Promise { await this.initialize(); await this.getPool().query( `INSERT INTO app_installations (id, shop_id, shopify_domain, event_type, plan_at_time, metadata, occurred_at, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, [ record.id, record.shop_id, record.shopify_domain, record.event_type, record.plan_at_time || 'growth', record.metadata ? JSON.stringify(record.metadata) : null, record.occurred_at, record.created_at || new Date().toISOString(), ] ); } async getAppInstallationsByShopId(shopId: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM app_installations WHERE shop_id = ? ORDER BY occurred_at DESC', [shopId] ); return rows.map((r: any) => this.mapAppInstallationRow(r)); } async getRecentAppInstallations(limit = 100): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM app_installations ORDER BY occurred_at DESC LIMIT ?', [Number(limit)] ); return rows.map((r: any) => this.mapAppInstallationRow(r)); } // ============================================================================== // SAAS ADMIN: SUBSCRIPTIONS MANAGEMENT // ============================================================================== async upsertSubscription(sub: SubscriptionRecord): Promise { await this.initialize(); const now = new Date().toISOString(); await this.getPool().query( `INSERT INTO subscriptions (id, shop_id, shopify_domain, shopify_subscription_id, plan_id, plan_name, status, billing_cycle, price, currency, trial_days, trial_starts_at, trial_ends_at, current_period_start, current_period_end, cancelled_at, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE shopify_subscription_id = VALUES(shopify_subscription_id), plan_id = VALUES(plan_id), plan_name = VALUES(plan_name), status = VALUES(status), billing_cycle = VALUES(billing_cycle), price = VALUES(price), currency = VALUES(currency), trial_days = VALUES(trial_days), trial_starts_at = VALUES(trial_starts_at), trial_ends_at = VALUES(trial_ends_at), current_period_start = VALUES(current_period_start), current_period_end = VALUES(current_period_end), cancelled_at = VALUES(cancelled_at), updated_at = VALUES(updated_at)`, [ sub.id, sub.shop_id, sub.shopify_domain, sub.shopify_subscription_id || null, sub.plan_id, sub.plan_name, sub.status, sub.billing_cycle || 'monthly', sub.price || 0, sub.currency || 'USD', sub.trial_days || 0, sub.trial_starts_at || null, sub.trial_ends_at || null, sub.current_period_start || null, sub.current_period_end || null, sub.cancelled_at || null, sub.created_at || now, sub.updated_at || now, ] ); } async getSubscriptions(filter?: { status?: string; plan_id?: string }): Promise { await this.initialize(); let query = 'SELECT * FROM subscriptions'; const params: any[] = []; const conditions: string[] = []; if (filter?.status && filter.status !== 'ALL') { conditions.push('status = ?'); params.push(filter.status); } if (filter?.plan_id && filter.plan_id !== 'ALL') { conditions.push('plan_id = ?'); params.push(filter.plan_id); } if (conditions.length > 0) { query += ' WHERE ' + conditions.join(' AND '); } query += ' ORDER BY created_at DESC'; const [rows]: [any[], any] = await this.getPool().query(query, params); return rows.map((r: any) => this.mapSubscriptionRow(r)); } async getSubscriptionByShopId(shopId: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM subscriptions WHERE shop_id = ? ORDER BY created_at DESC LIMIT 1', [shopId] ); if (rows.length === 0) return null; return this.mapSubscriptionRow(rows[0]); } async getSubscriptionById(id: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM subscriptions WHERE id = ?', [id] ); if (rows.length === 0) return null; return this.mapSubscriptionRow(rows[0]); } async cancelSubscription(id: string, cancelledAt?: string): Promise { await this.initialize(); const now = cancelledAt || new Date().toISOString(); await this.getPool().query( "UPDATE subscriptions SET status = 'cancelled', cancelled_at = ?, updated_at = ? WHERE id = ?", [now, now, id] ); } // ============================================================================== // SAAS ADMIN: BILLING EVENTS // ============================================================================== async recordBillingEvent(evt: BillingEventRecord): Promise { await this.initialize(); await this.getPool().query( `INSERT INTO billing_events (id, shop_id, shopify_domain, subscription_id, event_type, amount, currency, status, shopify_charge_id, details, billing_date, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ evt.id, evt.shop_id, evt.shopify_domain, evt.subscription_id || null, evt.event_type, evt.amount || 0, evt.currency || 'USD', evt.status || 'verified', evt.shopify_charge_id || null, evt.details ? JSON.stringify(evt.details) : null, evt.billing_date, evt.created_at || new Date().toISOString(), ] ); } async getBillingEventsByShopId(shopId: string): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM billing_events WHERE shop_id = ? ORDER BY billing_date DESC', [shopId] ); return rows.map((r: any) => this.mapBillingEventRow(r)); } async getRecentBillingEvents(limit = 100): Promise { await this.initialize(); const [rows]: [any[], any] = await this.getPool().query( 'SELECT * FROM billing_events ORDER BY billing_date DESC LIMIT ?', [Number(limit)] ); return rows.map((r: any) => this.mapBillingEventRow(r)); } // ============================================================================== // SAAS ADMIN: STORE ACTIVITY LOGS // ============================================================================== async recordStoreActivity(log: StoreActivityLogRecord): Promise { await this.initialize(); await this.getPool().query( `INSERT INTO store_activity_logs (id, shop_id, shopify_domain, action, actor, description, metadata, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)`, [ log.id, log.shop_id, log.shopify_domain, log.action, log.actor || 'system', log.description, log.metadata ? JSON.stringify(log.metadata) : null, log.created_at || new Date().toISOString(), ] ); } async createStoreActivityLog(log: Omit & { id?: string; created_at?: string }): Promise { await this.recordStoreActivity({ id: log.id || `act_${Date.now()}_${Math.random().toString(36).substring(2, 7)}`, shop_id: log.shop_id, shopify_domain: log.shopify_domain, action: log.action, actor: log.actor || 'system', description: log.description, metadata: log.metadata || null, created_at: log.created_at || new Date().toISOString(), }); } async getStoreActivityLogs(shopId?: string, limit = 100): Promise { await this.initialize(); let query = 'SELECT * FROM store_activity_logs'; const params: any[] = []; if (shopId) { query += ' WHERE shop_id = ?'; params.push(shopId); } query += ' ORDER BY created_at DESC LIMIT ?'; params.push(Number(limit)); const [rows]: [any[], any] = await this.getPool().query(query, params); return rows.map((r: any) => this.mapStoreActivityLogRow(r)); } // ============================================================================== // SAAS ADMIN: ROW MAPPERS // ============================================================================== private mapAdminUserRow(r: any): AdminUserRecord { return { ...r, is_active: Boolean(r.is_active), }; } private mapAppInstallationRow(r: any): AppInstallationRecord { return { ...r, metadata: typeof r.metadata === 'string' ? JSON.parse(r.metadata) : r.metadata || null, }; } private mapSubscriptionRow(r: any): SubscriptionRecord { return { ...r, price: Number(r.price || 0), trial_days: Number(r.trial_days || 0), }; } private mapBillingEventRow(r: any): BillingEventRecord { return { ...r, amount: Number(r.amount || 0), details: typeof r.details === 'string' ? JSON.parse(r.details) : r.details || null, }; } private mapStoreActivityLogRow(r: any): StoreActivityLogRecord { return { ...r, metadata: typeof r.metadata === 'string' ? JSON.parse(r.metadata) : r.metadata || null, }; } // ============================================================================ // SYSTEM BRANDING MANAGEMENT METHODS // ============================================================================ public async getSystemBranding(): Promise { const defaultRecord: SystemBrandingRecord = { id: 'default_branding', app_name: 'DigiPixora — Meta Pixel & Ads', logo_url: normalizeAssetUrl('/images/logo.png'), favicon_url: normalizeAssetUrl('/favicon.ico'), created_at: new Date().toISOString(), updated_at: new Date().toISOString(), updated_by: 'system', }; try { const pool = await this.getInitializedPool(); const [rows]: [any[], any] = await pool.query( 'SELECT * FROM system_branding WHERE id = ? LIMIT 1', ['default_branding'] ); if (rows && rows.length > 0) { const row = rows[0] as SystemBrandingRecord; return { ...row, logo_url: normalizeAssetUrl(row.logo_url || '/images/logo.png'), favicon_url: normalizeAssetUrl(row.favicon_url || '/favicon.ico'), }; } await pool.query( `INSERT IGNORE INTO system_branding (id, app_name, logo_url, favicon_url, created_at, updated_at, updated_by) VALUES (?, ?, ?, ?, ?, ?, ?)`, [ defaultRecord.id, defaultRecord.app_name, defaultRecord.logo_url, defaultRecord.favicon_url, defaultRecord.created_at, defaultRecord.updated_at, defaultRecord.updated_by, ] ); return defaultRecord; } catch (err) { console.error('Error fetching system branding:', err); return defaultRecord; } } public async updateSystemBranding( data: { app_name?: string; logo_url?: string | null; favicon_url?: string | null }, updatedBy: string = 'system' ): Promise { const current = await this.getSystemBranding(); const updated: SystemBrandingRecord = { ...current, app_name: data.app_name !== undefined ? data.app_name.trim() : current.app_name, logo_url: data.logo_url !== undefined ? data.logo_url : current.logo_url, favicon_url: data.favicon_url !== undefined ? data.favicon_url : current.favicon_url, updated_at: new Date().toISOString(), updated_by: updatedBy, }; try { const pool = await this.getInitializedPool(); await pool.query( `INSERT INTO system_branding (id, app_name, logo_url, favicon_url, created_at, updated_at, updated_by) VALUES (?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE app_name = VALUES(app_name), logo_url = VALUES(logo_url), favicon_url = VALUES(favicon_url), updated_at = VALUES(updated_at), updated_by = VALUES(updated_by)`, [ updated.id, updated.app_name, updated.logo_url, updated.favicon_url, updated.created_at, updated.updated_at, updated.updated_by, ] ); } catch (err) { console.error('Error updating system branding in database:', err); } return updated; } public async resetSystemBranding(updatedBy: string = 'system'): Promise { return this.updateSystemBranding( { app_name: 'DigiPixora — Meta Pixel & Ads', logo_url: normalizeAssetUrl('/images/logo.png'), favicon_url: normalizeAssetUrl('/favicon.ico'), }, updatedBy ); } public async getCampaignsByShopId(shopId: string): Promise { const pool = await this.getInitializedPool(); const [rows]: [any[], any] = await pool.query( `SELECT * FROM ad_campaigns WHERE shop_id = ? ORDER BY created_at DESC`, [shopId] ); return rows.map((r) => ({ id: r.id, name: r.name, status: r.status, budget: parseFloat(r.budget || '0'), spend: parseFloat(r.spend || '0'), impressions: parseInt(r.impressions || '0', 10), clicks: parseInt(r.clicks || '0', 10), ctr: parseFloat(r.ctr || '0'), cpc: parseFloat(r.cpc || '0'), purchases: parseInt(r.purchases || '0', 10), revenue: parseFloat(r.revenue || '0'), roas: parseFloat(r.roas || '0'), })); } public async createCampaign(campaign: any): Promise { const pool = await this.getInitializedPool(); await pool.query( `INSERT INTO ad_campaigns (id, shop_id, name, status, budget, spend, impressions, clicks, ctr, cpc, purchases, revenue, roas, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`, [ campaign.id, campaign.shop_id, campaign.name, campaign.status || 'ACTIVE', campaign.budget || 25.0, campaign.spend || 0, campaign.impressions || 0, campaign.clicks || 0, campaign.ctr || 0, campaign.cpc || 0, campaign.purchases || 0, campaign.revenue || 0, campaign.roas || 0, ] ); } public async updateCampaignStatus(id: string, shopId: string, status: string): Promise { const pool = await this.getInitializedPool(); await pool.query( `UPDATE ad_campaigns SET status = ?, updated_at = NOW() WHERE id = ? AND shop_id = ?`, [status, id, shopId] ); } public async getAudiencesByShopId(shopId: string): Promise { const pool = await this.getInitializedPool(); const [rows]: [any[], any] = await pool.query( 'SELECT * FROM ad_audiences WHERE shop_id = ? ORDER BY created_at DESC', [shopId] ); return rows.map((r) => ({ id: r.id, name: r.name, type: r.type, rule: r.rule, size: r.size, matchRate: r.match_rate, lastSynced: r.last_synced, status: r.status, })); } public async createAudience(audience: any): Promise { const pool = await this.getInitializedPool(); await pool.query( `INSERT INTO ad_audiences (id, shop_id, name, type, rule, size, match_rate, last_synced, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`, [ audience.id, audience.shop_id, audience.name, audience.type, audience.rule, audience.size || 0, audience.matchRate || '—', audience.lastSynced || 'Awaiting sync', audience.status || 'READY', ] ); } } export const db = new MySQLDatabaseStore();