-- Migration 080: Add Soft Delete Support
-- Adds deleted_at column to key tables to prevent permanent data loss
-- Date: 2025-11-24
-- Note: Uses IF NOT EXISTS to allow re-running safely

-- Add deleted_at to customers table
ALTER TABLE customers
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE customers
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to products table
ALTER TABLE products
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE products
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to suppliers table
ALTER TABLE suppliers
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE suppliers
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to invoices table
ALTER TABLE invoices
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE invoices
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to quotes table
ALTER TABLE quotes
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE quotes
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to sales_orders table
ALTER TABLE sales_orders
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE sales_orders
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to purchase_orders table
ALTER TABLE purchase_orders
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE purchase_orders
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to projects table
ALTER TABLE projects
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE projects
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to project_tasks table
ALTER TABLE project_tasks
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE project_tasks
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to work_orders table
ALTER TABLE work_orders
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE work_orders
ADD INDEX idx_deleted_at (deleted_at);

-- Add deleted_at to customer_contacts table
ALTER TABLE customer_contacts
ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at;

ALTER TABLE customer_contacts
ADD INDEX idx_deleted_at (deleted_at);

-- Note: The following tables may not exist in all installations
-- We'll check and add deleted_at only if they exist

-- Check and add to warehouses
SET @table_exists = (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'warehouses');
SET @sql = IF(@table_exists > 0,
    'ALTER TABLE warehouses ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at, ADD INDEX idx_deleted_at (deleted_at)',
    'SELECT "Table warehouses does not exist, skipping" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add to locations
SET @table_exists = (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'locations');
SET @sql = IF(@table_exists > 0,
    'ALTER TABLE locations ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at, ADD INDEX idx_deleted_at (deleted_at)',
    'SELECT "Table locations does not exist, skipping" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add to journal_entries
SET @table_exists = (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'journal_entries');
SET @sql = IF(@table_exists > 0,
    'ALTER TABLE journal_entries ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER updated_at, ADD INDEX idx_deleted_at (deleted_at)',
    'SELECT "Table journal_entries does not exist, skipping" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;