-- Migration 015: Remove Tax Fields from Sales and Sale Items
-- Date: 2026-01-14
-- Description: 
--   Removes VAT and tax-related fields from sales and sale_items tables.
--   This is part of system restructuring - tax system is being removed completely.

-- ============================================
-- Step 1: Remove VAT field from sales table
-- ============================================

SET @col_exists = (
  SELECT COUNT(*)
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'sales'
    AND COLUMN_NAME = 'vat'
);

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

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

-- ============================================
-- Step 2: Remove tax fields from sale_items table
-- ============================================

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

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

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

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

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

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

-- ============================================
-- Step 3: Update sales.total calculation (total = subtotal - discount_amount)
-- Note: This is a data migration step. The application logic should handle this.
-- ============================================

-- Update total to be subtotal - discount_amount (removing VAT from calculation)
UPDATE `sales` 
SET `total` = `subtotal` - `discount_amount`
WHERE `total` != (`subtotal` - `discount_amount`);

-- Verify the changes
SELECT 'Migration 015 completed: Tax fields removed from sales tables' AS status;
