CREATE DEFINER=`SYSUSR`@`%` PROCEDURE `setupFreshInstall_TruncateTables_v2`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
   DECLARE done INT DEFAULT FALSE;
   DECLARE tableName VARCHAR(100);
    DECLARE keepTableNames TEXT DEFAULT 'acceptable_quality_limits_table_one, acceptable_quality_limits_table_two,company_overview, countries, hr_work_schedules, hr_work_shift, pest_control_device_types, pest_control_pest_types, suppliers_approval_method,suppliers_risk_status, user_stores_perms'; -- Comma-separated list of tables to KEEP (NOT truncated). Example: 'system_users,system_settings'
    DECLARE keepTableLikePattern VARCHAR(255) DEFAULT 'system\\_%'; -- LIKE pattern of tables to KEEP. Use SQL wildcards. Default keeps tables starting with 'system_'.
    DECLARE keepTemplates TINYINT(1) DEFAULT 1; -- If true, tables with 'template' in the name are kept (not truncated).
    DECLARE forceTruncateTableNames TEXT DEFAULT 'system_setup_state,system_trigger_email,system_trigger_notifications,system_users_logs,system_user_audit_trail'; -- Comma-separated list of tables to ALWAYS truncate (even if matching keepTableLikePattern).
    DECLARE prevForeignKeyChecks INT DEFAULT 1;
    DECLARE usersPkColumn VARCHAR(64) DEFAULT '';

    DECLARE cur CURSOR FOR
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_type = 'BASE TABLE'
          AND (
                FIND_IN_SET(table_name, forceTruncateTableNames)
                OR (
                    (keepTableNames = '' OR NOT FIND_IN_SET(table_name, keepTableNames))
                    AND (keepTableLikePattern = '' OR LOWER(table_name) NOT LIKE LOWER(keepTableLikePattern) ESCAPE '\\')
                    AND (keepTemplates = 0 OR LOWER(table_name) NOT LIKE '%template%')
                )
          );
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    IF keepTableNames = '' AND keepTableLikePattern = '' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'setupFreshInstall_TruncateTables_v2: both keepTableNames and keepTableLikePattern are empty. Refusing to truncate ALL tables. Populate a keep list and/or keep LIKE pattern in truncateDemoData.sql.';
    END IF;

    SET prevForeignKeyChecks = @@FOREIGN_KEY_CHECKS;
    SET FOREIGN_KEY_CHECKS = 0;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO tableName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Truncate table (TRUNCATE resets AUTO_INCREMENT in MySQL)
        SET @truncate_query = CONCAT('TRUNCATE TABLE `', tableName, '`');
        PREPARE truncate_stmt FROM @truncate_query;
        EXECUTE truncate_stmt;
        DEALLOCATE PREPARE truncate_stmt;
    END LOOP;

    CLOSE cur;

    -- Special-case: keep `system_users` but purge non-superadmin users.
    -- This preserves the hidden maintenance account while removing tenant/demo users.
    IF EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = 'system_users'
    ) THEN
        SET @delete_users_sql = 'DELETE FROM `system_users` WHERE NOT (is_superadmin = b\'1\' OR is_superadmin = 1)';
        PREPARE stmt_delete_users FROM @delete_users_sql;
        EXECUTE stmt_delete_users;
        DEALLOCATE PREPARE stmt_delete_users;
    END IF;

    -- Special-case: cleanup orphan permissions after user purge.
    -- Supports either system_users.user_id or system_users.id.
    IF EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = 'system_user_permissions'
    ) AND EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE() AND table_name = 'system_users'
    ) AND EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = DATABASE() AND table_name = 'system_user_permissions' AND column_name = 'user_id'
    ) THEN
        SET usersPkColumn = (
            SELECT column_name
            FROM information_schema.columns
            WHERE table_schema = DATABASE()
              AND table_name = 'system_users'
              AND column_name IN ('user_id','id')
            ORDER BY CASE column_name WHEN 'user_id' THEN 0 ELSE 1 END
            LIMIT 1
        );

        IF usersPkColumn IS NOT NULL AND usersPkColumn <> '' THEN
            SET @cleanup_perms_sql = CONCAT(
                'DELETE p FROM `system_user_permissions` p ',
                'LEFT JOIN `system_users` u ON u.`', usersPkColumn, '` = p.`user_id` ',
                'WHERE u.`', usersPkColumn, '` IS NULL'
            );
            PREPARE stmt_cleanup_perms FROM @cleanup_perms_sql;
            EXECUTE stmt_cleanup_perms;
            DEALLOCATE PREPARE stmt_cleanup_perms;
        END IF;
    END IF;

    SET FOREIGN_KEY_CHECKS = prevForeignKeyChecks;
END
