-- Rollback Script: POS Sessions Management Enhancement
-- Date: 2025-11-15
-- Purpose: Rollback changes from 20251115_pos_sessions_management_enhancement.sql
-- WARNING: This will remove verification data. Back up pos_session_verification_log before running!

-- ========================================
-- PART 1: Drop Triggers and Functions
-- ========================================

-- Drop verification logging trigger
DROP TRIGGER IF EXISTS trg_log_session_verification ON pos_sessions;

-- Drop trigger function
DROP FUNCTION IF EXISTS log_session_verification();

-- Drop payment breakdown function
DROP FUNCTION IF EXISTS get_session_payment_breakdown(UUID);

-- ========================================
-- PART 2: Drop Audit Log Table
-- ========================================

-- WARNING: This deletes all verification history!
-- Consider backing up this table before rollback:
-- pg_dump -t pos_session_verification_log your_database > verification_log_backup.sql

DROP TABLE IF EXISTS pos_session_verification_log;

-- ========================================
-- PART 3: Drop Enhanced View
-- ========================================

DROP VIEW IF EXISTS pos_sessions_enhanced;

-- ========================================
-- PART 4: Drop Indexes
-- ========================================

DROP INDEX IF EXISTS idx_pos_sessions_verified;
DROP INDEX IF EXISTS idx_pos_sessions_verified_by;
DROP INDEX IF EXISTS idx_pos_sessions_approval_status;
DROP INDEX IF EXISTS idx_pos_sessions_cashier_date;
DROP INDEX IF EXISTS idx_pos_sessions_status_verified;

-- ========================================
-- PART 5: Remove Columns from pos_sessions
-- ========================================

-- Drop foreign key constraint first
ALTER TABLE pos_sessions 
DROP CONSTRAINT IF EXISTS fk_pos_sessions_verified_by;

-- Drop check constraint
ALTER TABLE pos_sessions 
DROP CONSTRAINT IF EXISTS chk_pos_sessions_approval_status;

-- Drop columns (WARNING: This deletes all verification data!)
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS verified;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS verified_by;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS verified_at;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS supervisor_counted_cash;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS adjusted_variance;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS variance_explanation;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS verification_notes;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS approval_status;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS total_sales;
ALTER TABLE pos_sessions DROP COLUMN IF EXISTS cash_sales;

-- ========================================
-- ROLLBACK COMPLETE
-- ========================================

DO $$
BEGIN
    RAISE NOTICE 'Rollback of 20251115_pos_sessions_management_enhancement completed';
    RAISE NOTICE 'WARNING: All verification data has been removed';
    RAISE NOTICE 'If you backed up pos_session_verification_log, you can restore it later';
END $$;
