-- Shopify Integration Tables for Marketing Dashboard
-- Run this SQL in phpMyAdmin if migrations don't run automatically

-- Table: ext_shopify_stores
CREATE TABLE IF NOT EXISTS `ext_shopify_stores` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `shop_domain` varchar(255) NOT NULL,
  `access_token` text DEFAULT NULL,
  `api_key` varchar(255) DEFAULT NULL,
  `api_secret` text DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `last_sync_at` timestamp NULL DEFAULT NULL,
  `last_product_sync_at` timestamp NULL DEFAULT NULL,
  `last_order_sync_at` timestamp NULL DEFAULT NULL,
  `sync_settings` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ext_shopify_stores_shop_domain_unique` (`shop_domain`),
  KEY `ext_shopify_stores_user_id_index` (`user_id`),
  KEY `ext_shopify_stores_shop_domain_index` (`shop_domain`),
  CONSTRAINT `ext_shopify_stores_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ext_shopify_products
CREATE TABLE IF NOT EXISTS `ext_shopify_products` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `shopify_store_id` bigint(20) UNSIGNED NOT NULL,
  `shopify_product_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `handle` varchar(255) DEFAULT NULL,
  `product_type` varchar(255) DEFAULT NULL,
  `vendor` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `compare_at_price` decimal(10,2) DEFAULT NULL,
  `inventory_quantity` int(11) NOT NULL DEFAULT 0,
  `inventory_policy` varchar(255) DEFAULT NULL,
  `image_url` text DEFAULT NULL,
  `images` json DEFAULT NULL,
  `variants` json DEFAULT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'active',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ext_shopify_products_store_product_unique` (`shopify_store_id`, `shopify_product_id`),
  KEY `ext_shopify_products_shopify_store_id_index` (`shopify_store_id`),
  KEY `ext_shopify_products_shopify_product_id_index` (`shopify_product_id`),
  KEY `ext_shopify_products_status_index` (`status`),
  CONSTRAINT `ext_shopify_products_shopify_store_id_foreign` FOREIGN KEY (`shopify_store_id`) REFERENCES `ext_shopify_stores` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ext_shopify_orders
CREATE TABLE IF NOT EXISTS `ext_shopify_orders` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `shopify_store_id` bigint(20) UNSIGNED NOT NULL,
  `shopify_order_id` bigint(20) UNSIGNED NOT NULL,
  `order_number` varchar(255) DEFAULT NULL,
  `customer_email` varchar(255) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `customer_phone` varchar(255) DEFAULT NULL,
  `total_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `subtotal_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_tax` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total_discounts` decimal(10,2) NOT NULL DEFAULT 0.00,
  `currency` varchar(3) NOT NULL DEFAULT 'USD',
  `financial_status` varchar(255) DEFAULT NULL,
  `fulfillment_status` varchar(255) DEFAULT NULL,
  `total_items` int(11) NOT NULL DEFAULT 0,
  `line_items` json DEFAULT NULL,
  `shipping_address` json DEFAULT NULL,
  `billing_address` json DEFAULT NULL,
  `utm_source` varchar(255) DEFAULT NULL,
  `utm_medium` varchar(255) DEFAULT NULL,
  `utm_campaign` varchar(255) DEFAULT NULL,
  `landing_site` text DEFAULT NULL,
  `referring_site` text DEFAULT NULL,
  `shopify_created_at` timestamp NULL DEFAULT NULL,
  `shopify_updated_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ext_shopify_orders_store_order_unique` (`shopify_store_id`, `shopify_order_id`),
  KEY `ext_shopify_orders_shopify_store_id_index` (`shopify_store_id`),
  KEY `ext_shopify_orders_shopify_order_id_index` (`shopify_order_id`),
  KEY `ext_shopify_orders_customer_email_index` (`customer_email`),
  KEY `ext_shopify_orders_financial_status_index` (`financial_status`),
  KEY `ext_shopify_orders_utm_campaign_index` (`utm_campaign`),
  KEY `ext_shopify_orders_shopify_created_at_index` (`shopify_created_at`),
  CONSTRAINT `ext_shopify_orders_shopify_store_id_foreign` FOREIGN KEY (`shopify_store_id`) REFERENCES `ext_shopify_stores` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ext_campaign_conversions
CREATE TABLE IF NOT EXISTS `ext_campaign_conversions` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `campaign_id` bigint(20) UNSIGNED NOT NULL,
  `shopify_order_id` bigint(20) UNSIGNED NOT NULL,
  `revenue` decimal(10,2) NOT NULL DEFAULT 0.00,
  `utm_source` varchar(255) DEFAULT NULL,
  `utm_medium` varchar(255) DEFAULT NULL,
  `utm_campaign` varchar(255) DEFAULT NULL,
  `utm_term` varchar(255) DEFAULT NULL,
  `utm_content` varchar(255) DEFAULT NULL,
  `conversion_type` varchar(255) NOT NULL DEFAULT 'direct',
  `converted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ext_campaign_conversions_campaign_id_index` (`campaign_id`),
  KEY `ext_campaign_conversions_shopify_order_id_index` (`shopify_order_id`),
  KEY `ext_campaign_conversions_utm_campaign_index` (`utm_campaign`),
  KEY `ext_campaign_conversions_converted_at_index` (`converted_at`),
  CONSTRAINT `ext_campaign_conversions_campaign_id_foreign` FOREIGN KEY (`campaign_id`) REFERENCES `ext_marketing_campaigns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ext_campaign_conversions_shopify_order_id_foreign` FOREIGN KEY (`shopify_order_id`) REFERENCES `ext_shopify_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: ext_marketing_dashboard_settings
CREATE TABLE IF NOT EXISTS `ext_marketing_dashboard_settings` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `widget_visibility` json DEFAULT NULL,
  `widget_order` json DEFAULT NULL,
  `shopify_settings` json DEFAULT NULL,
  `chart_settings` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ext_marketing_dashboard_settings_user_id_unique` (`user_id`),
  CONSTRAINT `ext_marketing_dashboard_settings_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Success message
SELECT 'Shopify Integration tables created successfully!' AS message;
