-- ============================================================================
-- LASER CUT SAAS - SHIPPING & DELIVERY SCHEMA
-- Local delivery with KM-based pricing
-- ============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================================
-- SHIPPING ZONES
-- ============================================================================

CREATE TABLE IF NOT EXISTS `shipping_zones` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL COMMENT 'Região Centro, Zona Norte, etc',
  `type` ENUM('local_delivery', 'correios', 'transportadora', 'pickup') DEFAULT 'local_delivery',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_type` (`type`),
  INDEX `idx_active` (`active`),
  CONSTRAINT `fk_shipping_zones_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- DELIVERY CONFIG
-- ============================================================================

CREATE TABLE IF NOT EXISTS `delivery_config` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `origin_zipcode` VARCHAR(10) NOT NULL COMMENT 'CEP de origem para cálculo',
  `origin_address` VARCHAR(255) NULL COMMENT 'Endereço de referência',
  `origin_city` VARCHAR(100) NULL,
  `origin_state` VARCHAR(2) NULL,
  `origin_lat` DECIMAL(10, 8) NULL COMMENT 'Latitude para cálculo de distância',
  `origin_lng` DECIMAL(11, 8) NULL COMMENT 'Longitude para cálculo de distância',
  `price_per_km` DECIMAL(10, 2) NOT NULL DEFAULT 2.50 COMMENT 'Valor por KM',
  `min_delivery_fee` DECIMAL(10, 2) NOT NULL DEFAULT 10.00 COMMENT 'Taxa mínima de entrega',
  `max_delivery_radius_km` DECIMAL(10, 2) DEFAULT 50.00 COMMENT 'Raio máximo de entrega em KM',
  `free_delivery_min_value` DECIMAL(10, 2) NULL COMMENT 'Valor mínimo para frete grátis',
  `delivery_time_base_days` INT NOT NULL DEFAULT 2 COMMENT 'Prazo base em dias úteis',
  `delivery_time_per_10km` INT NOT NULL DEFAULT 1 COMMENT 'Dias adicionais a cada 10km',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_tenant_config` (`tenant_id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  CONSTRAINT `fk_delivery_config_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- SHIPPING CALCULATIONS LOG
-- ============================================================================

CREATE TABLE IF NOT EXISTS `shipping_calculations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `session_id` VARCHAR(255) NULL COMMENT 'Session do cliente',
  `customer_id` INT UNSIGNED NULL,
  `destination_zipcode` VARCHAR(10) NOT NULL,
  `destination_city` VARCHAR(100) NULL,
  `destination_state` VARCHAR(2) NULL,
  `destination_lat` DECIMAL(10, 8) NULL,
  `destination_lng` DECIMAL(11, 8) NULL,
  `distance_km` DECIMAL(10, 2) NULL COMMENT 'Distância calculada',
  `price_per_km` DECIMAL(10, 2) NULL,
  `delivery_fee` DECIMAL(10, 2) NULL,
  `estimated_days` INT NULL,
  `calculation_method` ENUM('km', 'fixed', 'api') DEFAULT 'km',
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_tenant_id` (`tenant_id`),
  INDEX `idx_customer_id` (`customer_id`),
  INDEX `idx_destination_zipcode` (`destination_zipcode`),
  INDEX `idx_created_at` (`created_at`),
  CONSTRAINT `fk_shipping_calculations_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_shipping_calculations_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ZIPCODE CACHE (for performance)
-- ============================================================================

CREATE TABLE IF NOT EXISTS `zipcode_cache` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `zipcode` VARCHAR(10) NOT NULL,
  `street` VARCHAR(255) NULL,
  `neighborhood` VARCHAR(100) NULL,
  `city` VARCHAR(100) NOT NULL,
  `state` VARCHAR(2) NOT NULL,
  `lat` DECIMAL(10, 8) NULL,
  `lng` DECIMAL(11, 8) NULL,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_zipcode` (`zipcode`),
  INDEX `idx_city_state` (`city`, `state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;