-- Migration: Drop Customer and Order Cache Tables for GDPR Compliance
-- Description: Removes persistent storage of personal data (customers and orders)
-- Date: 2025-10-31
-- Related: Issue #48 - GDPR-compliant webshop sync refactoring
-- IMPORTANT: This migration will DELETE all cached customer and order data

-- ============================================================================
-- STEP 1: Backup Information (Run manually before applying if needed)
-- ============================================================================

-- If you need to backup data before dropping, run these queries manually:
-- pg_dump -h host -U user -d database -t shopify_customers_cache > shopify_customers_backup.sql
-- pg_dump -h host -U user -d database -t shopify_orders_cache > shopify_orders_backup.sql
-- pg_dump -h host -U user -d database -t woocommerce_customers_cache > woocommerce_customers_backup.sql
-- pg_dump -h host -U user -d database -t woocommerce_orders_cache > woocommerce_orders_backup.sql
-- pg_dump -h host -U user -d database -t shoprenter_customers_cache > shoprenter_customers_backup.sql
-- pg_dump -h host -U user -d database -t shoprenter_orders_cache > shoprenter_orders_backup.sql

-- ============================================================================
-- STEP 2: Log Table Sizes Before Deletion (for auditing)
-- ============================================================================

DO $$
DECLARE
  table_stats TEXT;
BEGIN
  SELECT string_agg(
    format('%s: %s rows', table_name, row_count),
    ', '
  ) INTO table_stats
  FROM (
    SELECT 'shopify_customers_cache' AS table_name,
           (SELECT COUNT(*) FROM shopify_customers_cache) AS row_count
    UNION ALL
    SELECT 'shopify_orders_cache',
           (SELECT COUNT(*) FROM shopify_orders_cache)
    UNION ALL
    SELECT 'woocommerce_customers_cache',
           (SELECT COUNT(*) FROM woocommerce_customers_cache)
    UNION ALL
    SELECT 'woocommerce_orders_cache',
           (SELECT COUNT(*) FROM woocommerce_orders_cache)
    UNION ALL
    SELECT 'shoprenter_customers_cache',
           (SELECT COUNT(*) FROM shoprenter_customers_cache WHERE FALSE) -- Check if exists
    UNION ALL
    SELECT 'shoprenter_orders_cache',
           (SELECT COUNT(*) FROM shoprenter_orders_cache WHERE FALSE) -- Check if exists
  ) counts;

  RAISE NOTICE 'Table sizes before deletion: %', table_stats;
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Could not log table sizes: %', SQLERRM;
END $$;

-- ============================================================================
-- STEP 3: Drop Shopify Customer and Order Cache Tables
-- ============================================================================

-- Drop dependent views or materialized views if any
DROP MATERIALIZED VIEW IF EXISTS shopify_customer_stats CASCADE;
DROP VIEW IF EXISTS shopify_order_summary CASCADE;

-- Drop triggers and functions associated with these tables
DROP TRIGGER IF EXISTS trigger_shopify_customers_updated ON shopify_customers_cache;
DROP TRIGGER IF EXISTS trigger_shopify_orders_updated ON shopify_orders_cache;
DROP FUNCTION IF EXISTS update_shopify_customer_stats() CASCADE;
DROP FUNCTION IF EXISTS update_shopify_order_stats() CASCADE;

-- Drop the tables
DROP TABLE IF EXISTS shopify_customers_cache CASCADE;
DROP TABLE IF EXISTS shopify_orders_cache CASCADE;

RAISE NOTICE 'Dropped Shopify customer and order cache tables';

-- ============================================================================
-- STEP 4: Drop WooCommerce Customer and Order Cache Tables
-- ============================================================================

-- Drop dependent views or materialized views if any
DROP MATERIALIZED VIEW IF EXISTS woocommerce_customer_stats CASCADE;
DROP VIEW IF EXISTS woocommerce_order_summary CASCADE;

-- Drop triggers and functions associated with these tables
DROP TRIGGER IF EXISTS trigger_woocommerce_customers_updated ON woocommerce_customers_cache;
DROP TRIGGER IF EXISTS trigger_woocommerce_orders_updated ON woocommerce_orders_cache;
DROP FUNCTION IF EXISTS update_woocommerce_customer_stats() CASCADE;
DROP FUNCTION IF EXISTS update_woocommerce_order_stats() CASCADE;

-- Drop the tables
DROP TABLE IF EXISTS woocommerce_customers_cache CASCADE;
DROP TABLE IF EXISTS woocommerce_orders_cache CASCADE;

RAISE NOTICE 'Dropped WooCommerce customer and order cache tables';

