-- Migration 014: Remove Tax System
-- Date: 2026-01-14
-- Description: 
--   Removes all tax-related tables (tax_categories, tax_exemptions, tax_rates).
--   This is part of system restructuring - tax system is being removed completely.
--
-- WARNING: This migration will DELETE all tax data!
-- Make sure to backup your database before running this migration.

-- ============================================
-- Step 1: Remove foreign key constraints that reference tax tables
-- ============================================

-- Remove tax_category_id foreign key from products
SET @fk_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'products'
    AND COLUMN_NAME = 'tax_category_id'
    AND REFERENCED_TABLE_NAME = 'tax_categories'
);

SET @sql = IF(@fk_exists > 0,
  'ALTER TABLE `products` DROP FOREIGN KEY `products_ibfk_1`',
  'SELECT "Foreign key for products.tax_category_id does not exist" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Remove tax_category_id column from products
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'products'
    AND COLUMN_NAME = 'tax_category_id'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `products` DROP COLUMN `tax_category_id`',
  'SELECT "Column tax_category_id does not exist in products table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Remove default_vat_rate column from products
SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'products'
    AND COLUMN_NAME = 'default_vat_rate'
);

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `products` DROP COLUMN `default_vat_rate`',
  'SELECT "Column default_vat_rate does not exist in products table" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ============================================
-- Step 2: Drop tax-related tables
-- ============================================

-- Drop tax_exemptions table first (has foreign keys to customers and tax_categories)
DROP TABLE IF EXISTS `tax_exemptions`;

-- Drop tax_categories table (has foreign key to tax_rates, but we'll drop the constraint first)
-- First drop the foreign key constraint
SET @fk_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'tax_categories'
    AND CONSTRAINT_NAME = 'tax_categories_ibfk_1'
);

SET @sql = IF(@fk_exists > 0,
  'ALTER TABLE `tax_categories` DROP FOREIGN KEY `tax_categories_ibfk_1`',
  'SELECT "Foreign key tax_categories_ibfk_1 does not exist" AS message'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Now drop tax_categories table
DROP TABLE IF EXISTS `tax_categories`;

-- Drop tax_rates table (now that foreign key is removed)
DROP TABLE IF EXISTS `tax_rates`;

-- Verify the changes
SELECT 'Migration 014 completed: Tax tables removed' AS status;
