-- Migration: Add Tax Groups Support
-- This migration adds tax_groups table and updates products table

-- Create tax_groups table
CREATE TABLE IF NOT EXISTS `tax_groups` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  `type` ENUM('percentage','fixed') NOT NULL DEFAULT 'percentage',
  `description` TEXT,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add tax_group_id column to products table
ALTER TABLE `products` 
ADD COLUMN `tax_group_id` INT UNSIGNED NULL AFTER `brand_id`,
ADD KEY `tax_group_id` (`tax_group_id`),
ADD FOREIGN KEY (`tax_group_id`) REFERENCES `tax_groups`(`id`) ON DELETE SET NULL;
