-- ============================================================================
-- LASER CUT SAAS - WHATSAPP QUOTE SCHEMA
-- Quick quote via WhatsApp tracking
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- WHATSAPP QUOTES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `whatsapp_quotes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED NULL,
  `session_id` VARCHAR(255) NULL COMMENT 'Session for non-logged users',
  `product_id` INT UNSIGNED NULL,
  `product_name` VARCHAR(255) NOT NULL,
  `product_sku` VARCHAR(100) NULL,
  `variation_name` VARCHAR(255) NULL COMMENT 'Selected variation',
  `quantity` INT NOT NULL DEFAULT 1,
  `customization_summary` TEXT NULL,
  `customer_note` TEXT NULL,
  `customer_phone` VARCHAR(50) NULL COMMENT 'If provided',
  `customer_name` VARCHAR(255) NULL,
  `whatsapp_number` VARCHAR(50) NOT NULL COMMENT 'Store WhatsApp number used',
  `message_generated` TEXT NOT NULL COMMENT 'Pre-filled message',
  `clicked_at` DATETIME NOT NULL,
  `converted` TINYINT(1) DEFAULT 0 COMMENT 'If became an order',
  `order_id` INT UNSIGNED NULL COMMENT 'Related order if converted',
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_customer_id` (`customer_id`),
  INDEX `idx_product_id` (`product_id`),
  INDEX `idx_converted` (`converted`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_whatsapp_quotes_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_whatsapp_quotes_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_whatsapp_quotes_product` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_whatsapp_quotes_order` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- WHATSAPP CONFIG (per tenant)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `whatsapp_config` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `phone_number` VARCHAR(50) NOT NULL COMMENT 'WhatsApp number with country code',
  `display_name` VARCHAR(100) NULL COMMENT 'Name shown in chat',
  `greeting_message` TEXT NULL COMMENT 'Default greeting',
  `business_hours_start` TIME NULL,
  `business_hours_end` TIME NULL,
  `away_message` TEXT NULL COMMENT 'Message outside business hours',
  `quote_message_template` TEXT NULL COMMENT 'Template for quote messages',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_tenant_whatsapp` (`tenant_id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  CONSTRAINT `fk_whatsapp_config_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;