-- ============================================================================
-- STEP 5: Drop ShopRenter Customer and Order Cache Tables
-- ============================================================================

-- Drop dependent views or materialized views if any
DROP MATERIALIZED VIEW IF EXISTS shoprenter_customer_stats CASCADE;
DROP VIEW IF EXISTS shoprenter_order_summary CASCADE;

-- Drop triggers and functions associated with these tables
DROP TRIGGER IF EXISTS trigger_shoprenter_customers_updated ON shoprenter_customers_cache;
DROP TRIGGER IF EXISTS trigger_shoprenter_orders_updated ON shoprenter_orders_cache;
DROP FUNCTION IF EXISTS update_shoprenter_customer_stats() CASCADE;
DROP FUNCTION IF EXISTS update_shoprenter_order_stats() CASCADE;

-- Drop the tables (if they exist)
DROP TABLE IF EXISTS shoprenter_customers_cache CASCADE;
DROP TABLE IF EXISTS shoprenter_orders_cache CASCADE;

RAISE NOTICE 'Dropped ShopRenter customer and order cache tables';

-- ============================================================================
-- STEP 6: Update Sync Status Functions to Remove Customer/Order References
-- ============================================================================

-- Remove or update any functions that reference the dropped tables
-- Note: Functions are updated in subsequent migrations or Edge Function changes

-- Log a notice about updating related functions
DO $$
BEGIN
  RAISE NOTICE 'Customer and order cache tables have been dropped for GDPR compliance';
  RAISE NOTICE 'Update Edge Functions to remove customer/order sync logic';
  RAISE NOTICE 'Customer and order data will now be accessed in real-time via API';
END $$;

-- ============================================================================
-- STEP 7: Clean Up store_sync_config Table
-- ============================================================================

-- Remove sync_orders and sync_customers columns from store_sync_config
ALTER TABLE store_sync_config DROP COLUMN IF EXISTS sync_orders CASCADE;
ALTER TABLE store_sync_config DROP COLUMN IF EXISTS sync_customers CASCADE;

-- Ensure sync_products column exists and is properly configured
ALTER TABLE store_sync_config ALTER COLUMN sync_products SET DEFAULT true;

RAISE NOTICE 'Updated store_sync_config table to remove customer/order sync flags';

-- ============================================================================
-- STEP 8: Update sync_logs Table Structure
-- ============================================================================

-- Add note field to track the change
COMMENT ON TABLE sync_logs IS 'Sync execution logs - tracks product sync only (customer/order data removed for GDPR compliance)';

-- ============================================================================
-- STEP 9: Clean Up alt_data in Stores Table
-- ============================================================================

-- Remove customer/order sync statistics from stores.alt_data
UPDATE stores
SET alt_data = alt_data - 'last_customer_sync' - 'last_order_sync' - 'customer_count' - 'order_count'
WHERE alt_data IS NOT NULL
  AND (
    alt_data ? 'last_customer_sync'
    OR alt_data ? 'last_order_sync'
    OR alt_data ? 'customer_count'
    OR alt_data ? 'order_count'
  );

RAISE NOTICE 'Cleaned up alt_data in stores table';

-- ============================================================================
-- STEP 10: Summary and Final Notes
-- ============================================================================

DO $$
BEGIN
  RAISE NOTICE '=================================================================';
  RAISE NOTICE 'GDPR Compliance Migration Complete';
  RAISE NOTICE '=================================================================';
  RAISE NOTICE 'Dropped Tables:';
  RAISE NOTICE '  - shopify_customers_cache';
  RAISE NOTICE '  - shopify_orders_cache';
  RAISE NOTICE '  - woocommerce_customers_cache';
  RAISE NOTICE '  - woocommerce_orders_cache';
  RAISE NOTICE '  - shoprenter_customers_cache (if existed)';
  RAISE NOTICE '  - shoprenter_orders_cache (if existed)';
  RAISE NOTICE '';
  RAISE NOTICE 'Kept Tables:';
  RAISE NOTICE '  - shopify_products_cache';
  RAISE NOTICE '  - woocommerce_products_cache';
  RAISE NOTICE '  - shoprenter_products_cache';
  RAISE NOTICE '';
  RAISE NOTICE 'Next Steps:';
  RAISE NOTICE '  1. Deploy updated Edge Functions (remove customer/order sync logic)';
  RAISE NOTICE '  2. Deploy webshop-data-api Edge Function for real-time data access';
  RAISE NOTICE '  3. Update frontend to use new real-time API endpoints';
  RAISE NOTICE '  4. Test data access permissions';
  RAISE NOTICE '=================================================================';
END $$;
