-- =====================================================
-- CLEAR TRANSACTION DATA ONLY
-- File: cleardata.sql
-- Purpose: Clear all transaction data while keeping master data intact
-- Date: 24 Dec 2024
-- Database: PostgreSQL
-- =====================================================

-- Disable foreign key checks temporarily (PostgreSQL way)
SET session_replication_role = replica;

-- =====================================================
-- CLEAR TRANSACTION TABLES (in dependency order)
-- =====================================================

-- Clear Stock Opname Change Logs (audit trail)
DELETE FROM t_stock_opname_change_logs;
SELECT 'Cleared t_stock_opname_change_logs' as status, COUNT(*) as remaining_records FROM t_stock_opname_change_logs;

-- Clear Stock Movements (stock audit trail)
DELETE FROM t_stock_movements;
SELECT 'Cleared t_stock_movements' as status, COUNT(*) as remaining_records FROM t_stock_movements;

-- Clear Current Stocks (real-time stock)
DELETE FROM t_current_stocks;
SELECT 'Cleared t_current_stocks' as status, COUNT(*) as remaining_records FROM t_current_stocks;

-- Clear Item Request Cancellations
DELETE FROM t_item_request_cancellations;
SELECT 'Cleared t_item_request_cancellations' as status, COUNT(*) as remaining_records FROM t_item_request_cancellations;

-- Clear Item Request Details
DELETE FROM t_item_request_details;
SELECT 'Cleared t_item_request_details' as status, COUNT(*) as remaining_records FROM t_item_request_details;

-- Clear Item Requests (SPB)
DELETE FROM t_item_requests;
SELECT 'Cleared t_item_requests' as status, COUNT(*) as remaining_records FROM t_item_requests;

-- Clear Stock Opname Details
DELETE FROM t_stock_opname_details;
SELECT 'Cleared t_stock_opname_details' as status, COUNT(*) as remaining_records FROM t_stock_opname_details;

-- Clear Stock Opnames (LSM)
DELETE FROM t_stock_opnames;
SELECT 'Cleared t_stock_opnames' as status, COUNT(*) as remaining_records FROM t_stock_opnames;

-- Clear Forecasts
DELETE FROM t_forecasts;
SELECT 'Cleared t_forecasts' as status, COUNT(*) as remaining_records FROM t_forecasts;

-- =====================================================
-- RESET AUTO INCREMENT SEQUENCES
-- =====================================================

-- Reset sequences for transaction tables
ALTER SEQUENCE t_stock_opnames_id_seq RESTART WITH 1;
ALTER SEQUENCE t_stock_opname_details_id_seq RESTART WITH 1;
ALTER SEQUENCE t_stock_opname_change_logs_id_seq RESTART WITH 1;
ALTER SEQUENCE t_item_requests_id_seq RESTART WITH 1;
ALTER SEQUENCE t_item_request_details_id_seq RESTART WITH 1;
ALTER SEQUENCE t_item_request_cancellations_id_seq RESTART WITH 1;
ALTER SEQUENCE t_forecasts_id_seq RESTART WITH 1;
ALTER SEQUENCE t_stock_movements_id_seq RESTART WITH 1;
ALTER SEQUENCE t_current_stocks_id_seq RESTART WITH 1;

-- Re-enable foreign key checks
SET session_replication_role = DEFAULT;

-- =====================================================
-- MASTER DATA REMAINS INTACT
-- =====================================================
-- The following master data tables are NOT cleared:
-- - users (user accounts)
-- - m_stores (store master)
-- - m_raw_materials (raw material master)
-- - m_armada_types (armada type master)
-- - m_shipment_deliveries (shipment schedule master)
-- - m_target_sisa_malam (target sisa malam master)
-- - m_store_raw_materials (store-raw material mapping)

-- =====================================================
-- SUMMARY REPORT
-- =====================================================

SELECT 
    'TRANSACTION DATA CLEARED' as operation,
    'All transaction tables have been cleared' as status,
    'Master data remains intact' as note;

-- Show remaining record counts for verification
SELECT 
    'Master Data Summary' as category,
    CONCAT(
        (SELECT COUNT(*) FROM users), ' users, ',
        (SELECT COUNT(*) FROM m_stores), ' stores, ',
        (SELECT COUNT(*) FROM m_raw_materials), ' raw materials, ',
        (SELECT COUNT(*) FROM m_armada_types), ' armada types, ',
        (SELECT COUNT(*) FROM m_shipment_deliveries), ' shipment schedules, ',
        (SELECT COUNT(*) FROM m_target_sisa_malam), ' target sisa malam, ',
        (SELECT COUNT(*) FROM m_store_raw_materials), ' store-raw material mappings'
    ) as details;

SELECT 
    'Transaction Data Summary' as category,
    CONCAT(
        (SELECT COUNT(*) FROM t_stock_opnames), ' stock opnames, ',
        (SELECT COUNT(*) FROM t_item_requests), ' item requests, ',
        (SELECT COUNT(*) FROM t_delivery_orders), ' delivery orders, ',
        (SELECT COUNT(*) FROM t_forecasts), ' forecasts, ',
        (SELECT COUNT(*) FROM t_current_stocks), ' current stocks, ',
        (SELECT COUNT(*) FROM t_stock_movements), ' stock movements'
    ) as details;

-- =====================================================
-- NOTES:
-- 
-- This script safely clears all transaction data while preserving:
-- ✅ User accounts and authentication data
-- ✅ Store master data
-- ✅ Raw material master data  
-- ✅ All other master data tables
-- ✅ Store-raw material mapping configurations
-- 
-- Cleared transaction data includes:
-- 🗑️ Stock Opnames (LSM) and details
-- 🗑️ Item Requests (SPB) and details  
-- 🗑️ Delivery Orders and details
-- 🗑️ Forecasts
-- 🗑️ Current stock levels
-- 🗑️ Stock movements and audit trails
-- 🗑️ Change logs and cancellation records
-- 
-- After running this script, you can:
-- 1. Re-run db_add2.sql to recreate stock tables with fresh sample data
-- 2. Start fresh with clean transaction data
-- 3. Test the system from a clean state
-- =====================================================