-- Clean all item/details/quantity data while keeping item categories.
--
-- This is for a fresh item upload. It preserves master/setup data such as:
-- item_categories, units, item_types, projects, project_divisions, suppliers, users.
--
-- It removes:
-- direct item catalog rows, indirect goods catalog rows, division inventory,
-- project/site inventory, stock movements, incoming/outgoing/direct delivery
-- details, material request/transfer details, returns, damaged/FOC stock,
-- and related quantity/history rows.

SET FOREIGN_KEY_CHECKS = 0;

DROP PROCEDURE IF EXISTS truncate_if_exists;

DELIMITER $$
CREATE PROCEDURE truncate_if_exists(IN target_table_name VARCHAR(64))
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_name = target_table_name
    ) THEN
        SET @truncate_sql = CONCAT('TRUNCATE TABLE `', target_table_name, '`');
        PREPARE truncate_stmt FROM @truncate_sql;
        EXECUTE truncate_stmt;
        DEALLOCATE PREPARE truncate_stmt;
    END IF;
END$$
DELIMITER ;

-- Direct goods / warehouse quantities and transaction details
CALL truncate_if_exists('foc_inventory_adjustments');
CALL truncate_if_exists('foc_inventory');
CALL truncate_if_exists('damaged_items');
CALL truncate_if_exists('outgoing_fulfillments');
CALL truncate_if_exists('material_delivery_notes');
CALL truncate_if_exists('material_site_transfer_item_allocations');
CALL truncate_if_exists('material_site_transfer_items');
CALL truncate_if_exists('material_site_transfers');
CALL truncate_if_exists('material_transfer_issue_items');
CALL truncate_if_exists('material_transfer_issues');
CALL truncate_if_exists('material_transfer_request_items');
CALL truncate_if_exists('material_transfer_requests');
CALL truncate_if_exists('division_deliveries');
CALL truncate_if_exists('direct_delivery_items');
CALL truncate_if_exists('direct_deliveries');
CALL truncate_if_exists('lpo_items');
CALL truncate_if_exists('lpo_management');
CALL truncate_if_exists('incoming_operation_items');
CALL truncate_if_exists('incoming_operations');
CALL truncate_if_exists('return_items');
CALL truncate_if_exists('return_transactions');
CALL truncate_if_exists('outgoing_items');
CALL truncate_if_exists('outgoing_transactions');
CALL truncate_if_exists('incoming_items');
CALL truncate_if_exists('incoming_transactions');
CALL truncate_if_exists('stock_movements');
CALL truncate_if_exists('project_inventory');
CALL truncate_if_exists('inventory');
CALL truncate_if_exists('supplier_items');

-- Indirect goods quantities and transaction details
CALL truncate_if_exists('indirect_goods_consumption');
CALL truncate_if_exists('indirect_goods_stock_movements');
CALL truncate_if_exists('indirect_goods_request_items');
CALL truncate_if_exists('indirect_goods_requests');
CALL truncate_if_exists('indirect_goods_incoming_items');
CALL truncate_if_exists('indirect_goods_incoming');
CALL truncate_if_exists('indirect_goods_project_inventory');
CALL truncate_if_exists('indirect_goods_inventory');
CALL truncate_if_exists('indirect_goods');

-- Direct item catalog, intentionally after all dependent rows.
CALL truncate_if_exists('items');

SET FOREIGN_KEY_CHECKS = 1;

DROP TEMPORARY TABLE IF EXISTS cleanup_verification;
CREATE TEMPORARY TABLE cleanup_verification (
    table_name VARCHAR(64) NOT NULL,
    row_count BIGINT NOT NULL
);

DROP PROCEDURE IF EXISTS count_if_exists;

DELIMITER $$
CREATE PROCEDURE count_if_exists(IN target_table_name VARCHAR(64))
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_name = target_table_name
    ) THEN
        SET @count_sql = CONCAT(
            'INSERT INTO cleanup_verification SELECT ',
            QUOTE(target_table_name),
            ', COUNT(*) FROM `',
            target_table_name,
            '`'
        );
        PREPARE count_stmt FROM @count_sql;
        EXECUTE count_stmt;
        DEALLOCATE PREPARE count_stmt;
    END IF;
END$$
DELIMITER ;

-- Verification: item_categories should remain, item/stock tables should be empty.
CALL count_if_exists('item_categories');
CALL count_if_exists('items');
CALL count_if_exists('inventory');
CALL count_if_exists('project_inventory');
CALL count_if_exists('indirect_goods');
CALL count_if_exists('indirect_goods_inventory');
CALL count_if_exists('indirect_goods_project_inventory');

SELECT * FROM cleanup_verification ORDER BY table_name;

DROP PROCEDURE IF EXISTS count_if_exists;
DROP PROCEDURE IF EXISTS truncate_if_exists;
DROP TEMPORARY TABLE IF EXISTS cleanup_verification;
